`

oracle bulk collection

OracleSQLCC++C# 
阅读更多
Oracle Bulk Collection & FORALL
Version 11.1
Array Processing
Note: Without the bulk bind, PL/SQL sends a SQL statement to the SQL engine for each record that is inserted, updated, or deleted leading to context switches that hurt performance.
 
BULK COLLECT

BULK COLLECT Syntax		 FETCH BULK COLLECT <cursor_name> BULK COLLECT INTO <collection_name>
LIMIT <numeric_expression>;

or

FETCH BULK COLLECT <cursor_name> BULK COLLECT INTO <array_name>
LIMIT <numeric_expression>;
set timing on

DECLARE
 CURSOR a_cur IS
 SELECT program_id
 FROM airplanes;
BEGIN
  FOR cur_rec IN a_cur LOOP
    NULL;
  END LOOP;
END;
/

DECLARE
 CURSOR a_cur IS
 SELECT program_id
 FROM airplanes;

 TYPE myarray IS TABLE OF a_cur%ROWTYPE;
 cur_array myarray;
BEGIN
  OPEN a_cur;
  LOOP
    FETCH a_cur BULK COLLECT INTO cur_array LIMIT 100;
    EXIT WHEN a_cur%NOTFOUND;
  END LOOP;
  CLOSE a_cur;
END;
/

DECLARE
 CURSOR a_cur IS
 SELECT program_id
 FROM airplanes;

 TYPE myarray IS TABLE OF a_cur%ROWTYPE;
 cur_array myarray;
BEGIN
  OPEN a_cur;
  LOOP
    FETCH a_cur BULK COLLECT INTO cur_array LIMIT 500;
    EXIT WHEN a_cur%NOTFOUND;
  END LOOP;
  CLOSE a_cur;
END;
/

DECLARE
 CURSOR a_cur IS
 SELECT program_id
 FROM airplanes;

 TYPE myarray IS TABLE OF a_cur%ROWTYPE;
 cur_array myarray;
BEGIN
  OPEN a_cur;
  LOOP
    FETCH a_cur BULK COLLECT INTO cur_array LIMIT 1000;
    EXIT WHEN a_cur%NOTFOUND;
  END LOOP;
  CLOSE a_cur;
END;
/

-- try with a LIMIT clause of 2500, 5000, and 10000. What do you see?
 
FORALL

FORALL Syntax		 FORALL <index_name> IN <lower_boundary> .. <upper_boundary>
<sql_statement>
SAVE EXCEPTIONS;

FORALL <index_name> IN INDICES OF <collection>
[BETWEEN <lower_boundary> AND <upper_boundary>]
<sql_statement>
SAVE EXCEPTIONS;

FORALL <index_name> IN INDICES OF <collection>
VALUES OF <index_collection>
<sql_statement>
SAVE EXCEPTIONS;

FORALL Insert 		CREATE TABLE servers2 AS
SELECT *
FROM servers
WHERE 1=2;

DECLARE
 CURSOR s_cur IS
 SELECT *
 FROM servers;

 TYPE fetch_array IS TABLE OF s_cur%ROWTYPE;
 s_array fetch_array;
BEGIN
  OPEN s_cur;
  LOOP
    FETCH s_cur BULK COLLECT INTO s_array LIMIT 1000;

    FORALL i IN 1..s_array.COUNT
    INSERT INTO servers2 VALUES s_array(i);

    EXIT WHEN s_cur%NOTFOUND;
  END LOOP;
  CLOSE s_cur;
  COMMIT;
END;
/

FORALL Update 		SELECT DISTINCT srvr_id
FROM servers2
ORDER BY 1;

DECLARE
 TYPE myarray IS TABLE OF servers2.srvr_id%TYPE
 INDEX BY BINARY_INTEGER;

 d_array myarray;
BEGIN
  d_array(1) := 608;
  d_array(2) := 610;
  d_array(3) := 612;

  FORALL i IN d_array.FIRST .. d_array.LAST
  UPDATE servers2
  SET srvr_id = 0
  WHERE srvr_id = d_array(i);

  COMMIT;
END;
/

SELECT srvr_id
FROM servers2
WHERE srvr_id = 0;

FORALL Delete 		set serveroutput on

DECLARE
 TYPE myarray IS TABLE OF servers2.srvr_id%TYPE
 INDEX BY BINARY_INTEGER;

 d_array myarray;
BEGIN
  d_array(1) := 614;
  d_array(2) := 615;
  d_array(3) := 616;

  FORALL i IN d_array.FIRST .. d_array.LAST
  DELETE servers2
  WHERE srvr_id = d_array(i);

  COMMIT;

  FOR i IN d_array.FIRST .. d_array.LAST LOOP
    dbms_output.put_line('Iteration #' || i || ' deleted ' ||
    SQL%BULK_ROWCOUNT(i) || ' rows.');
  END LOOP;
END;
/

SELECT srvr_id
FROM servers2
WHERE srvr_id IN (614, 615, 616);
 
Performance Demos

Performance Comparison
CREATE TABLE t1 (pnum INTEGER, pname VARCHAR2(15));
CREATE TABLE t2 AS SELECT * FROM t1;

CREATE OR REPLACE PROCEDURE perf_compare(iterations PLS_INTEGER) IS

 TYPE NumTab IS TABLE OF t1.pnum%TYPE INDEX BY PLS_INTEGER;
 TYPE NameTab IS TABLE OF t1.pname%TYPE INDEX BY PLS_INTEGER;
 pnums  NumTab;
 pnames NameTab;

 a INTEGER;
 b INTEGER;
 c INTEGER;
BEGIN
  FOR j IN 1..iterations LOOP -- load index-by tables
    pnums(j) := j;
    pnames(j) := 'Part No. ' || TO_CHAR(j);
  END LOOP;

  a := dbms_utility.get_time;

  FOR i IN 1..iterations LOOP -- use FOR loop
    INSERT INTO t1 VALUES (pnums(i), pnames(i));
  END LOOP;

  b := dbms_utility.get_time;

  FORALL i IN 1 .. iterations -- use FORALL statement
  INSERT INTO t2 VALUES (pnums(i), pnames(i));

  c := dbms_utility.get_time;

  dbms_output.put_line('Execution Time (secs)');
  dbms_output.put_line('---------------------');
  dbms_output.put_line('FOR loop: ' || TO_CHAR((b - a)/100));
  dbms_output.put_line('FORALL: ' || TO_CHAR((c - b)/100));
  COMMIT;
END perf_compare;
/

set serveroutput on

exec perf_compare(500);
exec perf_compare(5000);
exec perf_compare(50000);
 
Bulk Collection Demo Table CREATE TABLE parent (
part_num  NUMBER,
part_name VARCHAR2(15));

CREATE TABLE child AS
SELECT *
FROM parent;

Create And Load Demo Data 		DECLARE
 j PLS_INTEGER := 1;
 k parent.part_name%TYPE := 'Transducer';
BEGIN
  FOR i IN 1 .. 200000
  LOOP
    SELECT DECODE(k, 'Transducer', 'Rectifier',
    'Rectifier', 'Capacitor',
    'Capacitor', 'Knob',
    'Knob', 'Chassis',
    'Chassis', 'Transducer')
    INTO k
    FROM dual;

    INSERT INTO parent VALUES (j+i, k);
  END LOOP;
  COMMIT;
END;
/

SELECT COUNT(*) FROM parent;
SELECT COUNT(*) FROM child;

Slow Way		 CREATE OR REPLACE PROCEDURE slow_way IS

BEGIN
  FOR r IN (SELECT * FROM parent)
  LOOP
    -- modify record values
    r.part_num := r.part_num * 10;
    -- store results
    INSERT INTO child
    VALUES
    (r.part_num, r.part_name);
  END LOOP;
  COMMIT;
END slow_way;
/

set timing on

exec slow_way -- 07.71

Fast Way 1

Fetch into user defined array		 CREATE OR REPLACE PROCEDURE fast_way IS

TYPE myarray IS TABLE OF parent%ROWTYPE;
l_data myarray;

CURSOR r IS
SELECT part_num, part_name
FROM parent;

BEGIN
  OPEN r;
  LOOP
    FETCH r BULK COLLECT INTO l_data LIMIT 1000;

    FOR j IN 1 .. l_data.COUNT
    LOOP
      l_data(j).part_num := l_data(j).part_num * 10;
    END LOOP;

    FORALL i IN 1..l_data.COUNT
    INSERT INTO child VALUES l_data(i);

    EXIT WHEN r%NOTFOUND;
  END LOOP;
  COMMIT;
  CLOSE r;
END fast_way;
/

set timing on

exec fast_way -- 00.50

set timing off

SELECT 7.71/0.50 FROM dual;

Fast Way 2

Fetch into user defined PL/SQL table		 CREATE OR REPLACE PROCEDURE fast_way IS

TYPE PartNum IS TABLE OF parent.part_num%TYPE
INDEX BY BINARY_INTEGER;
pnum_t PartNum;

TYPE PartName IS TABLE OF parent.part_name%TYPE
INDEX BY BINARY_INTEGER;
pnam_t PartName;

BEGIN
  SELECT part_num, part_name
  BULK COLLECT INTO pnum_t, pnam_t
  FROM parent;

  FOR i IN pnum_t.FIRST .. pnum_t.LAST
  LOOP
    pnum_t(i) := pnum_t(i) * 10;
  END LOOP;

  FORALL i IN pnum_t.FIRST .. pnum_t.LAST
  INSERT INTO child
  (part_num, part_name)
  VALUES
  (pnum_t(i), pnam_t(i));
  COMMIT;
END fast_way;
/

set timing on

exec fast_way -- 0.62

Fast Way 3

Fetch into DBMS_SQL defined array		 CREATE OR REPLACE PROCEDURE fast_way IS

TYPE parent_rec IS RECORD (
part_num   dbms_sql.number_table,
part_name  dbms_sql.varchar2_table);

p_rec parent_rec;

CURSOR c IS
SELECT part_num, part_name
FROM parent;

l_done BOOLEAN;

BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO p_rec.part_num, p_rec.part_name
    LIMIT 500;
    l_done := c%NOTFOUND;

    FOR i IN 1 .. p_rec.part_num.COUNT
    LOOP
      p_rec.part_num(i) := p_rec.part_num(i) * 10;
    END LOOP;

    FORALL i IN 1 .. p_rec.part_num.COUNT
    INSERT INTO child
    (part_num, part_name)
    VALUES
    (p_rec.part_num(i), p_rec.part_name(i));

    EXIT WHEN (l_done);
  END LOOP;
  COMMIT;
  CLOSE c;
END fast_way;
/

set timing on

exec fast_way -- 0.51

Fast Way 4

Affect of triggers on performance of cursor loops vs. array processing		 TRUNCATE TABLE child;

set timing on

exec slow_way;
exec fast_way;

set timing off

TRUNCATE TABLE child;

CREATE OR REPLACE TRIGGER bi_child
BEFORE INSERT
ON child
FOR EACH ROW
BEGIN
  NULL;
END bi_child;
/

set timing on

exec slow_way;
-- Elapsed: 00:05:54.36

exec fast_way;
-- Elapsed: 00:00:01.96

Fast Way 5

Insert into multiple tables		 TRUNCATE TABLE child;

RENAME child TO child1;

CREATE TABLE child2 AS
SELECT * FROM child1;
 
CREATE OR REPLACE PROCEDURE fast_way IS

TYPE myarray IS TABLE OF parent%ROWTYPE;
l_data myarray;

CURSOR r IS
SELECT part_num, part_name
FROM parent;

BEGIN
  OPEN r;
  LOOP
    FETCH r BULK COLLECT INTO l_data LIMIT 1000;

    FOR j IN 1 .. l_data.COUNT LOOP
      l_data(j).part_num := l_data(j).part_num * 10;
    END LOOP;

    FORALL i IN 1..l_data.COUNT
    INSERT INTO child1 VALUES l_data(i);

    FORALL i IN 1..l_data.COUNT
    INSERT INTO child2 VALUES l_data(i);

    EXIT WHEN r%NOTFOUND;
  END LOOP;
  COMMIT;
  CLOSE r;
END fast_way;
/

set timing on

exec fast_way
 
Partial Collections

Part of Collection Demo		 CREATE TABLE test (
deptno  NUMBER(3,0),
empname VARCHAR2(20));

INSERT INTO test VALUES (100, 'Morgan');
INSERT INTO test VALUES (200, 'Allen');
INSERT INTO test VALUES (101, 'Lofstrom');
INSERT INTO test VALUES (102, 'Havemeyer');
INSERT INTO test VALUES (202, 'Norgaard');
INSERT INTO test VALUES (201, 'Lewis');
INSERT INTO test VALUES (103, 'Scott');
INSERT INTO test VALUES (104, 'Foote');
INSERT INTO test VALUES (105, 'Townsend');
INSERT INTO test VALUES (106, 'Abedrabbo');
COMMIT;

SELECT * FROM test;

CREATE OR REPLACE PROCEDURE collection_part IS
 TYPE NumList IS VARRAY(10) OF NUMBER;
 depts NumList := NumList(100,200,101,102,202,201,103,104,105,106);
BEGIN
  FORALL j IN 4..7 -- use only part of varray
  DELETE FROM test WHERE deptno = depts(j);
  COMMIT;
END collection_part;
/

SELECT * FROM test;
 
Sparse Collection
Note: A sparse collection is one from which elements have been deleted.

Sparse Collection Demo using IN INDICES OF		 ALTER TABLE child
ADD CONSTRAINT uc_child_part_num
UNIQUE (part_num)
USING INDEX;

DECLARE
 TYPE typ_part_name IS TABLE OF parent%ROWTYPE;
 v_part  typ_part_name;
BEGIN
  SELECT *
  BULK COLLECT INTO v_part
  FROM parent;

  FOR rec IN 1 .. v_part.LAST()
  LOOP
    IF v_part(rec).part_name != 'Rectifier' THEN
      v_part.delete(rec);
    END IF;
  END LOOP;

  FORALL i IN 1 .. v_part.COUNT
  INSERT INTO child
  VALUES
  v_part(i);

  COMMIT;
END;
/

DECLARE
  TYPE typ_part_name IS TABLE OF parent%ROWTYPE;
  v_part  typ_part_name;
BEGIN
  SELECT *
  BULK COLLECT INTO v_part
  FROM parent;

  FOR rec IN 1 .. v_part.LAST
  LOOP
    IF v_part(rec).part_name != 'Rectifier' THEN
      v_part.delete(rec);
    END IF;
  END LOOP;

  FORALL idx IN INDICES OF v_part
  INSERT INTO child
  VALUES
  v_part(idx);

  COMMIT;
END;
/

SELECT COUNT(*) FROM parent;

SELECT COUNT(*) FROM child;

Using INDICES OF and VALUES OF with Non-Consecutive Index Values		 CREATE TABLE valid_orders (
cust_name VARCHAR2(32),
amount NUMBER(10,2));

CREATE TABLE big_orders AS
SELECT * FROM valid_orders WHERE 1 = 0;

CREATE TABLE rejected_orders AS
SELECT * FROM valid_orders WHERE 1 = 0;

DECLARE
  -- collections to hold a set of customer names and amounts 
 SUBTYPE cust_name IS valid_orders.cust_name%TYPE;
 TYPE cust_typ IS TABLe OF cust_name;
 cust_tab cust_typ;

 SUBTYPE order_amount IS valid_orders.amount%TYPE;
 TYPE amount_typ IS TABLE OF NUMBER;
 amount_tab amount_typ;

  -- collections to point into the CUST_TAB collection.
 TYPE index_pointer_t IS TABLE OF PLS_INTEGER;

 big_order_tab index_pointer_t := index_pointer_t();

 rejected_order_tab index_pointer_t := index_pointer_t();

PROCEDURE setup_data IS
BEGIN
  -- Set up sample order data, with some invalid and 'big' orders
  cust_tab := cust_typ('Company1', 'Company2', 'Company3',
  'Company4', 'Company5');

  amount_tab := amount_typ(5000.01, 0, 150.25, 4000.00, NULL);
END setup_data;

BEGIN
  setup_data;

  dbms_output.put_line('--- Original order data ---');
  FOR i IN 1..cust_tab.LAST
  LOOP
    dbms_output.put_line('Cust#' || i || ', '|| cust_tab(i) ||
    ': $'||amount_tab(i));
  END LOOP;

  -- Delete invalid orders (where amount is null or 0)
  FOR i IN 1..cust_tab.LAST
  LOOP
    IF amount_tab(i) is null or amount_tab(i) = 0 THEN
      cust_tab.delete(i);
      amount_tab.delete(i);
    END IF;
  END LOOP;

  dbms_output.put_line('---Data with deleted invalid orders---');

  FOR i IN 1..cust_tab.LAST LOOP
    IF cust_tab.EXISTS(i) THEN
      dbms_output.put_line('Cust#' || i || ', ' || cust_tab(i) ||
      ': $'||amount_tab(i));
    END IF;
  END LOOP;

  -- Since the subscripts of our collections are not consecutive,
  -- we use use FORRALL...INDICES OF to iterate the subscripts
  FORALL i IN INDICES OF cust_tab
  INSERT INTO valid_orders
  (cust_name, amount)
  VALUES
  (cust_tab(i), amount_tab(i));

  -- Now let's process the order data differently extracting 
  --  2 subsets and storing each subset in a different table.
  setup_data; -- Reinitialize the CUST_TAB and AMOUNT_TAB collections

  FOR i IN cust_tab.FIRST .. cust_tab.LAST
  LOOP
    IF amount_tab(i) IS NULL OR amount_tab(i) = 0 THEN
      -- add a new element to the collection
      rejected_order_tab.EXTEND;
      -- record original collection subscript
      rejected_order_tab(rejected_order_tab.LAST) := i;
    END IF;

    IF amount_tab(i) > 2000 THEN
      -- Add a new element to the collection
      big_order_tab.EXTEND;
      -- record original collection subscript
      big_order_tab(big_order_tab.LAST) := i;
    END IF;
  END LOOP;

  -- run one DML statement on one subset of elements,
  -- and another DML statement on a different subset.
  FORALL i IN VALUES OF rejected_order_tab
  INSERT INTO rejected_orders VALUES (cust_tab(i), amount_tab(i));

  FORALL i IN VALUES OF big_order_tab
  INSERT INTO big_orders VALUES (cust_tab(i), amount_tab(i));

  COMMIT;
END;
/

-- Verify that the correct order details were stored
SELECT cust_name "Customer", amount "Valid order amount"
FROM valid_orders;

SELECT cust_name "Customer", amount "Big order amount"
FROM big_orders;

SELECT cust_name "Customer", amount "Rejected order amount"
FROM rejected_orders;
 
Exception Handling

Bulk Collection Exception Handling

CREATE TABLE tmp_target AS SELECT table_name, num_rows
FROM all_tables
WHERE 1=2;

ALTER TABLE tmp_target
ADD CONSTRAINT cc_num_rows
CHECK (num_rows > 0);


CREATE OR REPLACE PROCEDURE forall_errors IS

TYPE myarray IS TABLE OF tmp_target%ROWTYPE;
l_data myarray;

CURSOR c IS
SELECT table_name, num_rows
FROM all_tables;

errors PLS_INTEGER;

dml_errors EXCEPTION;
PRAGMA EXCEPTION_INIT(dml_errors, -24381);

BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO l_data LIMIT 100;

    -- SAVE EXCEPTIONS means don't stop if some DELETES fail
    FORALL i IN 1..l_data.COUNT SAVE EXCEPTIONS
    INSERT INTO tmp_target VALUES l_data(i);

    -- If any errors occurred during the FORALL SAVE EXCEPTIONS,
    -- a single exception is raised when the statement completes.
    EXIT WHEN c%NOTFOUND;
  END LOOP;
EXCEPTION
  WHEN dml_errors THEN
    errors := SQL%BULK_EXCEPTIONS.COUNT;
    dbms_output.put_line('Number of DELETE statements that
    failed: ' || errors);

    FOR i IN 1 .. errors
    LOOP
      dbms_output.put_line('Error #' || i || ' at '|| 'iteration
      #' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
      dbms_output.put_line('Error message is ' ||
      SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
    END LOOP;
  WHEN OTHERS THEN
    RAISE;
END forall_errors;
/

SQL> exec forall_errors;

SQL>  SELECT * FROM tmp_target;

Exception Handling Demo		 CREATE OR REPLACE PROCEDURE array_exceptions IS

-- cursor for processing load_errors
CURSOR le_cur IS
SELECT *
FROM load_errors
FOR UPDATE;

TYPE myarray IS TABLE OF test%ROWTYPE;
l_data myarray;

CURSOR c IS
SELECT sub_date, cust_account_id, carrier_id, ticket_id, upd_date
FROM stage
FOR UPDATE SKIP LOCKED;

 errors PLS_INTEGER;

 cai   test.cust_account_id%TYPE;
 cid   test.carrier_id%TYPE;
 ecode NUMBER;
 iud   stage.upd_date%TYPE;
 sd    test.sub_date%TYPE;
 tid   test.ticket_id%TYPE;
 upd   test.upd_date%TYPE;
BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO l_data LIMIT 50000;

    FORALL i IN 1..l_data.COUNT SAVE EXCEPTIONS
    INSERT INTO test VALUES l_data(i);

    EXIT WHEN c%NOTFOUND;
  END LOOP;
  COMMIT; -- Exits here when no exceptions are raised
EXCEPTION
  WHEN OTHERS THEN
    -- get the number of errors in the exception array
    errors := SQL%BULK_EXCEPTIONS.COUNT;

    -- insert all exceptions into the load_errors table
    FOR j IN 1 .. errors LOOP
      ecode := SQL%BULK_EXCEPTIONS(j).ERROR_CODE;
      sd := TRUNC(l_data(SQL%BULK_EXCEPTIONS(j).ERROR_INDEX).sub_date);
      cai := l_data(SQL%BULK_EXCEPTIONS(j).ERROR_INDEX).cust_account_id;
      cid := l_data(SQL%BULK_EXCEPTIONS(j).ERROR_INDEX).carrier_id;
      tid := l_data(SQL%BULK_EXCEPTIONS(j).ERROR_INDEX).ticket_id;

      INSERT INTO load_errors
      (error_code, sub_date, cust_account_id, carrier_id, ticket_id)
      VALUES
      (ecode, sd, cai, cid, tid);
    END LOOP;

    -- for each record in load_errors process those that can
    -- be handled and delete them after successful handling
    FOR le_rec IN le_cur LOOP
      IF le_rec.error_code = 1 THEN
        SELECT upd_date
        INTO iud
        FROM test
        WHERE cust_account_id = le_rec.cust_account_id
        AND carrier_id = le_rec.carrier_id
        AND ticket_id = le_rec.ticket_id;

        IF iud IS NULL THEN
          RAISE;
        ELSIF iud < le_rec.upd_date THEN
          UPDATE test
          SET upd_date = le_rec.upd_date
          WHERE sub_date = le_rec.sub_date
          AND cust_account_id = le_rec.cust_account_id
          AND carrier_id = le_rec.carrier_id
          AND ticket_id = le_rec.ticket_id;
        ELSE
          RAISE;
        END IF;
      END IF;
    END LOOP;
    COMMIT; -- Exits here when any existing found.
END array_exceptions;
/
 
Native Dynamic SQL

Dynamic SQL Inside a FORALL Statement		 CREATE TABLE tmp_target AS SELECT rownum ID, table_name, num_rows
FROM all_tables
WHERE rownum < 101;

DECLARE
 TYPE NumList IS TABLE OF NUMBER;
 rownos NumList;

 TYPE NameList IS TABLE OF VARCHAR2(30);
 tnames NameList;
BEGIN
  rownos := NumList(2,4,6,8,16);

  FORALL i IN 1..5
  EXECUTE IMMEDIATE 'UPDATE tmp_target SET id = id * 1.1
  WHERE id = :1
  RETURNING table_name INTO :2'
  USING rownos(i) RETURNING BULK COLLECT INTO tnames;

  FOR j IN 1..5
  LOOP
    dbms_output.put_line(tnames(j));
  END LOOP;
END;
/
 
Array Of Records Demo

You cannot bulk collect into an ARRAY OF RECORDS. You can into a RECORD OF ARRAYS..... 

This demo intentionally generates an error. Familiarize yourself with the error and message so you will recognize it		 CREATE OR REPLACE TYPE uw_sel_row AS OBJECT (
part_num NUMBER, part_name VARCHAR2(15));
/

CREATE OR REPLACE PROCEDURE wrong_way IS
 TYPE uw_sel_tab IS TABLE OF uw_sel_row;
 uw_selection uw_sel_tab;
BEGIN
  SELECT uw_sel_row(part_num, part_name)
  BULK COLLECT INTO uw_selection
  FROM parent;

  FOR i IN 1..uw_selection.count
  LOOP
    uw_selection(i).part_num := uw_selection(i).part_num * 10;
  END LOOP;

  FORALL i IN 1..uw_selection.COUNT
  INSERT INTO child
  VALUES
  (uw_selection(i).part_num, uw_selection(i).part_name);
  COMMIT;
END wrong_way;
/

sho err

drop type uw_sel_row;

CREATE OR REPLACE PROCEDURE right_way IS

 TYPE uw_sel_row IS TABLE OF parent%ROWTYPE;
  uw_selection uw_sel_row;
BEGIN
  SELECT part_num, part_name
  BULK COLLECT INTO uw_selection
  FROM parent;

  FOR i IN 1..uw_selection.count
  LOOP
    uw_selection(i).part_num := uw_selection(i).part_num * 10;
  END LOOP;

  FORALL i IN 1..uw_selection.COUNT
  INSERT INTO child VALUES uw_selection(i);
  COMMIT;
END right_way;
/
 
Bulk Collect Into DBMS_SQL Data Types

Bulk Collect with DBMS_SQL Data Types		 CREATE TABLE t AS
SELECT *
FROM all_objects
WHERE 1=0;

CREATE OR REPLACE PROCEDURE nrows_at_a_time(p_array_size PLS_INTEGER) IS
 l_owner          dbms_sql.VARCHAR2_table;
 l_object_name    dbms_sql.VARCHAR2_table;
 l_subobject_name dbms_sql.VARCHAR2_table;
 l_object_id      dbms_sql.NUMBER_table;
 l_data_object_id dbms_sql.NUMBER_table;
 l_object_type    dbms_sql.VARCHAR2_table;
 l_created        dbms_sql.DATE_table;
 l_last_ddl_time  dbms_sql.DATE_table;
 l_timestamp      dbms_sql.VARCHAR2_table;
 l_status         dbms_sql.VARCHAR2_table;
 l_temporary      dbms_sql.VARCHAR2_table;
 l_generated      dbms_sql.VARCHAR2_table;
 l_secondary      dbms_sql.VARCHAR2_table;

 CURSOR c IS
 SELECT *
 FROM all_objects
 WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'W';
BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO
    l_owner, l_object_name, l_subobject_name, l_object_id,
    l_data_object_id, l_object_type, l_created,
    l_last_ddl_time, l_timestamp, l_status, l_temporary,
    l_generated, l_secondary
    LIMIT p_array_size;

    FORALL i in 1 .. l_owner.COUNT
      INSERT INTO t
      (owner, object_name, subobject_name, object_id,
       data_object_id, object_type, created, last_ddl_time,
       timestamp, status, temporary, generated, secondary)
      VALUES
      (l_owner(i), l_object_name(i), l_subobject_name(i),
       l_object_id(i), l_data_object_id(i),
       l_object_type(i), l_created(i), l_last_ddl_time(i),
       l_timestamp(i), l_status(i), l_temporary(i),
       l_generated(i), l_secondary(i));
    EXIT WHEN c%NOTFOUND;
  END LOOP;
  COMMIT;
  CLOSE c;
END nrows_at_a_time;
/
 
 
分享到：
| jsTree
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    Oracle Bulk Binds技术分析.pdf

    BULK COLLECT INTO collection_name[, collection_name] ... ``` 输入绑定则使用`FORALL`关键字，它指示PL/SQL引擎在发送SQL语句给SQL引擎执行前先进行批量绑定。这样，对于一组操作，只需要一次上下文切换，提高...

    sal,ename BULK COLLECT INTO

    在提供的代码片段中，“sal,ename BULK COLLECT INTO”这一表述并未直接出现在代码中，但从整体上下文来看，这里主要涉及的是Oracle数据库中的PL/SQL语言，特别是`BULK COLLECT INTO`这一关键字及其应用场景。...

    Oracle查询优化改写技巧与案例

    `，应改写为`SELECT * BULK COLLECT INTO collection FROM table;`，然后在PL/SQL集合中处理。 五、使用绑定变量 1. 绑定变量可以减少解析次数，提高执行效率。 2. 避免在SQL语句中硬编码值，使用?占位符代替。 ...

    Oracle 遍历游标的四种方式汇总（for、fetch、while、BULK COLLECT）

    BULK COLLECT INTO COLLECTION_FIRST_INDEX FROM INDEX_A A ORDER BY A_ID; FORALL i IN 1..COLLECTION_FIRST_INDEX.COUNT DBMS_OUTPUT.PUT_LINE('{"ID":"' || COLLECTION_FIRST_INDEX(i).A_ID || '","名称":...

    Oracle9iPL/SQL编程的经验小结

    4. **数据集返回选择**：在PL/SQL中，选择使用集合（bulk collection）还是游标取决于具体需求。如果需要返回多行数据给另一个PL/SQL程序，集合通常是更优选择，因为它能利用批量操作提升性能。而当需要将数据返回到...

    PL/SQL编程经验小结开发者网络Oracle

    - **批量处理**：尽可能使用批量集合（bulk collection），它能大幅提升执行效率，特别是在Oracle 9i及以上版本。 - **显式游标优先**：显式游标比隐式游标通常更快，因此推荐使用显式游标进行复杂操作。 - **缓存小...

    JAVA 与 Sql学习笔记

    BULK COLLECT INTO collection_name FROM table_name; ``` 5. **LIMIT子句**： 使用LIMIT子句可以在BULK COLLECT中限制批量操作的大小，防止一次性处理过多数据导致资源消耗过大。 6. **RETURNING INTO子句**...

    PL SQL编程经验小结

    在Oracle 9i及以上版本，可以使用bulk collection（批量收集）与游标配合，以提高性能。 - 对于小表或静态数据，考虑在包级别缓存数据到集合，这样从PGA缓存而不是SGA读取数据，可以提升执行速度。 这些经验小结...

    PL_SQL总结-5.rar

    PL/SQL提供了一系列内置数据类型，如 NUMBER、VARCHAR2、DATE等，以及复合数据类型如记录（Record）和集合（Collection）。理解这些数据类型对于编写有效的PL/SQL代码至关重要。 3. **控制流语句**： 包括IF-THEN...

    ibatis 批量 增删改查

    3. 考虑数据库的特性，如MySQL的`ON DUPLICATE KEY UPDATE`或Oracle的`BULK COLLECT INTO`。 在Ibatis中，批量操作提供了更高效、更灵活的方式处理大量数据。理解并熟练应用这些技巧，能显著提升应用程序的性能和可...

    PL-SQL用户指南与参考（译）

    ##### 十四、利用BULKCOLLECTION子句为集合赋值 **1、从游标中批量取得数据的例子** 演示如何使用BULKCOLLECT子句从游标中一次性获取大量数据。 **2、使用LIMIT子句限制FETCH操作批量取得的数据个数** 说明如何...

    PL/SQL用户指南与参考

    了解并避免常见的集合异常，如`COLLECTION IS EMPTY`。 ##### 十二、使用集合批量绑定减少循环开销 使用集合批量绑定可以显著减少循环中的I/O开销。 ##### 十三、使用FORALL语句 `FORALL`语句用于批量执行DML语句...

    PLSQL用户指南与参考

    **十四、利用 BULK COLLECTION 子句为集合赋值** - **示例**：展示如何使用 BULK COLLECT 子句将数据加载到集合中。 以上是对 PL/SQL 用户指南与参考文档的详细解析，涵盖了从基础知识到高级功能的各个方面。通过对...

    PL_SQL用户指南与参考.doc

    除了在数据库中运行外，PL/SQL也可以在多种Oracle工具中使用，比如Oracle SQL Developer、Oracle GoldenGate等，这些工具提供了丰富的界面和功能支持PL/SQL开发。 ##### 三、PL/SQL的优势 **1、SQL的支持** PL/SQL...

    PLSQL用户指南与参考中文PDF版

    **十四、利用 BULK COLLECTION 子句为集合赋值**：可以使用 BULK COLLECT 子句将查询结果直接加载到集合中。 1. **从游标中批量收集数据**：通过使用 BULK COLLECT INTO 语句，可以从游标中一次收集多行数据到集合...

    EhcacheUserGuide

    #### 十四、垃圾回收调优（Tuning Garbage Collection） 由于Ehcache存储了大量的对象，因此垃圾回收的性能直接影响到Ehcache的整体表现。这部分内容提供了关于如何调优垃圾回收策略的具体指导，帮助开发者提高系统...

Magicbox
Global site tag (gtag.js) - Google Analytics