Oracle PL/SQL Tutorial/Collections/Table Collection Attributes

Collection methods: First, Last, Next

SQL>
SQL>
SQL> create table employee
  2          (
  3           empl_no                integer         primary key
  4          ,lastname               varchar2(20)    not null
  5          ,firstname              varchar2(15)    not null
  6          ,midinit                varchar2(1)
  7          ,street                 varchar2(30)
  8          ,city                   varchar2(20)
  9          ,state                  varchar2(2)
 10          ,zip                    varchar2(5)
 11          ,zip_4                  varchar2(4)
 12          ,area_code              varchar2(3)
 13          ,phone                  varchar2(8)
 14          ,company_name           varchar2(50));
Table created.
SQL>
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
  2  values(1,"Jones","Joe","J","10 Ave","New York","NY","11111","1111","111", "111-1111","A Company");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
  2  values(2,"Smith","Sue","J","20 Ave","New York","NY","22222","2222","222", "222-111","B Company");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
  2  values(3,"Anderson","Peggy","J","500 St","New York","NY","33333","3333","333", "333-3333","C Company");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
  2  values(4,"Andy","Jill", null,"930 St","New York","NY","44444","4444","212", "634-7733","D Company");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
  2  values(5,"OK","Carl","L","19 Drive","New York","NY","55555","3234","212", "243-4243","E Company");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
  2  values(6,"Peter","Jee","Q","38 Ave","New York","NY","66666","4598","212", "454-5443","F Inc");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
  2  values(7,"Baker","Paul","V","738 St.","Queens","NY","77777","3842","718", "664-4333","G Inc");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
  2  values(8,"Young","Steve","J","388 Ave","New York","NY","88888","3468","212", "456-4566","H Associates Inc");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
  2  values(9,"Mona","Joe","T","9300 Ave","Kansas City","MO","99999","3658","415", "456-4563","J Inc");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
  2  values(10,"Hackett","Karen","S","Kings Rd. Apt 833","Bellmore","NY","61202","3898","516", "767-5677","AA Inc");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
  2  values(11,"Bob","Jack","S","12 Giant Rd.","Newark","NJ","27377","3298","908", "123-7367","Z Associates");
1 row created.
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE loadarray IS
  2
  3    TYPE cust_table_type IS TABLE OF VARCHAR2(100)
  4         INDEX BY BINARY_INTEGER;
  5
  6    cust_table  cust_table_type;
  7    indx   NUMBER := 0;
  8  BEGIN
  9
 10    FOR crec IN (select empl_no,
 11                 firstname ||" "|| lastname
 12                 AS name
 13                 from employee) LOOP
 14        cust_table(crec.empl_no) := crec.name;
 15    END LOOP;
 16
 17    indx := cust_table.FIRST;
 18    WHILE indx <= cust_table.LAST LOOP
 19      dbms_output.put_line( cust_table(indx) );
 20          indx := cust_table.NEXT(indx);
 21    END LOOP;
 22  END;
 23  /
Procedure created.
SQL> exec loadarray
Joe Jones
Sue Smith
Peggy Anderson
Jill Andy
Carl OK
Jee Peter
Paul Baker
Steve Young
Joe Mona
Karen Hackett
Jack Bob
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table employee;
Table dropped.

Extend Table collection

SQL>
SQL>
SQL> create table ord(
  2           order_no               integer          primary key
  3          ,empl_no                integer
  4          ,order_date             date not null
  5          ,total_order_price      number(7,2)
  6          ,deliver_date           date
  7          ,deliver_time           varchar2(7)
  8          ,payment_method         varchar2(2)
  9          ,emp_no                 number(3,0)
 10          ,deliver_name           varchar2(35)
 11          ,gift_message           varchar2(100)
 12  );
Table created.
SQL>
SQL>
SQL> insert into ord(order_no,empl_no,order_date,total_order_price,deliver_date,deliver_time,payment_method,emp_no,deliver_name,gift_message)
  2           values(1,1,"14-Feb-2002", 23.00, "14-Feb-2002", "12 noon", "CA",1, null, "Gift for wife");
1 row created.
SQL> insert into ord(order_no  ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time ,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(2,1,"14-Feb-2003", 510.98, "14-feb-2003", "5 pm", "NY",7, "Rose Ted", "Happy Valentines Day to Mother");
1 row created.
SQL> insert into ord(order_no  ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(3, 2,"14-Feb-2004", 315.99, "14-feb-2004", "3 pm", "VS",2, "Ani Forest", "Happy Valentines Day to Father");
1 row created.
SQL> insert into ord(order_no  ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(4, 2,"14-Feb-1999", 191.95, "14-feb-1999", "2 pm", "NJ",2, "O. John", "Happy Valentines Day");
1 row created.
SQL> insert into ord(order_no  ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message    )
  2           values(5, 6,"4-mar-2002", 101.95, "5-mar-2002", "2:30 pm", "MO"    , 2, "Cora", "Happy Birthday from John");
1 row created.
SQL> insert into ord(order_no  ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(6, 9,"7-apr-2003", 221.95, "7-apr-2003", "3 pm", "MA", 2, "Sake Keith", "Happy Birthday from Joe" );
1 row created.
SQL> insert into ord(order_no  ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(7, 9,"20-jun-2004", 315.95, "21-jun-2004", "12 noon", "BC", 2, "Jessica Li", "Happy Birthday from Jessica");
1 row created.
SQL> insert into ord(order_no  ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values (8, 12, "31-dec-1999", 135.95, "1-jan-2000", "12 noon", "DI",   3, "Larry", "Happy New Year from Lawrence");
1 row created.
SQL> insert into ord(order_no  ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values (9, 12, "26-dec-2003", 715.95, "2-jan-2004", "12 noon", "SK",7, "Did", "Happy Birthday from Nancy" );
1 row created.
SQL> insert into ord(order_no  ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(10, 4, sysdate-1, 119.95, sysdate+2, "6:30 pm", "VG",2, "P. Jing", "Happy Valentines Day to Jason");
1 row created.
SQL> insert into ord(order_no  ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(11, 2, sysdate, 310.00, sysdate+2, "3:30 pm", "DC",2, "C. Late", "Happy Birthday Day to Jack");
1 row created.
SQL> insert into ord(order_no  ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
  2           values(12, 7, sysdate-3, 121.95, sysdate-2, "1:30 pm", "AC",2, "W. Last", "Happy Birthday Day to You");
1 row created.
SQL> insert into ord(order_no  ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
  2           values(13, 7, sysdate, 211.95, sysdate-4, "4:30 pm", "CA",2, "J. Bond", "Thanks for hard working");
1 row created.
SQL>
SQL>
SQL> create or replace type sqlMONTH_TABLEtype as table of date;
  2  /
Type created.
SQL>
SQL> create or replace function month_generator
  2     (p_num_months in number)
  3     RETURN sqlMONTH_TABLEtype
  4  AS
  5      month_table     sqlMONTH_TABLEtype := sqlMONTH_TABLEtype();
  6  BEGIN
  7
  8      for i in 1..p_num_months loop
  9          month_table.extend(1);
 10          month_table(i) := add_months(sysdate, -i);
 11      end loop;
 12      return(month_table);
 13
 14  END;
 15  /
Function created.
SQL>
SQL> select to_Char(x.column_value, "mm/yyyy") , nvl(avg(total_order_price),0) as avg_Sales
  2  from TABLE( month_generator(12) ) x LEFT OUTER JOIN ord
  3  ON to_Char(x.column_value, "mm/yyyy") = to_Char(order_date, "mm/yyyy")
  4  group by to_Char(x.column_value, "mm/yyyy") ;
TO_CHAR  AVG_SALES
------- ----------
04/2008          0
03/2008          0
02/2008          0
10/2007          0
01/2008          0
05/2008          0
06/2008          0
12/2007          0
11/2007          0
07/2007          0
09/2007          0
TO_CHAR  AVG_SALES
------- ----------
08/2007          0
12 rows selected.
SQL>
SQL> drop table ord;
Table dropped.

Reference elements in table collection of varchar2 by index

SQL>
SQL>
SQL> DECLARE
  2    TYPE CAR_TABLE_TYPE IS TABLE OF VARCHAR2(20);
  3    CAR_TABLE CAR_TABLE_TYPE;
  4  BEGIN
  5    CAR_TABLE := CAR_TABLE_TYPE();
  6    CAR_TABLE.EXTEND(10);
  7    CAR_TABLE(1) := "DODGE";
  8    CAR_TABLE(2) := "FORD";
  9    CAR_TABLE(3) := "MUSTANG";
 10    CAR_TABLE(4) := "EDSEL";
 11    CAR_TABLE(5) := "STUDEBAKER";
 12
 13    CAR_TABLE.DELETE(4);
 14    CAR_TABLE(4):= "ROLLS";
 15
 16  END;
 17  /
PL/SQL procedure successfully completed.
SQL>
SQL>

Table of numbers: count, last, first

SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE random
  2  ( p_array_size in number)
  3   AS
  4
  5    TYPE t_values_array IS TABLE OF NUMBER
  6    INDEX BY BINARY_INTEGER;
  7
  8    random_value_array t_values_array;
  9
 10  BEGIN
 11      FOR x IN 1..p_array_size LOOP
 12        random_value_array(x) := dbms_random.random();
 13      END LOOP;
 14
 15      DBMS_OUTPUT.PUT_LINE ("Total rows stored: " || random_value_array.count);
 16
 17      DBMS_OUTPUT.PUT_LINE ("First row stored: " || random_value_array(random_value_array.FIRST)   );
 18      DBMS_OUTPUT.PUT_LINE ("Last row stored: " || random_value_array(random_value_array.LAST) );
 19
 20  END;
 21  /
Procedure created.
SQL>
SQL> show errors
No errors.
SQL>
SQL> execute random(500);
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL>