Mega Code Archive
Different BULK_COLLECT statements used for bulk binds
<![CDATA[
SQL>
SQL> set serveroutput on format wrapped
SQL>
SQL> CREATE TABLE MyTable (
2 num_col NUMBER,
3 char_col VARCHAR2(60)
4 );
Table created.
SQL>
SQL>
SQL> DECLARE
2 TYPE t_Numbers IS TABLE OF MyTable.num_col%TYPE;
3 TYPE t_Strings IS TABLE OF MyTable.char_col%TYPE;
4 v_Numbers t_Numbers := t_Numbers(1);
5 v_Strings t_Strings := t_Strings(1);
6 v_Numbers2 t_Numbers;
7 v_Strings2 t_Strings;
8
9 CURSOR c_char IS
10 SELECT char_col
11 FROM MyTable
12 WHERE num_col > 800
13 ORDER BY num_col;
14
15 BEGIN
16 v_Numbers.EXTEND(1500);
17 v_Strings.EXTEND(1500);
18 FOR v_Count IN 1..1000 LOOP
19 v_Numbers(v_Count) := v_Count;
20 v_Strings(v_Count) := 'Element #' || v_Count;
21 IF v_Count > 500 THEN
22 v_Numbers(v_Count + 500) := v_Count;
23 v_Strings(v_Count + 500) := 'Element #' || v_Count;
24 END IF;
25 END LOOP;
26
27 DELETE FROM MyTable;
28 FORALL v_Count IN 1..1500
29 INSERT INTO MyTable (num_col, char_col)
30 VALUES (v_Numbers(v_Count), v_Strings(v_Count));
31
32 SELECT num_col, char_col
33 BULK COLLECT INTO v_Numbers, v_Strings
34 FROM MyTable
35 ORDER BY num_col;
36
37 DBMS_OUTPUT.PUT_LINE(
38 'First query fetched ' || v_Numbers.COUNT || ' rows');
39
40 SELECT num_col
41 BULK COLLECT INTO v_Numbers2
42 FROM MyTable;
43
44 DBMS_OUTPUT.PUT_LINE(
45 'Second query fetched ' || v_Numbers2.COUNT || ' rows');
46
47 OPEN c_char;
48 FETCH c_char BULK COLLECT INTO v_Strings2;
49 CLOSE c_char;
50
51 DBMS_OUTPUT.PUT_LINE(
52 'Cursor fetch retrieved ' || v_Strings2.COUNT || ' rows');
53
54 END;
55 /
First query fetched 1500 rows
Second query fetched 1500 rows
Cursor fetch retrieved 400 rows
PL/SQL procedure successfully completed.
SQL>
SQL> DECLARE
2 TYPE t_Numbers IS TABLE OF MyTable.num_col%TYPE
3 INDEX BY BINARY_INTEGER;
4 TYPE t_Strings IS TABLE OF MyTable.char_col%TYPE
5 INDEX BY BINARY_INTEGER;
6 v_Numbers t_Numbers;
7 v_Strings t_Strings;
8 BEGIN
9 DELETE FROM MyTable;
10 FOR v_Outer IN 1..10 LOOP
11 FOR v_Inner IN 1..v_Outer LOOP
12 INSERT INTO MyTable (num_col, char_col)
13 VALUES (v_Outer, 'Element #' || v_Inner);
14 END LOOP;
15 v_Numbers(v_Outer) := v_Outer;
16 END LOOP;
17
18 FORALL v_Count IN 1..5
19 DELETE FROM MyTable
20 WHERE num_col = v_Numbers(v_Count)
21 RETURNING char_col BULK COLLECT INTO v_Strings;
22
23 DBMS_OUTPUT.PUT_LINE('After delete:');
24 FOR v_Count IN 1..v_Strings.COUNT LOOP
25 DBMS_OUTPUT.PUT_LINE(
26 ' v_Strings(' || v_Count || ') = ' || v_Strings(v_Count));
27 END LOOP;
28 END;
29 /
After delete:
v_Strings(1) = Element #1
v_Strings(2) = Element #1
v_Strings(3) = Element #2
v_Strings(4) = Element #1
v_Strings(5) = Element #2
v_Strings(6) = Element #3
v_Strings(7) = Element #1
v_Strings(8) = Element #2
v_Strings(9) = Element #3
v_Strings(10) = Element #4
v_Strings(11) = Element #1
v_Strings(12) = Element #2
v_Strings(13) = Element #3
v_Strings(14) = Element #4
v_Strings(15) = Element #5
PL/SQL procedure successfully completed.
SQL>
SQL> select * from MyTable;
NUM_COL CHAR_COL
---------- ------------------------------------------------------------
6 Element #1
6 Element #2
6 Element #3
6 Element #4
6 Element #5
6 Element #6
7 Element #1
7 Element #2
7 Element #3
7 Element #4
7 Element #5
NUM_COL CHAR_COL
---------- ------------------------------------------------------------
7 Element #6
7 Element #7
8 Element #1
8 Element #2
8 Element #3
8 Element #4
8 Element #5
8 Element #6
8 Element #7
8 Element #8
9 Element #1
NUM_COL CHAR_COL
---------- ------------------------------------------------------------
9 Element #2
9 Element #3
9 Element #4
9 Element #5
9 Element #6
9 Element #7
9 Element #8
9 Element #9
10 Element #1
10 Element #2
10 Element #3
NUM_COL CHAR_COL
---------- ------------------------------------------------------------
10 Element #4
10 Element #5
10 Element #6
10 Element #7
10 Element #8
10 Element #9
10 Element #10
40 rows selected.
SQL>
SQL> drop table MyTable;
Table dropped.
SQL>
]]>