Oracle 10g批量綁定forall bulk collect
上一篇 /
下一篇 2007-12-02 10:05:21
/ 個人分類：數據庫
批量綁定可以通過減少在PL/SQL和SQL引擎之間的上下文切換(context switches )以此提高性能。
批量綁定（Bulk binds）主要包括：
(1) Input collections, use the FORALL statement，用來改善DML（INSERT、UPDATE和DELETE) 操作的性能。
(2) Output collections, use BULK COLLECT clause，用來提高查詢（SELECT）的性能。
Oracle 10g開始forall語句可以使用三種方式：
◆ in low..up
◆ in indices of collection 取得集合元素下標的值。
◆ in values of collection 取得集合元素的值。
forall語句還可以使用部分集合元素。
sql%bulk_rowcount(i)表示forall語句第i元素所作用的行數。
--drop table blktest;
--CREATE TABLE blktest (num NUMBER(20), name varchar2(50));
--CREATE OR REPLACE PROCEDURE p_bulktest IS
DECLARE
TYPE type_num IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
TYPE type_name IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
tab_num type_num;
tab_name type_name;
t1 NUMBER;
t2 NUMBER;
t3 NUMBER;
BEGIN
FOR i IN 1 .. 500000 LOOP
tab_num(i) := i;
tab_name(i) := 'name: ' || to_char(i);
END LOOP;
SELECT dbms_utility.get_time
INTO t1
FROM dual;
FOR i IN 1 .. 500000 LOOP
INSERT INTO blktest
VALUES
(tab_num(i), tab_name(i));
END LOOP;
SELECT dbms_utility.get_time
INTO t2
FROM dual;
FORALL i IN 1 .. 500000
INSERT INTO blktest
VALUES
(tab_num(i), tab_name(i));
SELECT dbms_utility.get_time
INTO t3
FROM dual;
dbms_output.put_line('Execution Time(S)');
dbms_output.put_line('-------------------');
dbms_output.put_line('FOR loop: '
|| to_char((t2 - t1) / 100));
dbms_output.put_line('FORALL: '
|| to_char((t3 - t2) / 100));
END;
/*
Execution Time(S)
-------------------
FOR loop: 32.78
FORALL: 2.64
*/
/*
bulk collect 語句：
用于取得批量數據，只適用于select into ,fetch into 及DML語句的返回子句
DECLARE
TYPE type_emp IS TABLE OF
scott.emp%ROWTYPE INDEX BY BINARY_INTEGER;
tab_emp type_emp;
TYPE type_ename IS TABLE OF
scott.emp.ename%TYPE INDEX BY BINARY_INTEGER;
tab_ename type_ename;
CURSOR c IS
SELECT *
FROM scott.emp;
BEGIN
SELECT * BULK COLLECT
INTO tab_emp
FROM scott.emp;
FOR i IN 1 .. tab_emp.COUNT LOOP
dbms_output.put_line(tab_emp(i).ename);
END LOOP;
dbms_output.new_line;
DELETE scott.emp RETURNING
ename BULK COLLECT INTO tab_ename;
FOR i IN 1 .. tab_emp.COUNT LOOP
dbms_output.put_line(tab_emp(i).ename);
END LOOP;
ROLLBACK;
OPEN c;
FETCH c BULK COLLECT
INTO tab_emp;
dbms_output.new_line;
FOR i IN 1 .. tab_emp.COUNT LOOP
dbms_output.put_line(tab_emp(i).sal);
END LOOP;
END;
*/
==============================
批量輸入FORALL+批量輸出BULK
DECLARE
--批量輸入FORALL+批量輸出BULK
TYPE type_num IS TABLE OF NUMBER;
tab_1 type_num;
tab_2 type_num;
BEGIN
tab_1 := type_num(1, 2, 3);
FORALL i IN 1 .. tab_1.COUNT
--EXECUTE IMMEDIATE 'update t2 set id2=id*2
where id=:1 returning id2 into :2'
--USING tab_1(i) RETURNING BULK COLLECT INTO tab_2;
update t2 set id2=id*2 where id=tab_1(i)
returning id2 bulk collect into tab_2;
FOR i IN 1 .. tab_2.COUNT LOOP
dbms_output.put_line(tab_2(i));
END LOOP;
END;
============================== |
導入論壇
引用鏈接
收藏
分享給好友
推薦到圈子
管理
舉報
TAG:
