Sunday, March 27, 2016

Oracle PL/SQL Bulk Collect Example

--> This script illustrates several different BULK_COLLECT statements, used for bulk binds

set serveroutput on format wrapped

DECLARE
  TYPE t_Numbers IS TABLE OF temp_table.num_col%TYPE;
  TYPE t_Strings IS TABLE OF temp_table.char_col%TYPE;
  v_Numbers t_Numbers := t_Numbers(1);
  v_Strings t_Strings := t_Strings(1);
  v_Numbers2 t_Numbers;
  v_Strings2 t_Strings;

  CURSOR c_char IS
    SELECT char_col
    FROM temp_table
    WHERE num_col > 800
    ORDER BY num_col;

BEGIN
  -- First load temp_table with 1500 rows, 500 of which are duplicates.
  v_Numbers.EXTEND(1500);
  v_Strings.EXTEND(1500);
  FOR v_Count IN 1..1000 LOOP
    v_Numbers(v_Count) := v_Count;
    v_Strings(v_Count) := 'Element #' || v_Count;
    IF v_Count > 500 THEN
      v_Numbers(v_Count + 500) := v_Count;
      v_Strings(v_Count + 500) := 'Element #' || v_Count;
    END IF;
  END LOOP;

  DELETE FROM temp_table;
  FORALL v_Count IN 1..1500
    INSERT INTO temp_table (num_col, char_col)
      VALUES (v_Numbers(v_Count), v_Strings(v_Count));

  -- Grab all of the rows back into the nested tables in one operation.
  SELECT num_col, char_col
    BULK COLLECT INTO v_Numbers, v_Strings
    FROM temp_table
    ORDER BY num_col;

  DBMS_OUTPUT.PUT_LINE(
    'First query fetched ' || v_Numbers.COUNT || ' rows');

  -- The table does not have to be initialized, the BULK COLLECT will add elements as needed:
  SELECT num_col
    BULK COLLECT INTO v_Numbers2
    FROM temp_table;

  DBMS_OUTPUT.PUT_LINE(
    'Second query fetched ' || v_Numbers2.COUNT || ' rows');

  -- We can bulk fetch from a cursor as well.
  OPEN c_char;
  FETCH c_char BULK COLLECT INTO v_Strings2;
  CLOSE c_char;

  DBMS_OUTPUT.PUT_LINE(
    'Cursor fetch retrieved ' || v_Strings2.COUNT || ' rows');
END;
/

DECLARE
  TYPE t_Numbers IS TABLE OF temp_table.num_col%TYPE
    INDEX BY BINARY_INTEGER;
  TYPE t_Strings IS TABLE OF temp_table.char_col%TYPE
    INDEX BY BINARY_INTEGER;
  v_Numbers t_Numbers;
  v_Strings t_Strings;
BEGIN
  -- Delete from the table, and then insert 55 rows.  Also set up t_Numbers here.
  DELETE FROM temp_table;
  FOR v_Outer IN 1..10 LOOP
    FOR v_Inner IN 1..v_Outer LOOP
      INSERT INTO temp_table (num_col, char_col)
        VALUES (v_Outer, 'Element #' || v_Inner);
    END LOOP;
    v_Numbers(v_Outer) := v_Outer;
  END LOOP;

  -- Delete some of the rows, but save the character data.
  FORALL v_Count IN 1..5
    DELETE FROM temp_table
      WHERE num_col = v_Numbers(v_Count)
      RETURNING char_col BULK COLLECT INTO v_Strings;

  -- v_Strings now contains 15 rows, which is 1+2+3+4+5.
  DBMS_OUTPUT.PUT_LINE('After delete:');
  FOR v_Count IN 1..v_Strings.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(
      '  v_Strings(' || v_Count || ') = ' || v_Strings(v_Count));
  END LOOP;
END;
/


No comments:

Post a Comment

Best Blogger TipsGet Flower Effect