Oracle PL/SQL开发基础(第二十弹:批量绑定和BULK COLLECT)

发布时间:2017-6-29 10:23:32编辑:www.fx114.net 分享查询网我要评论
本篇文章主要介绍了"Oracle PL/SQL开发基础(第二十弹:批量绑定和BULK COLLECT)",主要涉及到Oracle PL/SQL开发基础(第二十弹:批量绑定和BULK COLLECT)方面的内容,对于Oracle PL/SQL开发基础(第二十弹:批量绑定和BULK COLLECT)感兴趣的同学可以参考一下。

批量绑定

在编写PL/SQL代码时,PL/SQL引擎通常会与SQL引擎进行交互,比如将SQL语句发送到PL/SQL引擎,SQL引擎在执行了SQL语句后,会返回一些数据给PL/SQL引擎。

频繁的交互会大大降低效率,例如下面的示例将在一个循环中向SQL引擎发送多条DELETE指令,会导致效率非常低下。

DECLARE
   TYPE dept_type IS VARRAY (20) OF NUMBER;  --定义嵌套表变量  
   depts dept_type:=dept_type (10, 30, 70);  --实例化嵌套表,分配3个元素
BEGIN
   FOR i IN depts.FIRST..depts.LAST          --循环嵌套表元素 
   LOOP
      DELETE FROM emp
            WHERE deptno = depts (i);       --向SQL引擎发送SQL命令执行SQL操作
   END LOOP;
END;

可以看到要删除emp表中特定部门编号的记录,代码通过循环依次向SQL引擎发送SQL语句,这样的操作方式会降低执行的性能,特别是当元素个数比较多的时候。
如果使用PL/SQL的批量绑定,将一次性向SQL引擎发送所有的SQL语句,会显著的提高执行的性能。在示例中,一次一个depts集合元素的传递是造成性能降低的关键点,可以批量传递集合中的元素来执行,这个过程称为批量绑定。
要使用批量绑定,可以使用FORALL语句,该语句将输入的集合送到SQL引擎之前,通知PL/SQL引擎将集合中的所有元素进行批量绑定。

FORALL语句并不是一个FOR循环,它仅包含了一个重复的步骤,用来通知PL/SQL引擎在将SQL语句发送给SQL引擎之前,将集合中的所有元素批量绑定,以便一次性将多个绑定到SQL语句的变量一次性发送给SQL引擎。

FORALL使用如下:

FORALL index IN lower_bound..upper_bound
sql_statement;

语法的含义如下:
- index:只能在FORALL语句块内作为集合下标使用。
- SQL语句:必须是使用了集合元素的INSERT、UPDATE或DELETE语句。
- bound:有效范围是连续的索引号。在这个范围内,SQL引擎为每个索引号执行一次SQL语句。

将上面的示例通过FORALL改写:

DECLARE
   TYPE dept_type IS VARRAY (20) OF NUMBER;                  --定义嵌套表变量
   depts   dept_type := dept_type (10, 30, 70);   --实例化嵌套表,分配3个元素   
BEGIN
   FORALL i IN depts.FIRST .. depts.LAST                     --循环嵌套表元素
      DELETE FROM emp
            WHERE deptno = depts (i);       --向SQL引擎发送SQL命令执行SQL操作
   FOR i IN 1..depts.COUNT LOOP  
   DBMS_OUTPUT.put_line (   '部门编号'
                         || depts (i)
                         || '的删除操作受影响的行为:'
                         || SQL%BULK_ROWCOUNT (i)
                        );
   END LOOP;
END;

由于FORALL不是一个循环语句,因此不需要使用LOOP和END LOOP。在代码中,还使用了SQL游标变量来获取当前批量绑定操作中,特定的集合元素所影响的行。
由于批量绑定是一次性对多个SQL语句进行操作,因此要获取当前哪个SQL语句执行后受影响的行数信息,可以使用%BULK_ROWCOUNT,该变量接收一个集合元素的索引值,在示例中使用了循环语句依次获取受影响的行结果。

BULK COLLECT

FORALL关键字用来批量绑定多个集合的变量到SQL引擎,与之相反的是,BULK COLLECT关键字则可以批量地从SQL引擎中批量接收数据到一个集合,可以在SELECT-INTO,FETCH-INTO和RETURNING-INTO自居中使用BULK COLLECT。

SQL引擎能批量绑定出现在INTO列表后的所有集合,对应的字段可以保存为标量类型或复合类型的值,其中也包括对象类型。SQL引擎会初始化和扩展集合(但是,它不能把变长数组的长度扩大到超过变长数组的最大长度值),然后从索引1开始,连续地插入元素并覆盖先前已存在的元素。

如:

DECLARE
   TYPE numtab IS TABLE OF emp.empno%TYPE;     --员工编号嵌套表
   TYPE nametab IS TABLE OF emp.ename%TYPE;    --员工名称嵌套表
   nums    numtab;                             --定义嵌套表变量,不需要初始化        
   names   nametab;
BEGIN
   SELECT empno, ename
   BULK COLLECT INTO nums, names
     FROM emp;                                --从emp表中查出员工编号和名称,批量插入到集合
   FOR i IN 1 .. nums.COUNT                   --循环显示集合内容
   LOOP
      DBMS_OUTPUT.put ('num(' || i || ')=' || nums (i)||'   ');
      DBMS_OUTPUT.put_line ('names(' || i || ')=' || names (i));      
   END LOOP;
END;

使用BULK COLLECT有一些限制:
- 不能对使用字符串类型作为键的索引表使用BULK COLLECT子句。
- 只能在服务器端的程序中使用BULK COLLECT,如果在客户端使用,就会产生一个不支持这种特性的错误。
- BULK COLLECTINTO的目标对象必须是集合类型。



上一篇:请不要让环境限制你的成长——让你的努力落地有声
下一篇:图像旋转算法与实现

相关文章

相关评论

本站评论功能暂时取消,后续此功能例行通知。

一、不得利用本站危害国家安全、泄露国家秘密,不得侵犯国家社会集体的和公民的合法权益,不得利用本站制作、复制和传播不法有害信息!

二、互相尊重,对自己的言论和行为负责。

好贷网好贷款