FETCH cursor BULK COLLECT : Cursor Fetch « Cursor « Oracle PL / SQL

1. Oracle PL / SQL
2. Cursor
3. Cursor Fetch

FETCH cursor BULK COLLECT

  
SQL>
SQL> CREATE TABLE MyTable (
  2    num_col    NUMBER,
  3    char_col   VARCHAR2(60));

Table created.

SQL>
SQL> set serveroutput on format wrapped
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('First query fetched ' || v_Numbers.COUNT || ' rows');
 38
 39    SELECT num_col
 40      BULK COLLECT INTO v_Numbers2
 41      FROM MyTable;
 42
 43    DBMS_OUTPUT.PUT_LINE('Second query fetched ' || v_Numbers2.COUNT || ' rows');
 44
 45    OPEN c_char;
 46    FETCH c_char BULK COLLECT INTO v_Strings2;
 47    CLOSE c_char;
 48
 49    DBMS_OUTPUT.PUT_LINE('Cursor fetch retrieved ' || v_Strings2.COUNT || ' rows');
 50
 51  END;
 52  /
First query fetched 1500 rows
Second query fetched 1500 rows
Cursor fetch retrieved 400 rows

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> drop table MyTable;

Table dropped.

SQL>
SQL>

   
  

Related examples in the same category