SQL>
SQL> set serveroutput on size 1000000
SQL>
SQL> CREATE OR REPLACE type integer_table is table of integer;
2 /
Type created.
SQL>
SQL> DECLARE
2
3
4 vendor_key_table integer_table;
5 vendor_key_table2 integer_table;
6 CURSOR tst
7 IS
8 SELECT purch_order, SUM (dollars),
9 CAST (COLLECT (TO_NUMBER (vendor_key)) AS integer_table)
10 FROM (SELECT 1 purch_order, 3 dollars, 435235 vendor_key
11 FROM DUAL
12 UNION ALL
13 SELECT 1 purch_order, 8 dollars, 123452 vendor_key
14 FROM DUAL
15 UNION ALL
16 SELECT 2 purch_order, 7 dollars, 433738 vendor_key
17 FROM DUAL
18 UNION ALL
19 SELECT 2 purch_order, 4 dollars, 383738 vendor_key
20 FROM DUAL
21 UNION ALL
22 SELECT 2 purch_order, 5 dollars, 387118 vendor_key
23 FROM DUAL)
24 GROUP BY purch_order;
25
26
27 CURSOR tst2
28 IS
29 SELECT purch_order, SUM (dollars),
30 CAST (COLLECT (TO_NUMBER (vendor_key)) AS integer_table)
31 FROM (SELECT 1 purch_order, 3 dollars, 435235 vendor_key
32 FROM DUAL
33 UNION ALL
34 SELECT 2 purch_order, 4 dollars, 383738 vendor_key
35 FROM DUAL
36 UNION ALL
37 SELECT 2 purch_order, 7 dollars, 433738 vendor_key
38 FROM DUAL
39 UNION ALL
40 SELECT 2 purch_order, 5 dollars, 387118 vendor_key
41 FROM DUAL)
42 GROUP BY purch_order;
43 v_purch_order NUMBER;
44 v_dollars NUMBER;
45
46
47 mystr VARCHAR2 (4000);
48
49
50 v_purch_order2 NUMBER;
51 v_dollars2 NUMBER;
52
53
54 mystr2 VARCHAR2 (4000);
55 BEGIN
56 OPEN tst;
57 open tst2;
58
59
60 LOOP
61 mystr := NULL;
62 mystr2 := NULL;
63
64
65 FETCH tst
66 INTO v_purch_order, v_dollars, vendor_key_table;
67
68
69 FETCH tst2
70 INTO v_purch_order2, v_dollars2, vendor_key_table2;
71
72
73 IF tst%NOTFOUND
74 THEN
75 EXIT;
76 END IF;
77
78
79 if vendor_key_table = vendor_key_table2 then
80 dbms_output.put_line('equal');
81 else
82 dbms_output.put_line(' not equal');
83 end if;
84
85
86 -- loop through the collection and build a string so that
87 -- we can display it and prove that it works
88 FOR cur1 IN (SELECT COLUMN_VALUE vendor_key
89 FROM TABLE (vendor_key_table))
90 LOOP
91 mystr := mystr || ',' || cur1.vendor_key;
92 -- /* based on the value of the sum, you can do something with each detail*/
93 -- if v_dollars > 12 then
94 -- UPDATE VENDOR SET paid_status = 'P' where vendor_key = cur1.vendor_key;
95 -- end if;
96 END LOOP;
97
98
99 DBMS_OUTPUT.put_line ( 'Purchace Order-> '
100 || TO_CHAR (v_purch_order)
101 || ' dollar total-> '
102 || TO_CHAR (v_dollars)
103 || ' vendorkey list-> '
104 || SUBSTR (mystr, 2));
105
106
107 -- loop throught the collection and build a string so that
108 -- we can display it and prove that it works
109 FOR cur2 IN (SELECT COLUMN_VALUE vendor_key
110 FROM TABLE (vendor_key_table2))
111 LOOP
112 mystr2 := mystr2 || ',' || cur2.vendor_key;
113 END LOOP;
114
115
116 DBMS_OUTPUT.put_line ( 'Purchace Order-> '
117 || TO_CHAR (v_purch_order2)
118 || ' dollar total-> '
119 || TO_CHAR (v_dollars2)
120 || ' vendorkey list-> '
121 || SUBSTR (mystr2, 2));
122
123
124 END LOOP;
125 CLOSE tst;
126 CLOSE tst2;
127 END;
128 /
not equal
Purchace Order-> 1 dollar total-> 11 vendorkey list-> 435235,123452
Purchace Order-> 1 dollar total-> 3 vendorkey list-> 435235
equal
Purchace Order-> 2 dollar total-> 16 vendorkey list-> 433738,387118,383738
Purchace Order-> 2 dollar total-> 16 vendorkey list-> 383738,387118,433738
PL/SQL procedure successfully completed.
SQL>
Comparing the collections rather than looping through each table makes the work much easier to complete. Notice we only needed one type created; the same type satisfied the conditions for both collection tables.
The next example shows how things can go astray with the bulk collect operation when the limit does not evenly divide the result set. In the first part of the example we use the well-known 'exit when cursor%notfound;' directive with less than stellar results (we miss inserting 5 records into the second table); the second part of the example shows how to properly implement an exit from a bulk collect operation; this one uses a varray:
SQL>
SQL> set echo on linesize 150 trimspool on
SQL>
SQL> create table emp_test as select * From emp where 0=1;
Table created.
SQL>
SQL> declare
2 type empcoltyp is table of emp%rowtype;
3 emp_c empcoltyp;
4
5 cursor get_emp_data is
6 select * from emp;
7
8 begin
9 open get_emp_data;
10 loop
11 fetch get_emp_data bulk collect into emp_c limit 9;
12 exit when get_emp_data%notfound;
13
14 for i in 1..emp_c.count loop
15 insert into emp_test (empno, ename, sal)
16 values (emp_c(i).empno, emp_c(i).ename, emp_c(i).sal);
17 end loop;
18
19 end loop;
20
21 commit;
22
23 end;
24 /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from emp_test;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH 800
7499 ALLEN 1600
7521 WARD 1250
7566 JONES 2975
7654 MARTIN 1250
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7839 KING 5000
9 rows selected.
SQL>
SQL> truncate table emp_test;
Table truncated.
SQL>
SQL> declare
2 type empcoltyp is table of emp%rowtype;
3 emp_c empcoltyp;
4
5 cursor get_emp_data is
6 select * from emp;
7
8 begin
9 open get_emp_data;
10 loop
11 fetch get_emp_data bulk collect into emp_c limit 9;
12 exit when emp_c.count = 0;
13
14 for i in 1..emp_c.count loop
15 insert into emp_test (empno, ename, sal)
16 values (emp_c(i).empno, emp_c(i).ename, emp_c(i).sal);
17 end loop;
18
19 end loop;
20
21 commit;
22
23 end;
24 /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from emp_test;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH 800
7499 ALLEN 1600
7521 WARD 1250
7566 JONES 2975
7654 MARTIN 1250
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7839 KING 5000
7844 TURNER 1500
7876 ADAMS 1100
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES 950
7902 FORD 3000
7934 MILLER 1300
14 rows selected.
SQL>
What happened in the first part? Since the limit was more than the number of remaining records the NOTFOUND indicator was set at the end of the fetch. We had 5 records left to process in the varray but the 'exit when cursor%notfound;' statement terminated the loop BEFORE we could get the remainng 5 records inserted into our table, thus they were lost. Using the 'exit when collection.count = 0;' construct prevents us from missing records since the count was greater than 0 even when the NOTFOUND indicator was set. This allows us to process the remaining records in the varray before exiting the loop. [Yes, the exit could be coded at the end of the loop rather than the beginning and the 'exit when cursor%NOTFOUND;' would process the remaing records but that, to me, defeats the purpose of the conditional exit. As I see it we want to exit the loop when no more work is to be done, not look for partial sets of data to apply then exit before the next (unsuccessful) fetch.]
Our next example does two things: loads data using bulk collect into a varray then uses the FORALL loop construct to quickly process the collection and insert the data into a staging table. The second part is a bit contrived as it uses a collection to process deletes from a table -- deletes that could have easily been executed with a single SQL statement -- but it does show the power of using collections and varrays:
SQL>
SQL> set echo on timing on
SQL>
SQL> create table stage_data(
2 uname varchar2(30),
3 ujob varchar2(20),
4 usal number
5 );
Table created.
SQL>
SQL> begin
2 for i in 1..1000000 loop
3 insert into stage_data
4 values ('Blorp'||i, 'Job'||i, 1200*(mod(i,3)));
5 end loop;
6
7 commit;
8
9 end;
10 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> create table forall_load(
2 uname varchar2(30),
3 ujob varchar2(20),
4 usal number
5 );
Table created.
SQL>
SQL> create table forall_load2(
2 uname varchar2(30),
3 ujob varchar2(20),
4 usal number
5 );
Table created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE fast_way IS
2
3 TYPE myarray IS TABLE OF stage_data%ROWTYPE;
4 l_data myarray;
5
6 CURSOR r IS
7 SELECT *
8 FROM stage_data;
9
10 BEGIN
11 OPEN r;
12 LOOP
13 FETCH r BULK COLLECT INTO l_data LIMIT 1000;
14
15 FORALL i IN 1..l_data.COUNT
16 INSERT INTO forall_load VALUES l_data(i);
17 FORALL i IN 1..l_data.COUNT
18 INSERT INTO forall_load2 VALUES l_data(i);
19
20 EXIT WHEN l_data.count=0;
21 END LOOP;
22 COMMIT;
23 CLOSE r;
24 END fast_way;
25 /
Procedure created.
SQL>
SQL> exec fast_way;
PL/SQL procedure successfully completed.
SQL>
SQL> select * from forall_load where uname like '%99999%';
UNAME UJOB USAL
------------------------------ -------------------- ----------
Blorp99999 Job99999 0
Blorp199999 Job199999 1200
Blorp299999 Job299999 2400
Blorp399999 Job399999 0
Blorp499999 Job499999 1200
Blorp599999 Job599999 2400
Blorp699999 Job699999 0
Blorp799999 Job799999 1200
Blorp899999 Job899999 2400
Blorp999990 Job999990 0
Blorp999991 Job999991 1200
UNAME UJOB USAL
------------------------------ -------------------- ----------
Blorp999992 Job999992 2400
Blorp999993 Job999993 0
Blorp999994 Job999994 1200
Blorp999995 Job999995 2400
Blorp999996 Job999996 0
Blorp999997 Job999997 1200
Blorp999998 Job999998 2400
Blorp999999 Job999999 0
19 rows selected.
SQL> select * from forall_load2 where uname like '%99999%';
UNAME UJOB USAL
------------------------------ -------------------- ----------
Blorp99999 Job99999 0
Blorp199999 Job199999 1200
Blorp299999 Job299999 2400
Blorp399999 Job399999 0
Blorp499999 Job499999 1200
Blorp599999 Job599999 2400
Blorp699999 Job699999 0
Blorp799999 Job799999 1200
Blorp899999 Job899999 2400
Blorp999990 Job999990 0
Blorp999991 Job999991 1200
UNAME UJOB USAL
------------------------------ -------------------- ----------
Blorp999992 Job999992 2400
Blorp999993 Job999993 0
Blorp999994 Job999994 1200
Blorp999995 Job999995 2400
Blorp999996 Job999996 0
Blorp999997 Job999997 1200
Blorp999998 Job999998 2400
Blorp999999 Job999999 0
19 rows selected.
SQL>
Trust me that the data loads took very little time to process. Here is the contrived part, but it is still a good example of the power of using collections:
SQL> CREATE OR REPLACE PROCEDURE del_rows IS
2
3 TYPE myarray IS TABLE OF stage_data.uname%TYPE;
4 l_data myarray;
5
6 CURSOR r IS
7 SELECT uname
8 FROM stage_data
9 where uname like '%9999%';
10
11 BEGIN
12 OPEN r;
13 LOOP
14 FETCH r BULK COLLECT INTO l_data LIMIT 1000;
15
16 FORALL i IN 1..l_data.COUNT
17 delete from forall_load where uname = l_data(i);
18 FORALL i IN 1..l_data.COUNT
19 delete from forall_load2 where uname = l_data(i);
20
21 EXIT WHEN l_data.count=0;
22 END LOOP;
23 COMMIT;
24 CLOSE r;
25 END del_rows;
26 /
Procedure created.
SQL>
SQL> show errors
No errors.
SQL>
SQL> exec del_rows;
PL/SQL procedure successfully completed.
SQL>
SQL> select * from forall_load where uname like '%99999%';
no rows selected
SQL> select * from forall_load2 where uname like '%99999%';
no rows selected
SQL>
Collections can be a real timesaver for bulk processng of data; they may not be applicable in every sitution but when the conditions are right they can make your job so much easier. Master collections and varrays and you may be able to amaze your friends.
Collections, anyone?
