登录  
 加关注
查看详情
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

li.angshan 的博客

关注数据计算领域

 
 
 

日志

 
 
 
 

plsql bulk collect and forall  

2009-10-29 11:38:21|  分类: oracle PL/SQL |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
批量绑定(Bulk binds)包括:
(i) Input collections, use the FORALL statement,一般用来改善DML(INSERT、UPDATE和DELETE) 操作的性能
(ii) Output collections, use BULK COLLECT clause,一般用来提高查询(SELECT)的性能
10g开始forall语句可以使用三种方式:
i in low..up
i in indices of collection 取得集合元素下标的值
i 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;

  评论这张
 
阅读(172)| 评论(0)

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018