Different BULK_COLLECT statements used for bulk binds : Cursor Fetch « Cursor « Oracle PL / SQL






Different BULK_COLLECT statements used for bulk binds

  
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>

   
  








Related examples in the same category

1.Below is a function that demonstrates how to use the FETCH statement
2.Fetch value from cursor
3.Fetches the records from a cursor variable that returns employee information
4.Fetch cursor to two variables
5.legal and illegal FETCH statements.
6."ORA-1002: fetch out of sequence" because of the commit inside the SELECT..FOR UPDATE loop.
7.A WHILE cursor fetch loop.
8.Check Cursor%FOUND after fetching
9.Fetch value in cursor until NOTFOUND
10.ORA-01002: fetch out of sequence
11.Fetch out cursor value and insert to another table
12.PLS-00394: wrong number of values in the INTO list of a FETCH statement
13.FETCH cursor BULK COLLECT
14.Fetch cursor data to number variable
15.Fetch cursor till cursorVariable%notfound
16.Fetch cursor to table collection of row type
17.Fetch cursor value to three variables
18.Fetch row by row
19.Fetch single column cursor to varchar2 variable