|
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;
/ |
