批量SQL之 BULK COLLECT 子句_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方-国内领先的云技术服务提供商
»  游客             当前位置:  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 1961 | 回复: 0   主题: 批量SQL之 BULK COLLECT 子句        下一篇 
zlasdf
注册用户
等级:新兵
经验:72
发帖:70
精华:0
注册:2011-10-17
状态:离线
发送短消息息给zlasdf 加好友    发送短消息息给zlasdf 发消息
发表于: IP:您无权察看 2015-7-21 15:34:44 | [全部帖] [楼主帖] 楼主

BULK COLLECT 子句会批量检索结果,即一次性将结果集绑定到一个集合变量中,并从SQL引擎发送到PL/SQL引擎。通常可以在SELECT INTO、FETCH INTO以及RETURNING INTO子句中使用BULK COLLECT。

一、BULK COLLECT批量绑定的示例

--下面的示例中使用了BULK COLLECT将得到的结果集绑定到记录变量中      


DECLARE
TYPE emp_rec_type IS RECORD          --声明记录类型
(
empno      emp.empno%TYPE
,ename      emp.ename%TYPE
,hiredate   emp.hiredate%TYPE
);
TYPE nested_emp_type ISTABLEOF emp_rec_type;  --声明记录类型变量 
emp_tab   nested_emp_type;
BEGIN
SELECT empno, ename, hiredate
BULK   COLLECT INTO emp_tab       --使用BULK COLLECT 将所得的结果集一次性绑定到记录变量emp_tab中
FROM   emp;
FOR i IN emp_tab.FIRST .. emp_tab.LAST
LOOP
DBMS_OUTPUT.put_line('Current record is ' emp_tab(i).empno chr(9) emp_tab(i).ename chr(9) emp_tab(i).hiredate);
END LOOP;
END;
--上面的例子可以通过FOR 循环和普通的SELECT INTO来实现,那两者之间的差异呢?


--差异是FOR循环的SELECT INTO逐行提取并绑定到记录变量,而BULK COLLECT则一次即可提取所有行并绑定到记录变量。即谓批量绑定。


二、使用LIMIT限制FETCH数据量

在使用BULK COLLECT 子句时,对于集合类型,如嵌套表,联合数组等会自动对其进行初始化以及扩展(如下示例)。因此如果使用BULK COLLECT子句操作集合,则无需对集合进行初始化以及扩展。由于BULK COLLECT的批量特性,如果数据量较大,而集合在此时又自动扩展,为避免过大的数据集造成性能下降,因此使用limit子句来限制一次提取的数据量。limit子句只允许出现在fetch操作语句的批量中。

用法:

 FETCH ... BULK COLLECT INTO ... [LIMIT rows]
DECLARE
CURSOR emp_cur IS
SELECT empno, ename, hiredate FROM emp;
TYPE emp_rec_type IS RECORD
(
empno      emp.empno%TYPE
,ename      emp.ename%TYPE
,hiredate   emp.hiredate%TYPE
);
TYPE nested_emp_type ISTABLEOF emp_rec_type;   -->定义了基于记录的嵌套表
emp_tab     nested_emp_type;           -->定义集合变量,此时未初始化


v_limit     PLS_INTEGER := 5;          -->定义了一个变量来作为limit的值


v_counter   PLS_INTEGER := 0;
BEGIN
OPEN emp_cur;
LOOP
FETCH emp_cur
BULK   COLLECT INTO emp_tab         -->fetch时使用了BULK COLLECT子句
LIMIT v_limit;                      -->使用limit子句限制提取数据量


EXIT WHEN emp_tab.COUNT = 0;        -->注意此时游标退出使用了emp_tab.COUNT,而不是emp_cur%notfound
v_counter   := v_counter + 1;       -->记录使用LIMIT之后fetch的次数


FOR i IN emp_tab.FIRST .. emp_tab.LAST
LOOP
DBMS_OUTPUT.put_line( 'Current record is ' emp_tab(i).empno CHR(9) emp_tab(i).ename CHR(9) emp_tab(i).hiredate);
END LOOP;
END LOOP;
CLOSE emp_cur;
DBMS_OUTPUT.put_line( 'The v_counter is '   v_counter );
END;


三、RETURNING 子句的批量绑定
BULK COLLECT除了与SELECT,FETCH进行批量绑定之外,还可以与INSERT,DELETE,UPDATE语句结合使用。当与这几个DML语句结合时,我们需要使用RETURNING子句来实现批量绑定。

--下面示例中从表emp中删除所有deptno=20的记录


DECLARE
TYPE emp_rec_type IS RECORD
(
empno      emp.empno%TYPE
,ename      emp.ename%TYPE
,hiredate   emp.hiredate%TYPE
);
TYPE nested_emp_type ISTABLEOF emp_rec_type;
emp_tab   nested_emp_type;
--   v_limit   PLS_INTEGER := 3;
--   v_counter   PLS_INTEGER := 0;
BEGIN
DELETEFROM emp
WHERE  deptno = 20
RETURNING empno, ename, hiredate     -->使用returning 返回这几个列


BULK   COLLECT INTO emp_tab;         -->将前面返回的列的数据批量插入到集合变量  
DBMS_OUTPUT.put_line( 'Deleted '   SQL%ROWCOUNT   ' rows.' );
COMMIT;
IF emp_tab.COUNT > 0 THEN-->当集合变量不为空时,输出所有被删除的元素
FOR i IN emp_tab.FIRST .. emp_tab.LAST
LOOP
DBMS_OUTPUT.
put_line(
'Current record  '
emp_tab( i ).empno
CHR( 9 )
emp_tab( i ).ename
CHR( 9 )
emp_tab( i ).hiredate
' has been deleted' );
END LOOP;
END IF;
END;


四、FORALL与BULK COLLECT 综合运用
FORALL与BULK COLLECT是实现批量SQL的两个重要方式,我们可以将其结合使用以提高性能。下面的示例即是两者的总和运用。

DROPTABLE tb_emp;
CREATETABLE tb_emp AS-->创建表tb_emp
SELECT empno, ename, hiredate
FROM   emp
WHERE  1 = 2;
DECLARE
CURSOR emp_cur IS-->声明游标 
SELECT empno, ename, hiredate FROM emp;
TYPE nested_emp_type ISTABLEOF emp_cur%ROWTYPE;  -->基于游标的嵌套表类型
emp_tab   nested_emp_type;                         -->声明嵌套变量


BEGIN
SELECT empno, ename, hiredate
BULK   COLLECT INTO emp_tab                        -->BULK  COLLECT批量提取数据
FROM   emp
WHERE  sal > 1000;
FORALL i IN 1 .. emp_tab.COUNT-->使用FORALL语句将变量中的数据插入到表tb_emp
INSERTINTO (SELECT empno, ename, hiredate FROM tb_emp)
VALUES emp_tab( i );
COMMIT;
DBMS_OUTPUT.put_line( 'The total '   emp_tab.COUNT   ' rows has been inserted to tb_emp' );
END;


五、BULK COLLECT的限制
1、不能对使用字符串类型作键的关联数组使用BULK COLLECT 子句。
2、只能在服务器端的程序中使用BULK COLLECT,如果在客户端使用,就会产生一个不支持这个特性的错误。
3、BULK COLLECT INTO 的目标对象必须是集合类型。
4、复合目标(如对象类型)不能在RETURNING INTO 子句中使用。
5、如果有多个隐式的数据类型转换的情况存在,多重复合目标就不能在BULK COLLECT INTO 子句中使用。
6、如果有一个隐式的数据类型转换,复合目标的集合(如对象类型集合)就不能用于BULK COLLECTINTO 子句中。

--转自 北京联动北方科技有限公司




赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码: 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论