SQL批处理语句
分类:多线程

我们知道PL/SQL程序中运行SQL语句是存在开销的,因为SQL语句是要提交给SQL引擎处理
这种在PL/SQL引擎和SQL引擎之间的控制转移叫做上下文却换,每次却换时,都有额外的开销

BULK COLLECT 子句会批量检索结果,即一次性将结果集绑定到一个集合变量中,并从SQL引擎发送到PL/SQL引擎。通常可以在SELECT INTO、
FETCH INTO以及RETURNING INTO子句中使用BULK COLLECT。本文将逐一描述BULK COLLECT在这几种情形下的用法。
    有关FORALL语句的用法请参考:批量SQL之 FORALL 语句

请看下图:

 

图片 1

一、BULK COLLECT批量绑定的示例

但是,FORALL和BULK COLLECT可以让PL/SQL引擎把多个上下文却换压缩成一个,这使得在PL/SQL中的要处理多行记录的SQL语句执行的花费时间骤降
请再看下图:

[sql] view plaincopyprint?

图片 2

  1. --下面的示例中使用了BULK COLLECT将得到的结果集绑定到记录变量中        
  2. DECLARE  
  3.    TYPE emp_rec_type IS RECORD          --声明记录类型  
  4.    (  
  5.       empno      emp.empno%TYPE  
  6.      ,ename      emp.ename%TYPE  
  7.      ,hiredate   emp.hiredate%TYPE  
  8.    );  
  9.   
  10.    TYPE nested_emp_type IS TABLE OF emp_rec_type;  --声明记录类型变量   
  11.   
  12.    emp_tab   nested_emp_type;  
  13. BEGIN  
  14.    SELECT empno, ename, hiredate  
  15.    BULK   COLLECT INTO emp_tab       --使用BULK COLLECT 将所得的结果集一次性绑定到记录变量emp_tab中  
  16.    FROM   emp;  
  17.   
  18.    FOR i IN emp_tab.FIRST .. emp_tab.LAST  
  19.    LOOP  
  20.       DBMS_OUTPUT.put_line('Current record is '||emp_tab(i).empno||chr(9)||emp_tab(i).ename||chr(9)||emp_tab(i).hiredate);  
  21.    END LOOP;  
  22. END;  
  23. --上面的例子可以通过FOR 循环和普通的SELECT INTO来实现,那两者之间的差异呢?  
  24. --差异是FOR循环的SELECT INTO逐行提取并绑定到记录变量,而BULK COLLECT则一次即可提取所有行并绑定到记录变量。即谓批量绑定。  

下面详解这爷俩

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

**㈠ 通过BULK COLLECT 加速查询

    用法:
        FETCH ... BULK COLLECT INTO ... [LIMIT rows]

⑴ BULK COLLECT 的用法**

[sql] view plaincopyprint?

采用BULK COLLECT可以将查询结果一次性地加载到collections中,而不是通过cursor一条一条地处理
可以在select into ,fetch into , returning into语句使用BULK COLLECT
注意在使用BULK COLLECT时,所有的INTO变量都必须是collections

  1. DECLARE  
  2.    CURSOR emp_cur IS  
  3.       SELECT empno, ename, hiredate FROM emp;  
  4.   
  5.    TYPE emp_rec_type IS RECORD  
  6.    (  
  7.       empno      emp.empno%TYPE  
  8.      ,ename      emp.ename%TYPE  
  9.      ,hiredate   emp.hiredate%TYPE  
  10.    );  
  11.   
  12.    TYPE nested_emp_type IS TABLE OF emp_rec_type;   -->定义了基于记录的嵌套表  
  13.   
  14.    emp_tab     nested_emp_type;           -->定义集合变量,此时未初始化  
  15.    v_limit     PLS_INTEGER := 5;          -->定义了一个变量来作为limit的值  
  16.    v_counter   PLS_INTEGER := 0;  
  17. BEGIN  
  18.    OPEN emp_cur;  
  19.   
  20.    LOOP  
  21.       FETCH emp_cur  
  22.       BULK   COLLECT INTO emp_tab         -->fetch时使用了BULK COLLECT子句  
  23.       LIMIT v_limit;                      -->使用limit子句限制提取数据量  
  24.   
  25.       EXIT WHEN emp_tab.COUNT = 0;        -->注意此时游标退出使用了emp_tab.COUNT,而不是emp_cur%notfound  
  26.       v_counter   := v_counter + 1;       -->记录使用LIMIT之后fetch的次数  
  27.   
  28.       FOR i IN emp_tab.FIRST .. emp_tab.LAST  
  29.       LOOP  
  30.          DBMS_OUTPUT.put_line( 'Current record is '||emp_tab(i).empno||CHR(9)||emp_tab(i).ename||CHR(9)||emp_tab(i).hiredate);  
  31.       END LOOP;  
  32.    END LOOP;  
  33.   
  34.    CLOSE emp_cur;  
  35.   
  36.    DBMS_OUTPUT.put_line( 'The v_counter is ' || v_counter );  
  37. END;  

举几个简单例子:

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

① 在select into语句中使用bulk collect

[sql] view plaincopyprint?

复制代码 代码如下:
DECLARE
TYPE sallist IS TABLE OF employees.salary%TYPE;
sals sallist;
BEGIN
SELECT salary BULK COLLECT INTO sals FROM employees where rownum<=50;
--接下来使用集合中的数据
END;
/

  1. --下面示例中从表emp中删除所有deptno=20的记录  
  2. DECLARE  
  3.    TYPE emp_rec_type IS RECORD  
  4.    (  
  5.       empno      emp.empno%TYPE  
  6.      ,ename      emp.ename%TYPE  
  7.      ,hiredate   emp.hiredate%TYPE  
  8.    );  
  9.   
  10.    TYPE nested_emp_type IS TABLE OF emp_rec_type;  
  11.   
  12.    emp_tab   nested_emp_type;  
  13. --   v_limit   PLS_INTEGER := 3;  
  14. --   v_counter   PLS_INTEGER := 0;  
  15. BEGIN  
  16.    DELETE FROM emp  
  17.    WHERE  deptno = 20  
  18.    RETURNING empno, ename, hiredate     -->使用returning 返回这几个列  
  19.    BULK   COLLECT INTO emp_tab;         -->将前面返回的列的数据批量插入到集合变量    
  20.   
  21.    DBMS_OUTPUT.put_line( 'Deleted ' || SQL%ROWCOUNT || ' rows.' );  
  22.    COMMIT;  
  23.      
  24.    IF emp_tab.COUNT > 0 THEN                 -->当集合变量不为空时,输出所有被删除的元素  
  25.       FOR i IN emp_tab.FIRST .. emp_tab.LAST       
  26.       LOOP  
  27.          DBMS_OUTPUT.  
  28.           put_line(  
  29.                        'Current record  '  
  30.                     || emp_tab( i ).empno  
  31.                     || CHR( 9 )  
  32.                     || emp_tab( i ).ename  
  33.                     || CHR( 9 )  
  34.                     || emp_tab( i ).hiredate  
  35.                     || ' has been deleted' );  
  36.       END LOOP;  
  37.    END IF;  
  38. END;  

② 在fetch into中使用bulk collect

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

复制代码 代码如下:
DECLARE
TYPE deptrectab IS TABLE OF departments%ROWTYPE;
dept_recs deptrectab;
CURSOR cur IS SELECT department_id,department_name FROM departments where department_id>10;
BEGIN
OPEN cur;
FETCH cur BULK COLLECT INTO dept_recs;
--接下来使用集合中的数据
END;
/

[sql] view plaincopyprint?

③ 在returning into中使用bulk collect

  1. DROP TABLE tb_emp;  
  2.   
  3. CREATE TABLE tb_emp AS                        -->创建表tb_emp  
  4.    SELECT empno, ename, hiredate  
  5.    FROM   emp  
  6.    WHERE  1 = 2;  
  7.   
  8. DECLARE                                 
  9.    CURSOR emp_cur IS                          -->声明游标   
  10.       SELECT empno, ename, hiredate FROM emp;  
  11.   
  12.    TYPE nested_emp_type IS TABLE OF emp_cur%ROWTYPE;  -->基于游标的嵌套表类型  
  13.   
  14.    emp_tab   nested_emp_type;                         -->声明嵌套变量  
  15. BEGIN  
  16.    SELECT empno, ename, hiredate  
  17.    BULK   COLLECT INTO emp_tab                        -->BULK  COLLECT批量提取数据  
  18.    FROM   emp  
  19.    WHERE  sal > 1000;  
  20.   
  21.    FORALL i IN 1 .. emp_tab.COUNT                     -->使用FORALL语句将变量中的数据插入到表tb_emp  
  22.       INSERT INTO (SELECT empno, ename, hiredate FROM tb_emp)  
  23.       VALUES emp_tab( i );  
  24.   
  25.    COMMIT;  
  26.    DBMS_OUTPUT.put_line( 'The total ' || emp_tab.COUNT || ' rows has been inserted to tb_emp' );  
  27. END;  

复制代码 代码如下:
CREATE TABLE emp AS SELECT * FROM employees;

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

DECLARE
TYPE numlist IS TABLE OF employees.employee_id%TYPE;
enums numlist;
TYPE namelist IS TABLE OF employees.last_name%TYPE;
names namelist;
BEGIN
DELETE emp WHERE department_id=30
RETURNING employee_id,last_name BULK COLLECT INTO enums,names;
DBMS_OUTPUT.PUT_LINE('deleted'||SQL%ROWCOUNT||'rows:');
FOR i IN enums.FIRST .. enums.LAST
LOOP
DBMS_OUTPUT.PUT_LINE('employee#'||enums(i)||':'||names(i));
END LOOP;
END;
/

deleted6rows:
employee#114:Raphaely
employee#115:Khoo
employee#116:Baida
employee#117:Tobias
employee#118:Himuro
employee#119:Colmenares

EATE TABLE emp AS SELECT * FROM employees;DECLARE TYPE numlist IS TABLE OF employees.employee_id%TYPE; enums numlist; TYPE namelist IS TABLE OF employees.last_name%TYPE; names namelist;BEGIN DELETE emp WHERE department_id=30 RETURNING employee_id,last_name BULK COLLECT INTO enums,names; DBMS_OUTPUT.PUT_LINE('deleted'||SQL%ROWCOUNT||'rows:'); FOR i IN enums.FIRST .. enums.LAST LOOP DBMS_OUTPUT.PUT_LINE('employee#'||enums(i)||':'||names(i)); END LOOP;END;/deleted6rows:employee#114:Raphaelyemployee#115:Khooemployee#116:Baidaemployee#117:Tobiasemployee#118:Himuroemployee#119:Colmenares

⑵ BULK COLLECT 对大数据DELETE UPDATE的优化

这里举DELETE就可以了,UPDATE同理

举个案例:
需要在一个1亿行的大表中,删除1千万行数据
需求是在对数据库其他应用影响最小的情况下,以最快的速度完成

如果业务无法停止的话,可以参考下列思路:
根据ROWID分片、再利用Rowid排序、批量处理、回表删除
在业务无法停止的时候,选择这种方式,的确是最好的
一般可以控制在每一万行以内提交一次,不会对回滚段造成太大压力
我在做大DML时,通常选择一两千行一提交
选择业务低峰时做,对应用也不至于有太大影响
代码如下:
复制代码 代码如下:
DECLARE
--按rowid排序的cursor
--删除条件是oo=xx,这个需根据实际情况来定
CURSOR mycursor IS SELECT rowid FROM t WHERE OO=XX ORDER BY rowid;
TYPE rowid_table_type IS TABLE OF rowid index by pls_integer;
v_rowid rowid_table_type;
BEGIN
OPEN mycursor;
LOOP
FETCH mycursor BULK COLLECT INTO v_rowid LIMIT 5000;--5000行提交一次
EXIT WHEN v_rowid.count=0;
FORALL i IN v_rowid.FIRST..v_rowid.LAST
DELETE t WHERE rowid=v_rowid(i);
COMMIT;
END LOOP;
CLOSE mycursor;
END;
/

⑶ 限制BULK COLLECT 提取的记录数

语法:
FETCH cursor BULK COLLECT INTO ...[LIMIT rows];
其中,rows可以是常量,变量或者求值的结果是整数的表达式

假设你需要查询并处理1W行数据,你可以用BULK COLLECT一次取出所有行,然后填充到一个非常大的集合中
可是,这种方法会消耗该会话的大量PGA,APP可能会因为PGA换页而导致性能下降

这时,LIMIT子句就非常有用,它可以帮助我们控制程序用多大内存来处理数据

例子:

复制代码 代码如下:
DECLARE
CURSOR allrows_cur IS SELECT * FROM employees;
TYPE employee_aat IS TABLE OF allrows_cur%ROWTYPE INDEX BY BINARY_INTEGER;
v_emp employee_aat;
BEGIN
OPEN allrows_cur;
LOOP
FETCH allrows_cur BULK FETCH INTO v_emp LIMIT 100;

/*通过扫描集合对数据进行处理*/
FOR i IN 1 .. v_emp.count
LOOP
upgrade_employee_status(v_emp(i).employee_id);
END LOOP;

EXIT WHEN allrows_cur%NOTFOUND;
END LOOP;

CLOSE allrows_cur;
END;
/

⑷ 批量提取多列

需求:
提取transportation表中的油耗小于 20公里/RMB的交通具体的全部信息
代码如下:

复制代码 代码如下:
DECLARE
--声明集合类型
TYPE vehtab IS TABLE OF transportation%ROWTYPE;
--初始化一个这个类型的集合
gas_quzzlers vehtab;
BEGIN
SELECT * BULK COLLECT INTO gas_quzzlers FROM transportation WHERE mileage < 20;
...

⑸ 对批量操作使用RETURNING子句

有了returning子句后,我们可以轻松地确定刚刚完成的DML操作的结果,无须再做额外的查询工作
例子请见BULK COLLECT 的用法的第三小点

㈡ 通过FORALL 加速DML

FORALL告诉PL/SQL引擎要先把一个或多个集合的所有成员都绑定到SQL语句中,然后再把语句发送给SQL引擎

⑴ 语法

未完待续。。。

本文由10bet手机官网发布于多线程,转载请注明出处:SQL批处理语句

上一篇:cache类代码_php技巧_脚本之家,的解决办法10bet备用网址官网: 下一篇:没有了
猜你喜欢
热门排行
精彩图文