Loop through table of number by index : Table of Number « Collections « Oracle PL/SQL Tutorial






SQL>
SQL>
SQL> create table employee
  2          (
  3           emp_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          ,salary                 number(5,2)
 15          ,birthdate              date
 16          ,hiredate               date
 17          ,title                  varchar2(20)
 18          ,dept_no                integer
 19        ,mgr              integer
 20        ,region           number
 21        ,division         number
 22        ,total_sales          number
 23         );

Table created.

SQL>
SQL>
SQL>
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, birthdate, title)
  2  values (1,'Gardinia','Joy','R','688 Ave','New York','NY','12122','2333','212','200-3393','12-nov-1956','President');

1 row created.

SQL>
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip, zip_4, area_code, phone, salary, birthdate, hiredate, title, dept_no, mgr, region, division, total_sales)
  2  values (2,'Anderson','Lucy','J','33 Ave','New York','NY','43552','6633','212','234-4444',7.75,'21-mar-1951','1-feb-1994','Sales Manager',2,1,100,10,40000);

1 row created.

SQL>
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip, zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
  2  values (3,'Somers','Ingrid','E','12 Ave','New York','NY','76822','8763','212','867-6893',7.75,'14-feb-1963','15-mar-1995','Sales Clerk',2,2,100,10,10000);

1 row created.

SQL>
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate, title, dept_no, mgr, region, division, total_sales)
  2  values (4,'Washington','Georgia','J','13th Street','New York','NY','43122','4333','212','340-4365',11.50,'2-jul-1963','21-apr-1994','Designer',1,1,100,10,40000);

1 row created.

SQL>
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip, zip_4, area_code, phone, salary, birthdate, hiredate, title, dept_no, mgr, region, division, total_sales)
  2  values (5,'Doright','Dudley','J','56 Langer Street','Staten Island','NY','23332','4983','718','777-4365',21.65,'15-may-1958','2-aug-1994','Designer',1,1,100,10,40000);

1 row created.

SQL>
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
  2  values ( 6,'Doright','Dorothy','R','56 Langer Street','Staten Island','NY','23332','4983','718','777-4365',24.65,'10-dec-1968','2-aug-1994','Designer',1,1,100,10,40000);

1 row created.

SQL>
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip, zip_4, area_code, phone, salary, birthdate, hiredate, title, dept_no, mgr, region, division, total_sales)
  2  values ( 7,'Perry','Donna','R','1st Ave','New York','NY','44444','3444','212','111-6893',7.75,'14-feb-1967','15-mar-1995','Sales Clerk',2,1,100,10,40000);

1 row created.

SQL>
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip, zip_4, area_code, phone, salary, birthdate, hiredate, title, dept_no, mgr, region, division, total_sales)
  2  values ( 8,'Roger','John','E','67 H Ave','New York','NY','33822','1163','212','122-6893',10.00,'14-jun-1956','15-mar-1995','Accountant',3,1,100,10,40000);

1 row created.

SQL>
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip, zip_4, area_code, phone, salary, birthdate, hiredate, title, dept_no, mgr, region, division, total_sales)
  2  values ( 9,'Hall','Ted','R','1236 Lane','New York','NY','33823','1164','212','222-4393',13.00,'10-jun-1959','15-aug-1997','Sales Representative',3,1,100,10,50000);

1 row created.

SQL>
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip, zip_4, area_code, phone, salary, birthdate, hiredate, title, dept_no, mgr, region, division, total_sales)
  2  values ( 10,'Barbee','Candice','L','400 Street','New York','NY','33811','2009','212','321-6873',12.00,'10-oct-1964','15-jan-1999','Sales Representative',3,1,100,10,35000);

1 row created.

SQL>
SQL>
SQL>
SQL> declare
  2    type num_type is    table of number;
  3    l_manager_array     num_type := num_type(1,2,3);
  4    child_record_found  exception;
  5    pragma    exception_init(child_record_found, -2292);
  6    v_index             number;
  7  begin
  8    forall i in l_manager_array.FIRST..l_manager_array.LAST
  9       delete from employee WHERE mgr = l_manager_array(i);
 10       dbms_output.put_line(sql%rowcount);
 11    exception
 12       when child_record_found then
 13           v_index := l_manager_array.first;
 14           dbms_output.put_line('child record found');
 15           for i in l_manager_array.FIRST..l_manager_array.LAST
 16           loop
 17             dbms_output.put_line('delete for manager '
 18                                  ||l_manager_array(v_index)
 19                                  ||' deleted '
 20                                  ||SQL%BULK_ROWCOUNT(v_index)
 21                                  ||' rows.');
 22             v_index := l_manager_Array.NEXT(v_index);
 23           end loop;
 24           commit;
 25  end;
 26  /
9

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> drop table employee;

Table dropped.

SQL>
SQL>








26.23.Table of Number
26.23.1.An example of declaring an Index-by table
26.23.2.Initializing a Nested Table
26.23.3.NULL and Empty Nested Tables and NULL elements
26.23.4.Is a table collection a null value
26.23.5.ORA-06531: Reference to uninitialized collection
26.23.6.Initialize table collection with null value
26.23.7.Accessing an Index-by table
26.23.8.Accessing an entire Index-by table
26.23.9.Accessing an undefined row of an Index-by table
26.23.10.Assigning rows of an Index-By table by means of a LOOP
26.23.11.Deleting an Index-by table using an empty Index-by table
26.23.12.Using the EXISTS method
26.23.13.An example of COUNT method
26.23.14.An example of the DELETE method
26.23.15.An example of the FIRST, LAST and NEXT methods
26.23.16.number_list.EXTEND(2): Add two null value members at the end of the list.
26.23.17.number_list.EXTEND(3,4): Add three members at the end of the list and copy the contents of item 4
26.23.18.Associative arrays
26.23.19.Accessing Nested Table elements
26.23.20.ORA-06533: Subscript beyond count
26.23.21.compare two tables of integers
26.23.22.Loop through table of number by index