Mega Code Archive
Loop through table of number by index
<![CDATA[
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> 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> 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> drop table employee;
Table dropped.
SQL>
]]>