批量SQL(原创)
批量sql概述
对 PL/SQL而言,任何的PL/SQL块或者子程序都是PL/SQL引擎来处理,而其中包含的SQL语句则由PL/SQL引擎发送SQL语句转交到SQL 引擎来处理,SQL引擎处理完毕后向PL/SQL引擎返回数据。Pl/SQL与SQL引擎之间的通信则称之为上下文切换。过多的上下文切换将带来过量的性 能负载。因此为减少性能的FORALL与BULK COLLECT的子句应运而生,即仅仅使用一次切换多次执行来降低上下文切换次数。
注意:并不是所有的SQL,FORALL语句都会一次性将SQL转交到SQL引擎来处理。具体可参见下面的例子2,例子3
FORALL
FORALL语法描述
FORALL loop_counter IN bounds_clause -- 注意FORALL块内不需要使用loop, end loop
SQL_STATEMENT [SAVE EXCEPTIONS];
bounds_clause的形式
lower_limit .. upper_limit -- 指明循环计数器的上限和下限,与for循环类似
INDICES OF collection_name BETWEEN lower_limit .. upper_limit -- 引用特定集合元素的下标(该集合可能为稀疏,即当集合(嵌套表或联合数组)中的元素被删除之后,对稀疏集合实现迭代。)
VALUES OF colletion_name -- VALUES OF选项可以指定FORALL语句中循环计数器的值来自于指定集合中元素的值。VALUES OF选项使用时有一些限制
如果VALUES OF子句中所使用的集合是联合数组,则必须使用PLS_INTEGER和BINARY_INTEGER进行索引;
VALUES OF 子句中所使用的元素必须是PLS_INTEGER或BINARY_INTEGER;
当VALUES OF 子句所引用的集合为空,则FORALL语句会导致异常;
SQL_STATEMENT部分:SQL_STATEMENT部分必须是一个或者多个集合的静态或者动态的DML(insert,update,delete)语句。
SAVE EXCEPTIONS部分:对 于任意的SQL语句执行失败,将导致整个语句或整个事务会滚。而使用SAVE EXCEPTIONS可以使得在对应的SQL语句异常的情形下,FORALL仍然可以继续执行。如果没有SAVE EXCEPTIONS时,当异常发生,FORALL语句就会停止执行。SAVE EXCEPTIONS使得FORALL子句中的DML下产生的所有异常都记录在SQL%BULK_EXCEPTIONS的游标属性中。 SQL%BULK_EXCEPTIONS属性是个记录集合,其中的每条记录由两个字段组成,ERROR_INDEX和ERROR_CODE。 ERROR_INDEX字段会存储发生异常的FORALL语句的迭代编号,而ERROR_CODE则存储对应异常的ORACLE错误代码。类似于这样: (2,01400),(6,1476)和(10,12899)。存放在%BULK_EXCEPTIONS中的值总是与最近一次FORALL语句执行的结果 相关,异常的个数存放在%BULK_EXCEPTIONS.COUNT属性中,%BULK_EXCEPTIONS有效的下标索引范围在1 到%BULK_EXCEPTIONS.COUNT之间。
%BULK_ROWCOUNT 也是专门为FORALL设计的,用于保存第i个元素第i次insert或update或delete所影响到的行数。如果第i次操作没有行被影响, 则%BULK_ROWCOUNT返回为零值。FORALL语句和%BULK_ROWCOUNT属性使用同样的下标索引。如果FORALL使用下标索引的范 围在5到8的话,那么
%BULK_ROWCOUNT的也是5到8。需要注意的是一般情况下,对于insert .. values而言,所影响的行数为1,即%BULK_ROWCOUNT的值为1。而对于insert .. select方式而言,%BULK_ROWCOUNT的值就有可能大于1。update与delete语句存在0,1,以及大于1的情形。
FORALL综合示例
示例1,循环效率和indices of
SQL CREATE TABLE t (
col_num NUMBER
,col_var VARCHAR2( 10 ) );
SQL declare
2 type col_num_type is table of number index by pls_integer; -- 声明了两个联合数组
3 type col_var_type is table of varchar2(10) index by pls_integer;
4 col_num_tab col_num_type;
5 col_var_tab col_var_type;
6 v_start_time integer;
7 v_end_time integer;
8 v_total integer;
9 begin
10 for i in 1..5000 loop -- 使用FOR循环向数组填充元素
11 col_num_tab(i) := i;
12 col_var_tab(i) := var_ || i ;
13 end loop;
14 v_start_time := dbms_utility.get_time; -- 获得FOR循环向表t插入数据前的初始时间
15 for i in 1..5000 loop -- 使用FOR循环向表t插入数据
16 insert into t
17 values(col_num_tab(i),col_var_tab(i));
18 end loop;
19 v_end_time := dbms_utility.get_time; rollback; -- 获得FOR循环向表t插入数据前的结束时间
20 dbms_output.put_line(Duration of the for loop: || (v_end_time - v_start_time));
21 v_start_time := dbms_utility.get_time; -- 获得FOR循环向表t插入数据前的初始时间
22 forall i in 1..5000 -- 使用FORALL循环向表t插入数据
23 insert into t
24 values(col_num_tab(i),col_var_tab(i));
25 v_end_time :=dbms_utility.get_time; rollback; -- 获得FORALL循环向表t插入数据前的结束时间
26 dbms_output.put_line(Duration of the forall statement: || (v_end_time - v_start_time));
27 col_num_tab.delete(2);col_num_tab.delete(4);col_num_tab.delete(6); -- 此处删除了数组中的第二个元素,导致数组变为稀疏型
28 col_var_tab.delete(2);col_var_tab.delete(4);col_var_tab.delete(6);
29 forall i in indices of col_num_tab -- 此处使用了indices OF empno_tab,则所有未被delete的元素都将进入循环
30 insert into t2
31 values(col_num_tab(i),col_var_tab(i));
32 select count(*) into v_total from t2;
33 dbms_output.put_line(The amount rows of t2: || v_total);
34* end;
35 /
Duration of the for loop: 105 -- 此处的计时单位为百分之一秒,即1.05s,下同
Duration of the forall statement: 2
The amount rows of t2: 4997 -- 此处少了3条记录。
PL/SQL procedure successfully completed.
示例2,save exception和values of
SQL create table tb_emp_ins_log(empno number,ename varchar2(50),hiredate date);
-- 创建表tb_emp_ins_log用于记录错误记录
Table created.
SQL CREATE TABLE tb_emp AS -- 创建表tb_emp
SELECT empno, ename, hiredate
FROM emp
WHERE 1 = 2;
SQL ALTER TABLE tb_emp MODIFY(empno NOT NULL); -- 为表添加约束
SQL declare
2 type col_num_type is table of number index by pls_integer; -- 一共定义了4个联合数组类型
3 type col_var_type is table of varchar2(100) index by pls_integer;
4 type col_date_type is table of date index by pls_integer;
5 type ins_log_type is table of pls_integer index by pls_integer; -- 用于填充异常记录的元素值
6 empno_tab col_num_type;
7 ename_tab col_var_type;
8 hiredate_tab col_date_type;
9 ins_log ins_log_type;
10 v_counter pls_integer := 0;
11 v_total integer := 0;
12 errors exception; -- 异常声明
13 pragma exception_init( errors, -24381);
14 begin
15 for rec in (select empno,ename,hiredate from emp) loop -- 使用for循环将数据填充到联合数组
16 v_counter := v_counter + 1 ;
17 empno_tab(v_counter) := rec.empno;
18 ename_tab(v_counter) := rec.ename;
19 hiredate_tab(v_counter) := rec.hiredate;
20 end loop;
21
22 empno_tab(2) := null; -- 对部分数据进行处理以产生异常
23 ename_tab(5) := rpad(ename_tab(5),15,*);
24 empno_tab(10) := null;
25
26 forall i in 1..empno_tab.count -- 使用forall将联合数组中的数据插入到表tb_emp
27 save exceptions
28 insert into tb_emp
29 values(empno_tab(i),ename_tab(i),hiredate_tab(i));
30 commit;
32 exception
33 when errors then
34 dbms_output.put_line(There are || SQL%bulk_exceptions.count || exceptions);
35 for i in 1..SQL%bulk_exceptions.count loop -- SQL%bulk_exceptions.COUNT记录异常个数来控制迭代
36 ins_log(i) := SQL%bulk_exceptions(i).error_index; -- 异常记录的索引值将填充ins_log_type联合数组,此处的结果是ins_log(1)=2, ins_log(2)=5, ins_log(3)=10
38 dbms_output.put_line(
39 Record
40 || SQL%bulk_exceptions(i).error_index
41 || caused error
42 || i
43 || ;
44 || SQL%bulk_exceptions(i).error_code
45 ||
46 || SQLERRM( -SQL%bulk_exceptions(i).error_code)); -- 使用SQLERRM根据错误号抛出具体的错误信息
47 end loop;
49 for i in empno_tab.first .. empno_tab.last loop
50 dbms_output.put_line(SQL%bulk_rowcount(i) || rows are inserted into tb_emp!);
51 end loop;
52 select count(*) into v_total from tb_emp;
53 dbms_output.put_line(v_total || rows are inserted into tb_emp!);
55 forall i in values of ins_log -- 使用VALUES OF子句为ins_log_type联合数组中的元素值
56 insert into tb_emp_ins_log
57 values(empno_tab(i),ename_tab(i),hiredate_tab(i)); -- 异常记录的索引值将填充ins_log_type联合数组
58 commit;
60 end;
61 /
There are 3 exceptions
Record 2 caused error 1; 1400 ORA-01400: cannot insert NULL into ()
Record 5 caused error 2; 12899 ORA-12899: value too large for column (actual: , maximum: )
Record 10 caused error 3; 1400 ORA-01400: cannot insert NULL into ()
1 rows are inserted into tb_emp!
0 rows are inserted into tb_emp!
1 rows are inserted into tb_emp!
1 rows are inserted into tb_emp!
0 rows are inserted into tb_emp!
1 rows are inserted into tb_emp!
1 rows are inserted into tb_emp!
1 rows are inserted into tb_emp!
1 rows are inserted into tb_emp!
0 rows are inserted into tb_emp!
1 rows are inserted into tb_emp!
1 rows are inserted into tb_emp!
1 rows are inserted into tb_emp!
1 rows are inserted into tb_emp!
11 rows are inserted into tb_emp!
PL/SQL procedure successfully completed.
SQL select * from tb_emp_ins_log; -- 异常的记录被插入到表tb_emp_ins_log
EMPNO ENAME HIREDATE
---------- --------------- ------------------
ALLEN 20-FEB-81
7654 MARTIN********* 28-SEP-81
TURNER 08-SEP-81
FROALL语句SQL%BULK_COUNT的使用可以参见下面例子:
例子3
create table emp_temp as select * from emp;
declare
TYPE numlist is table of NUMBER;
depts numlist := numlist(10,20,30);
begin
forall j in depts.first .. depts.last
delete from emp_temp where deptno = depts(j);
for i in depts.first .. depts.last loop
dbms_output.put_line(Statement # || i || deleted ||
SQL%BULK_ROWCOUNT(i) || rows.);
end loop;
dbms_output.put_line(Total rows deleted: || SQL%ROWCOUNT);
END;
/
Statement #1 deleted 3 rows.
Statement #2 deleted 5 rows.
Statement #3 deleted 6 rows.
Total rows deleted: 14
PL/SQL procedure successfully completed.
BULK COLLECT
BULK COLLECT 子句会批量检索结果,即一次性将结果集绑定到一个集合变量中,并从SQL引擎发送到PL/SQL引擎。通常可以在SELECT INTO、FETCH INTO以及RETURNING INTO子句中使用BULK COLLECT。
使用LIMIT限制FETCH数据量
在 使用BULK COLLECT 子句时,对于集合类型,如嵌套表,联合数组等会自动对其进行初始化以及扩展(如下示例)。因此如果使用BULKCOLLECT子句操作集合,则无需对集合 进行初始化以及扩展。由于BULK COLLECT的批量特性,如果数据量较大,而集合在此时又自动扩展,为避免过大的数据集造成性能下降,因此使用limit子句来限制一次提取的数据量。 limit子句只允许出现在fetch操作语句的批量中。
用法:
FETCH ... BULK COLLECT INTO ... [LIMIT rows]
RETURNING子句的批量绑定
BULK COLLECT除了与SELECT,FETCH进行批量绑定之外,还可以与INSERT,DELETE,UPDATE语句结合使用。当与这几个DML语句结合时,我们需要使用RETURNING子句来实现批量绑定。
BULK COLLECT综合示例
SQL declare
2 cursor emp_cur is
3 select empno,ename,hiredate from emp;
4 type emp_rec_type is record(
5 empno emp.empno%type,
6 ename emp.ename%type,
7 hiredate emp.hiredate%type);
type nested_emp_type is table of emp_rec_type;
emp_tab1 nested_emp_type;
emp_tab1 nested_emp_type;
emp_tab2 nested_emp_type;
v_limit integer := 5;
v_counter integer := 0;
begin
open emp_cur;
loop
fetch emp_cur -- fetch时使用了BULK COLLECT子句,并使用limit子句限制提取数据量
bulk collect into emp_tab1
limit v_limit;
19 dbms_output.put_line(The recycle time : || v_counter);
exit when emp_tab1.count = 0; -- 注意此时游标退出使用了emp_tab.COUNT,而不是emp_cur%notfound
delete from emp where deptno = 20
v_counter := v_counter + 1; -- 记录使用LIMIT之后fetch的次数
end loop;
dbms_output.put_line(The recycle time : || v_counter);
delete from emp where deptno = 20
returning empno,ename,hiredate
bulk collect into emp_tab2;tab2; -- 使用returning将前面返回的列的数据批量插入到集合变量
27
end loop;i in emp_tab2.first..emp_tab2.last loop
for i in emp_tab2.first..emp_tab2.last loop
dbms_output.put_line(Current record : || emp_tab2(i).empno
||
|| emp_tab2(i).ename
||
|| emp_tab2(i).hiredate
|| hase been deleted);
end loop;
36 end;
37 /
The recycle time : 3
Current record : 7369 SMITH 17-DEC-80 hase been deleted
Current record : 7566 JONES 02-APR-81 hase been deleted
Current record : 7788 SCOTT 19-APR-87 hase been deleted
Current record : 7876 ADAMS 23-MAY-87 hase been deleted
Current record : 7902 FORD 03-DEC-81 hase been deleted
PL/SQL procedure successfully completed.
BULK COLLECT的限制
1、不能对使用字符串类型作键的关联数组使用BULK COLLECT 子句。
2、只能在服务器端的程序中使用BULK COLLECT,如果在客户端使用,就会产生一个不支持这个特性的错误。
3、BULK COLLECT INTO 的目标对象必须是集合类型。
4、复合目标(如对象类型)不能在RETURNING INTO 子句中使用。
5、如果有多个隐式的数据类型转换的情况存在,多重复合目标就不能在BULK COLLECT INTO 子句中使用。
6、如果有一个隐式的数据类型转换,复合目标的集合(如对象类型集合)就不能用于BULK COLLECTINTO 子句中。
参考至:《Oracle PL/SQL实例精解》Benjamin著
http://blog.csdn.net/robinson_0612/article/details/7536926
http://blog.csdn.net/robinson_0612/article/details/7545597
本文原创,转载请注明出处、作者
如有错误,欢迎指正
邮箱:cmzcj@163.com
MS SQL批量生成作业脚本方法介绍总结 原文:MS SQL批量生成作业脚本方法介绍总结 在迁移或升级SQL Server数据库服务器时,很多场景下我们不能还原msdb,所以我们必须手工迁移SQL Server相关作业。如果手工生成每一个作业的脚本话,费时又费力,其实SQL Server中有好几种方法可以批量生成作业脚本的方法, 下面介绍一下。
MS SQL数据批量备份还原(适用于MS SQL 2005+) 原文:MS SQL数据批量备份还原(适用于MS SQL 2005+) 我们知道通过Sql代理,可以实现数据库的定时备份功能;当数据库里的数据库很多时,备份一个数据库需要建立对应的定时作业,相对来说比较麻烦; 还好,微软自带的osql工具,比较实用,通过在命令行里里输入命令,也能实现数据库备份的功能;如果能通过sql语句来生成一个批处理文件,那就方便了; 下面即是生成批处理文件的sql脚本,通过它可以实现:运行一个批处理文件,备份数据库服务器上的所有数据库到指定目录。
利用osql/ocmd批处理批量执行sql文件 原文:利用osql/ocmd批处理批量执行sql文件 上周在测试环境建了几十张表,保存了.sql文件,准备在正式环境重建的时候懒得一个个打开建了,做一在网上搜寻了一下,果然有简单点的方法。 利用osql/ocmd批处理批量执行sql文件 注意:在上图中我们可以看到osql 并不支持 SQL Server 2008的所有功能,如果需要使用SQL Server 2008的所有功能可以使用ocmd命令。
金蝶KIS&K3助记码SQL数据库批量刷新 金蝶KIS K3助记码SQL数据库批量刷新 用的次数不多,就没有写入存储过程或者触发里面了,可以自行实现。 第一步选择对应账套的数据库,执行下面的命令,这个是一个函数。 if exists (select * from sysobjects where name= fun_get...
SQL Server 批量主分区备份(One Job) 原文:SQL Server 批量主分区备份(One Job) 一.本文所涉及的内容(Contents) 本文所涉及的内容(Contents) 背景(Contexts) 案例分析(Case) 实现代码(SQL Codes) 主分区完整、差异还原(Primary Backup And Restore) 参考文献(References)
SQL Server 批量主分区备份(Multiple Jobs) 原文:SQL Server 批量主分区备份(Multiple Jobs) 一.本文所涉及的内容(Contents) 本文所涉及的内容(Contents) 背景(Contexts) 案例分析(Case) 方案一(Solution One) 方案二(Solution Two) 方案三(So...
相关文章
- EasyCVR如何使用SQL语句批量开启通道音频?
- sql服务器系统时间格式,SQL Server 日期格式和日期操做
- SQL开发知识:Sql注入原理简介
- oracle+mybatis 使用动态Sql当插入字段不确定的情况下实现批量insert
- 约束Oracle中实现SQL约束的技巧(oracle添加sql)
- 文件MySQL如何批量导入SQL文件(mysql导入sql)
- Oracle数据库SQL语句的导出与备份(oracle导出sql)
- Linux下快速批量重命名文件夹的方法(批量重命名linux)
- Linux 批量重命名:简易操作指南(批量重命名linux)
- Oracle多表更新:高效实现批量数据变更(oracle多表更新)
- MySQL如何有效防止SQL注入攻击?(mysql防sql注入)
- 轻松实现MySQL SQL批量执行方法,快速操作数据库(mysql批量执行sql)
- 快如闪电!学习MySQL批量插入技巧,让你高效处理大批量数据。(mysql批量插入)
- 和导入MySQL快速批量修改及数据导入操作(mysql 如何批量修改)
- 一步搞定!MySQL命令行快速导入SQL文件(mysql命令导入sql文件)
- MySQL中SQL语句实现求差(mysql中sql求差)
- Redis快速实现集合批量添加(redis 集合批量添加)
- 提升提升Oracle中SQL的效率之道(oracle中sql效率)
- Oracle SQL报告精准信息助力数据分析(oracle sql报告)
- XMLHTTP批量抓取远程资料
- MySQL批量插入遇上唯一索引避免方法
- Sql学习第一天——SQL练习题(建表/sql语句)
- python批量修改文件名的实现代码