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;
/ |
相关推荐
kettle快速加载到oracle之oracle批量加载-Oracle Bulk Loader
bulk collect bulk collect bulk collect例子特殊应用
Oracle 中使用 fetch bulk collect into 批量效率的读取游标数据
Oracle Bulk Binds技术分析.pdf
Decrible the functions using in the Oracle, you could use them to program for PL/SQL
采用bulk collect可以将查询结果一次性地加载到collections中。而不是通过cursor一条一条地处理。可以在select into,fetch into,returning into语句使用bulk collect。注意在使用bulk collect时,所有的into变量都...
详细介绍了使用 BULK COLLECT 进行批量操作 提高sql的执行效率 使用MERGE INTO USING 一条sql搞定 新增和修改 使用connect by 进行递归树查询
oracle 11g 11.2版本c# oraclebulkcopy需要使用的动态链接库 Oracle.DataAccess.Client.dll.
在oracle使用中为了提高取数据效率,本文将详细介绍Oracle BULK COLLECT批量取数据,需要了解跟多的朋友可以参考下
oracle资料oracle资料oracle资料oracle资料oracle资料oracle资料oracle资料oracle资料oracle资料oracle资料oracle资料oracle资料
Oracle 9i Client (Oracle 9i 客户端) 简化版 (不安装Oracle客户端,也可以使用PLSQL Developer 不用安装Oracle客户端也可以使用PLSQL Developer 绿色! 安全! 轻便! 可靠! 1、本软件可作为简单的Oracle9i客户端...
Oracle Exception汇总(自定义Oracle异常) 使用方法举例: Exception When no_data_found then Dbms_output.put_line(‘no_data_found’); ACCESS_INTO_NULL 为对象赋值前必需初始化对象。对应ORA-06530错误。 CASE...
oracle_课件oracle_课件oracle_课件oracle_课件oracle_课件oracle_课件oracle_课件oracle_课件oracle_课件oracle_课件oracle_课件
oracle 数据库oracle 数据库oracle 数据库oracle 数据库oracle 数据库oracle 数据库oracle 数据库
在一般的情况下,使用批量fetch的几率并不是很多,但是Oracle提供了这个功能我们最好能熟悉一下,说不定什么时候会用上它。 代码如下:declare cursor c1 is select * from t_depart; v_depart t_depart%...
Oracle宝典Oracle宝典Oracle宝典Oracle宝典Oracle宝典Oracle宝典Oracle宝典Oracle宝典Oracle宝典Oracle宝典Oracle宝典Oracle宝典Oracle宝典Oracle宝典Oracle宝典Oracle宝典Oracle宝典Oracle宝典Oracle宝典Oracle宝典...
oracle 9i所有版本最新下载链接 直接迅雷下载 Oracle9i Database Release 2 Enterprise/Standard/Personal Edition for Windows NT/2000/XP ...
它包含 JDBC 驱动程序类,但不包含在 Oracle Object 和 Collection 类型中支持 NLS 的类。 ojdbc6.jar(1,977,267 字节)— 用于 JDK 1.6 的类。它包含 JDBC 驱动程序类,但不包含在 Oracle Object 和 Collection ...