- 浏览: 450997 次
- 性别:
- 来自: 上海
文章分类
最新评论
-
鱼里的yeol:
正在学习EJB 感觉有些吃力
Ejb3学习之二----Ejb3的Ejb Bean类型介绍 -
phoenix5870:
默认就是singleton的。
Spring中的Singleton模式和Java中的Singleton模式 -
jhys7s8jd:
pdf打印机下载 无水印http://www.onlinedo ...
PDFCreate工具的使用 -
wang371134086:
:e vil:
浅谈Struts2中的拦截器实现责任链模式 -
liu765023051:
亲,local与remote有什么区别呢
EJB学习之三---Local和Remote接口
FORALL 用法小结:
作者:sonic
本文主要翻译、整理了ORACLE官方文档上有关FORALL的部份内容,不妥之处,还希望多和大家交流。
在发送语句到SQL引擎前,FORALL语句告知PL/SQL 引擎批挷定输入集合。尽管FORALL语句包含一个迭代(iteration)模式,它并不一是个FOR循环。其语法为:
FORALL index IN lower_bound..upper_bound sql_statement;
一、如何使用批挷定提高性能(How Do Bulk Binds Improve Performance)
在PL/SQL 和SQL引擎(engines)中,太多的上下文切换(context switches)会影响性能。这个会发生在当一个循环为集合中的每个元素执行一个单个SQL语句时。而使用批挷定能显著提高性能。下图显示PL/SQL引擎 和SQL引擎之间的context switches:(PL/SQL引擎执行存过语句仅发送SQL语句到SQL引擎,SQL引擎执行语句后返回数据给PL/SQL引擎)
PL/SQL引擎发送一次SQL语句给SQL引擎,在SQL引擎中则为范围中每个index数字执行一次SQL语句。
PL/SQL挷定操作包含以下三类:
in-bind: When a PL/SQL variable or host variable is stored in the database by an INSERT or UPDATE statement.
out-bind:When a database value is assigned to a PL/SQL variable or a host variable by the RETURNING clause of an INSERT, UPDATE, or DELETE statement.
define: When a database value is assigned to a PL/SQL variable or a host variable by a SELECT or FETCH statement.
在SQL语句中,为PL/SQL变量指定值称为挷定(binding),
DML语句能传递所有集合元素到一个单个操作中,这过程称为批挷定(bulk binding)。
如果集合有20个元素,批挷定让你用单个操作等效于执行与20个SELECT,INSERT, UPDATE或DELETE语句。这个技术通过减少在PL/SQL和SQL引擎(engines)间的上下文切换来提高性能。批挷定包括:
1.带INSERT, UPDATE, and DELETE语句的批挷定:在FORALL语句中嵌入SQL语句
2.带SELECT语句的批挷定:在SELECT语句中用BULK COLLECT 语句代替INTO
下边的例子分别用FOR和FORALL进行数据插入,以显示用批挷定的对性能的提高:
SQL> /
Execution Time (secs)
---------------------
FOR loop: 2592
FORALL: 358
PL/SQL procedure successfully completed
从而可以看出FORALL语句在性能上有显著提高。
注释:SQL语句能涉及多个集合,然而,性能提高只适用于下标集合(subscripted collections)
二、FORALL 如何影响回滚(How FORALL Affects Rollbacks)
在FORALL语句中,如果任何SQL语句执行产生未处理的异常(exception),先前执行的所有数据库改变都会被回滚。然而,如果产生的异常被捕获并处理,则回滚改变到一个隐式的保存点,该保存点在每个SQL语句执行前被标记。之前的改变不会被回滚。例如:
DEPTNO JOB
---------- ---------------
10 Clerk temp
10 Clerk temp
20 Bookkeeper
30 Analyst
30 Analyst
上边的例子SQL引擎执行UPDATE语句3次,指定范围内的每个索引号一次。第一个(depts(10))执行成功,但是第二个(depts(20))执行失败(插入值超过了列长),因此,仅仅第二个执行被回滚。
当执行任何SQL语句引发异常时,FORALL语句中断(halt)。上边的例子中,执行第二个UPDATE语句引发异常, 因此第三个语句不会执行。
三、用%BULK_ROWCOUNT 属性计算FORALL迭代影响行数
在进行SQL数据操作语句时,SQL引擎打开一个隐式游标(命名为SQL),该游标的标量属性(scalar attribute)有 %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT。
FORALL语句除具有上边的标量属性外,还有个复合属性(composite attribute):%BULK_ROWCOUNT,该属性具有索引表(index-by table)语法。它的第i个元素存贮SQL语句(INSERT, UPDATE或DELETE)第i个执行的处理行数。如果第i个执行未影响行,%bulk_rowcount (i),返回0。FORALL与%bulk_rowcount属性使用相同下标。例如:
DECLARE
TYPE NumList IS TABLE OF NUMBER;
depts NumList := NumList(10, 20, 50);
BEGIN
FORALL j IN depts.FIRST..depts.LAST
UPDATE emp SET sal = sal * 1.10 WHERE deptno = depts(j);
-- Did the 3rd UPDATE statement affect any rows?
IF SQL%BULK_ROWCOUNT(3) = 0 THEN ...
END;
%ROWCOUNT 返回SQL语句所有执行处理总的行数
%FOUND和 %NOTFOUND仅与SQL语句的最后执行有关,但是,可以使用%BULK_ROWCOUNT推断单个执行的值,如%BULK_ROWCOUNT(i)为0时,%FOUND和%NOTFOUND分别是FALSE和TRUE。
四、用%BULK_EXCEPTIONS属性处理FORALL异常
在执行FORALL语句期间,PL/SQL提供一个处理异常的机制。该机制使批挷定(bulk-bind)操作能保存异常信息并继续执行。方法是在FORALL语句中增加SAVE EXCEPTIONS关键字。语法为:
FORALL index IN lower_bound..upper_bound SAVE EXCEPTIONS
{insert_stmt | update_stmt | delete_stmt}
执行期间引发的所有异常都被保存游标属性 %BULK_EXCEPTIONS中,它存贮一个集合记录,每记录有两个字段:
%BULK_EXCEPTIONS(i).ERROR_INDEX:存贮在引发异常期间FORALL语句迭代(重复:iteration)
%BULK_EXCEPTIONS(i).ERROR_CODE:存贮相应的Oracle错误代码
%BULK_EXCEPTIONS.COUNT存贮异常的数量。(该属性不是%BULK_EXCEPTIONS集合记录的字段)。如果忽略SAVE EXCEPTIONS,当引发异常时,FORALL语句停止执行。此时,SQL%BULK_EXCEPTIONS.COUNT 返回1, 且SQL%BULK_EXCEPTIONS只包含一条记录。如果执行期间无异常 SQL%BULK_EXCEPTIONS.COUNT 返回 0.例子:
DECLARE
TYPE NumList IS TABLE OF NUMBER;
num_tab NumList := NumList(10,0,11,12,30,0,20,199,2,0,9,1);
errors NUMBER;
dml_errors EXCEPTION;
PRAGMA exception_init(dml_errors, -24381);
BEGIN
FORALL i IN num_tab.FIRST..num_tab.LAST SAVE EXCEPTIONS
DELETE FROM emp WHERE sal > 500000/num_tab(i);
EXCEPTION
WHEN dml_errors THEN
errors := SQL%BULK_EXCEPTIONS.COUNT;
dbms_output.put_line('Number of errors is ' || errors);
FOR i IN 1..errors LOOP
dbms_output.put_line('Error ' || i || ' occurred during '||
'iteration ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
dbms_output.put_line('Oracle error is ' ||
SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
END;
该例子中,当i等于2,6,10时,产生异常ZERO_DIVIDE,完成后SQL%BULK_EXCEPTIONS.COUNT为3,其值为(2,1476), (6,1476)和(10,1476),错误输出如下:
Number of errors is 3
Error 1 occurred during iteration 2
Oracle error is ORA-01476: divisor is equal to zero
Error 2 occurred during iteration 6
Oracle error is ORA-01476: divisor is equal to zero
Error 3 occurred during iteration 10
Oracle error is ORA-01476: divisor is equal to zero
五、用BULK COLLECT子句取回查询结果至集合中
在返回到PL/SQL引擎之前,关键字BULK COLLECT告诉SQL引擎批挷定输出集合。该关键字能用于SELECT INTO, FETCH INTO和RETURNING INTO语句中。语法如下:
... BULK COLLECT INTO collection_name[, collection_name] ...
示例1:
DECLARE
TYPE NumTab IS TABLE OF emp.empno%TYPE;
TYPE NameTab IS TABLE OF emp.ename%TYPE;
enums NumTab; -- no need to initialize
names NameTab;
BEGIN
SELECT empno, ename BULK COLLECT INTO enums, names FROM emp;
...
END;
示例2:
CREATE TYPE Coords AS OBJECT (x NUMBER, y NUMBER);
CREATE TABLE grid (num NUMBER, loc Coords);
INSERT INTO grid VALUES(10, Coords(1,2));
INSERT INTO grid VALUES(20, Coords(3,4));
DECLARE
TYPE CoordsTab IS TABLE OF Coords;
pairs CoordsTab;
BEGIN
SELECT loc BULK COLLECT INTO pairs FROM grid;
-- now pairs contains (1,2) and (3,4)
END;
示例3:
DECLARE
TYPE SalList IS TABLE OF emp.sal%TYPE;
sals SalList;
BEGIN
SELECT sal BULK COLLECT INTO sals FROM emp
WHERE ROWNUM <= 100;
...
END;
示例4:Examples of Bulk Fetching from a Cursor:
DECLARE
TYPE NameList IS TABLE OF emp.ename%TYPE;
TYPE SalList IS TABLE OF emp.sal%TYPE;
CURSOR c1 IS SELECT ename, sal FROM emp WHERE sal > 1000;
names NameList;
sals SalList;
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT INTO names, sals; --可返回到一个或多个集合
END;
示例5:Examples of Bulk Fetching from a Cursor:
DECLARE
TYPE DeptRecTab IS TABLE OF dept%ROWTYPE;
dept_recs DeptRecTab;
CURSOR c1 IS
SELECT deptno, dname, loc FROM dept WHERE deptno > 10;
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT INTO dept_recs; --返回到一个记录(records)集合
END;
六、用LIMIT 子句限制批取出操作行
LIMIT子句可限制从数据库中取出的行数。该子句仅能用于批(非标量 not scalar)FETCH语句.语法为:
FETCH ... BULK COLLECT INTO ... [LIMIT rows];
rows可以是文字的(literal),变量(variable)或表达式(expression),但必须返回一个数字。否则,PL/SQL引发预定义异常VALUE_ERROR,如果为负数,PL/SQ引发INVALID_NUMBER。如果需要,PL/SQL四舍五入(round)为最近的整数.
下例中,在每个循环迭代中,FETCH语句返回到索引表empnos中,先前的值会被覆盖。
DECLARE
TYPE NumTab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
CURSOR c1 IS SELECT empno FROM emp;
empnos NumTab;
rows NATURAL := 10;
BEGIN
OPEN c1;
LOOP
/**//* The following statement fetches 10 rows (or less). */
FETCH c1 BULK COLLECT INTO empnos LIMIT rows;
EXIT WHEN c1%NOTFOUND;
...
END LOOP;
CLOSE c1;
END;
七、用RETURNING INTO子句取回DML结果至集合
你能在INSERT、UPDATE或DELETE语句的RETURNING INTO子句中使用BULK COLLECT子句。(注意,此处是没有SELECT语句的)
DECLARE
TYPE NumList IS TABLE OF emp.empno%TYPE;
enums NumList;
BEGIN
DELETE FROM emp WHERE deptno = 20
RETURNING empno BULK COLLECT INTO enums;
-- if there were five employees in department 20,
-- then enums contains five employee numbers
END;
八、BULK COLLECT上的限制
1.You cannot bulk collect into an associative array that has a string type for the key. .
2.BULK COLLECT语句只能用于服务器端(server-side),而非客户端
3.在BULK COLLECT INTO语句中的所有目标必须是集合,如下例:
DECLARE
TYPE NameList IS TABLE OF emp.ename%TYPE;
names NameList;
salary emp.sal%TYPE;
BEGIN
SELECT ename, sal BULK COLLECT INTO names, salary -- illegal target
FROM emp WHERE ROWNUM < 50;
...
END;.
4.复合目标(如对象)不能用于RETURNING INTO子句中.
5.当需要隐式数据类型转换时,多复合目标(如对象集合)不能用于bulk collect into子句。
九、同时使用FORALL 和BULK COLLECT
Using FORALL and BULK COLLECT Together
你能使FORALL语句与BULK COLLECT结合,如下例:如果集合depts有3个元素,每个元素导致5行被删除,则语句完成时,集合enums有15个元素:
FORALL j IN depts.FIRST..depts.LAST
DELETE FROM emp WHERE empno = depts(j)
RETURNING empno BULK COLLECT INTO enums;
注意:不能在FORALL语句中使用SELECT ... BULK COLLECT语句。
十、Using Host Arrays with Bulk Binds
客户端程序能用匿名PL/SQL块批挷定输入和输出数组(arrays)。实际上,这是与服务器端传递集合最有效的方式。
Host arrays are declared in a host environment such as an OCI or Pro*C program and must be prefixed with a colon to distinguish them from PL/SQL collections. In the example below, an input host array is used in a DELETE statement. At run time, the anonymous PL/SQL block is sent to the database server for execution.
DECLARE
...
BEGIN
-- assume that values were assigned to the host array
-- and host variables in the host environment
FORALL i IN :lower..:upper
DELETE FROM emp WHERE deptno = :depts(i);
...
END;
限制:以下限制将应用于FORALL语句:
1. 在FORALL循环中,UPDATE语句中的SET子句和WHERE子句中不能指向同一个集合,你可能需要获得另一个集合副本,以在WHERE子句指向新的名称。
2. INSERT,UPDATE或DELETE语句必须至少涉及一个集合。如在LOOP插入一组常量的FORALL语句将引发异常。( FORALL的INSERT之类的语句一定要用in-bind 方式,如:
SQL> BEGIN
2 FORALL i IN 1..100
3 INSERT INTO parts VALUES(i,i);
4 END;
5 /
ORA-06550: 第 3 行, 第 5 列:
PLS-00435: 没有 BULK In-BIND 的 DML 语句在 FORALL 内不能使用
ORA-06550: 第 2 行, 第 12 列:
PL/SQL: Statement ignored
3. 指定范围内的所有集合元素必须存在,如果一元素丢失或删除,你将收到一个错误,如:
DECLARE
TYPE NumList IS TABLE OF NUMBER;
depts NumList := NumList(10, 20, 30, 40);
BEGIN
depts.DELETE(3); -- delete third element
FORALL i IN depts.FIRST..depts.LAST
DELETE FROM emp WHERE deptno = depts(i); -- causes an error
END;
4. 下例显示,复合值的输入集合不能被分解和跳跃数据库列
CREATE TABLE coords (x NUMBER, y NUMBER);
CREATE TYPE Pair AS OBJECT (m NUMBER, n NUMBER);
DECLARE
TYPE PairTab IS TABLE OF Pair;
pairs PairTab := PairTab(Pair(1,2), Pair(3,4), Pair(5,6));
TYPE NumTab IS TABLE OF NUMBER;
nums NumTab := NumTab(1, 2, 3);
BEGIN
/**//* The following statement fails. */
FORALL i IN 1..3
UPDATE coords SET (x, y) = pairs(i)
WHERE x = nums(i);
END;
The workaround is to decompose the composite values manually:
DECLARE
TYPE PairTab IS TABLE OF Pair;
pairs PairTab := PairTab(Pair(1,2), Pair(3,4), Pair(5,6));
TYPE NumTab IS TABLE OF NUMBER;
nums NumTab := NumTab(1, 2, 3);
BEGIN
/**//* The following statement succeeds. */
FORALL i in 1..3
UPDATE coords SET (x, y) = (pairs(i).m, pairs(i).n)
WHERE x = nums(i);
END;
5. 集合下标不能是表达式,示例:
FORALL j IN mgrs.FIRST..mgrs.LAST
DELETE FROM emp WHERE mgr = mgrs(j+1); -- invalid subscript
6. 游标属性%BULK_ROWCOUNT不能分配给其它集合,同样,它也不能作为参数传递到子程序。
作者:sonic
本文主要翻译、整理了ORACLE官方文档上有关FORALL的部份内容,不妥之处,还希望多和大家交流。
在发送语句到SQL引擎前,FORALL语句告知PL/SQL 引擎批挷定输入集合。尽管FORALL语句包含一个迭代(iteration)模式,它并不一是个FOR循环。其语法为:
FORALL index IN lower_bound..upper_bound sql_statement;
一、如何使用批挷定提高性能(How Do Bulk Binds Improve Performance)
在PL/SQL 和SQL引擎(engines)中,太多的上下文切换(context switches)会影响性能。这个会发生在当一个循环为集合中的每个元素执行一个单个SQL语句时。而使用批挷定能显著提高性能。下图显示PL/SQL引擎 和SQL引擎之间的context switches:(PL/SQL引擎执行存过语句仅发送SQL语句到SQL引擎,SQL引擎执行语句后返回数据给PL/SQL引擎)
PL/SQL引擎发送一次SQL语句给SQL引擎,在SQL引擎中则为范围中每个index数字执行一次SQL语句。
PL/SQL挷定操作包含以下三类:
in-bind: When a PL/SQL variable or host variable is stored in the database by an INSERT or UPDATE statement.
out-bind:When a database value is assigned to a PL/SQL variable or a host variable by the RETURNING clause of an INSERT, UPDATE, or DELETE statement.
define: When a database value is assigned to a PL/SQL variable or a host variable by a SELECT or FETCH statement.
在SQL语句中,为PL/SQL变量指定值称为挷定(binding),
DML语句能传递所有集合元素到一个单个操作中,这过程称为批挷定(bulk binding)。
如果集合有20个元素,批挷定让你用单个操作等效于执行与20个SELECT,INSERT, UPDATE或DELETE语句。这个技术通过减少在PL/SQL和SQL引擎(engines)间的上下文切换来提高性能。批挷定包括:
1.带INSERT, UPDATE, and DELETE语句的批挷定:在FORALL语句中嵌入SQL语句
2.带SELECT语句的批挷定:在SELECT语句中用BULK COLLECT 语句代替INTO
下边的例子分别用FOR和FORALL进行数据插入,以显示用批挷定的对性能的提高:
SQL> SET SERVEROUTPUT ON SQL> CREATE TABLE parts (pnum NUMBER(4), pname CHAR(15)); Table created. SQL> DECLARE 2 TYPE NumTab IS TABLE OF parts.pnum%TYPE INDEX BY BINARY_INTEGER; 3 TYPE NameTab IS TABLE OF parts.pname%TYPE INDEX BY BINARY_INTEGER; 4 pnums NumTab; 5 Pnames NameTab; 6 t1 NUMBER; 7 t2 NUMBER; 8 t3 NUMBER; 9 BEGIN 10 FOR i IN 1..500000 LOOP 11 pnums(i) := i; 12 pnames(i) := 'Part No.'||to_char(i); 13 END LOOP; 14 t1 := dbms_utility.get_time; 15 16 FOR i IN 1..500000 LOOP 17 INSERT INTO parts VALUES(pnums(i),pnames(i)); 18 END LOOP; 19 t2 := dbms_utility.get_time; 20 21 FORALL i IN 1..500000 22 INSERT INTO parts VALUES(pnums(i),pnames(i)); 23 t3 := dbms_utility.get_time; 24 25 dbms_output.put_line('Execution Time (secs)'); 26 dbms_output.put_line('---------------------'); 27 dbms_output.put_line('FOR loop: ' || TO_CHAR(t2 - t1)); 28 dbms_output.put_line('FORALL: ' || TO_CHAR(t3 - t2)); 29 END;
SQL> /
Execution Time (secs)
---------------------
FOR loop: 2592
FORALL: 358
PL/SQL procedure successfully completed
从而可以看出FORALL语句在性能上有显著提高。
注释:SQL语句能涉及多个集合,然而,性能提高只适用于下标集合(subscripted collections)
二、FORALL 如何影响回滚(How FORALL Affects Rollbacks)
在FORALL语句中,如果任何SQL语句执行产生未处理的异常(exception),先前执行的所有数据库改变都会被回滚。然而,如果产生的异常被捕获并处理,则回滚改变到一个隐式的保存点,该保存点在每个SQL语句执行前被标记。之前的改变不会被回滚。例如:
CREATE TABLE emp2 (deptno NUMBER(2), job VARCHAR2(15)); INSERT INTO emp2 VALUES(10, 'Clerk'); INSERT INTO emp2 VALUES(10, 'Clerk'); INSERT INTO emp2 VALUES(20, 'Bookkeeper'); -- 10-char job title INSERT INTO emp2 VALUES(30, 'Analyst'); INSERT INTO emp2 VALUES(30, 'Analyst'); Comit; DECLARE TYPE NumList IS TABLE OF NUMBER; depts NumList := NumList(10, 20, 30); BEGIN FORALL j IN depts.FIRST..depts.LAST UPDATE emp2 SET job = job || ' (temp)' WHERE deptno = depts(j); -- raises a "value too large" exception EXCEPTION WHEN OTHERS THEN COMMIT; END; / PL/SQL procedure successfully completed SQL> select * from emp2;
DEPTNO JOB
---------- ---------------
10 Clerk temp
10 Clerk temp
20 Bookkeeper
30 Analyst
30 Analyst
上边的例子SQL引擎执行UPDATE语句3次,指定范围内的每个索引号一次。第一个(depts(10))执行成功,但是第二个(depts(20))执行失败(插入值超过了列长),因此,仅仅第二个执行被回滚。
当执行任何SQL语句引发异常时,FORALL语句中断(halt)。上边的例子中,执行第二个UPDATE语句引发异常, 因此第三个语句不会执行。
三、用%BULK_ROWCOUNT 属性计算FORALL迭代影响行数
在进行SQL数据操作语句时,SQL引擎打开一个隐式游标(命名为SQL),该游标的标量属性(scalar attribute)有 %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT。
FORALL语句除具有上边的标量属性外,还有个复合属性(composite attribute):%BULK_ROWCOUNT,该属性具有索引表(index-by table)语法。它的第i个元素存贮SQL语句(INSERT, UPDATE或DELETE)第i个执行的处理行数。如果第i个执行未影响行,%bulk_rowcount (i),返回0。FORALL与%bulk_rowcount属性使用相同下标。例如:
DECLARE
TYPE NumList IS TABLE OF NUMBER;
depts NumList := NumList(10, 20, 50);
BEGIN
FORALL j IN depts.FIRST..depts.LAST
UPDATE emp SET sal = sal * 1.10 WHERE deptno = depts(j);
-- Did the 3rd UPDATE statement affect any rows?
IF SQL%BULK_ROWCOUNT(3) = 0 THEN ...
END;
%ROWCOUNT 返回SQL语句所有执行处理总的行数
%FOUND和 %NOTFOUND仅与SQL语句的最后执行有关,但是,可以使用%BULK_ROWCOUNT推断单个执行的值,如%BULK_ROWCOUNT(i)为0时,%FOUND和%NOTFOUND分别是FALSE和TRUE。
四、用%BULK_EXCEPTIONS属性处理FORALL异常
在执行FORALL语句期间,PL/SQL提供一个处理异常的机制。该机制使批挷定(bulk-bind)操作能保存异常信息并继续执行。方法是在FORALL语句中增加SAVE EXCEPTIONS关键字。语法为:
FORALL index IN lower_bound..upper_bound SAVE EXCEPTIONS
{insert_stmt | update_stmt | delete_stmt}
执行期间引发的所有异常都被保存游标属性 %BULK_EXCEPTIONS中,它存贮一个集合记录,每记录有两个字段:
%BULK_EXCEPTIONS(i).ERROR_INDEX:存贮在引发异常期间FORALL语句迭代(重复:iteration)
%BULK_EXCEPTIONS(i).ERROR_CODE:存贮相应的Oracle错误代码
%BULK_EXCEPTIONS.COUNT存贮异常的数量。(该属性不是%BULK_EXCEPTIONS集合记录的字段)。如果忽略SAVE EXCEPTIONS,当引发异常时,FORALL语句停止执行。此时,SQL%BULK_EXCEPTIONS.COUNT 返回1, 且SQL%BULK_EXCEPTIONS只包含一条记录。如果执行期间无异常 SQL%BULK_EXCEPTIONS.COUNT 返回 0.例子:
DECLARE
TYPE NumList IS TABLE OF NUMBER;
num_tab NumList := NumList(10,0,11,12,30,0,20,199,2,0,9,1);
errors NUMBER;
dml_errors EXCEPTION;
PRAGMA exception_init(dml_errors, -24381);
BEGIN
FORALL i IN num_tab.FIRST..num_tab.LAST SAVE EXCEPTIONS
DELETE FROM emp WHERE sal > 500000/num_tab(i);
EXCEPTION
WHEN dml_errors THEN
errors := SQL%BULK_EXCEPTIONS.COUNT;
dbms_output.put_line('Number of errors is ' || errors);
FOR i IN 1..errors LOOP
dbms_output.put_line('Error ' || i || ' occurred during '||
'iteration ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
dbms_output.put_line('Oracle error is ' ||
SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
END;
该例子中,当i等于2,6,10时,产生异常ZERO_DIVIDE,完成后SQL%BULK_EXCEPTIONS.COUNT为3,其值为(2,1476), (6,1476)和(10,1476),错误输出如下:
Number of errors is 3
Error 1 occurred during iteration 2
Oracle error is ORA-01476: divisor is equal to zero
Error 2 occurred during iteration 6
Oracle error is ORA-01476: divisor is equal to zero
Error 3 occurred during iteration 10
Oracle error is ORA-01476: divisor is equal to zero
五、用BULK COLLECT子句取回查询结果至集合中
在返回到PL/SQL引擎之前,关键字BULK COLLECT告诉SQL引擎批挷定输出集合。该关键字能用于SELECT INTO, FETCH INTO和RETURNING INTO语句中。语法如下:
... BULK COLLECT INTO collection_name[, collection_name] ...
示例1:
DECLARE
TYPE NumTab IS TABLE OF emp.empno%TYPE;
TYPE NameTab IS TABLE OF emp.ename%TYPE;
enums NumTab; -- no need to initialize
names NameTab;
BEGIN
SELECT empno, ename BULK COLLECT INTO enums, names FROM emp;
...
END;
示例2:
CREATE TYPE Coords AS OBJECT (x NUMBER, y NUMBER);
CREATE TABLE grid (num NUMBER, loc Coords);
INSERT INTO grid VALUES(10, Coords(1,2));
INSERT INTO grid VALUES(20, Coords(3,4));
DECLARE
TYPE CoordsTab IS TABLE OF Coords;
pairs CoordsTab;
BEGIN
SELECT loc BULK COLLECT INTO pairs FROM grid;
-- now pairs contains (1,2) and (3,4)
END;
示例3:
DECLARE
TYPE SalList IS TABLE OF emp.sal%TYPE;
sals SalList;
BEGIN
SELECT sal BULK COLLECT INTO sals FROM emp
WHERE ROWNUM <= 100;
...
END;
示例4:Examples of Bulk Fetching from a Cursor:
DECLARE
TYPE NameList IS TABLE OF emp.ename%TYPE;
TYPE SalList IS TABLE OF emp.sal%TYPE;
CURSOR c1 IS SELECT ename, sal FROM emp WHERE sal > 1000;
names NameList;
sals SalList;
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT INTO names, sals; --可返回到一个或多个集合
END;
示例5:Examples of Bulk Fetching from a Cursor:
DECLARE
TYPE DeptRecTab IS TABLE OF dept%ROWTYPE;
dept_recs DeptRecTab;
CURSOR c1 IS
SELECT deptno, dname, loc FROM dept WHERE deptno > 10;
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT INTO dept_recs; --返回到一个记录(records)集合
END;
六、用LIMIT 子句限制批取出操作行
LIMIT子句可限制从数据库中取出的行数。该子句仅能用于批(非标量 not scalar)FETCH语句.语法为:
FETCH ... BULK COLLECT INTO ... [LIMIT rows];
rows可以是文字的(literal),变量(variable)或表达式(expression),但必须返回一个数字。否则,PL/SQL引发预定义异常VALUE_ERROR,如果为负数,PL/SQ引发INVALID_NUMBER。如果需要,PL/SQL四舍五入(round)为最近的整数.
下例中,在每个循环迭代中,FETCH语句返回到索引表empnos中,先前的值会被覆盖。
DECLARE
TYPE NumTab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
CURSOR c1 IS SELECT empno FROM emp;
empnos NumTab;
rows NATURAL := 10;
BEGIN
OPEN c1;
LOOP
/**//* The following statement fetches 10 rows (or less). */
FETCH c1 BULK COLLECT INTO empnos LIMIT rows;
EXIT WHEN c1%NOTFOUND;
...
END LOOP;
CLOSE c1;
END;
七、用RETURNING INTO子句取回DML结果至集合
你能在INSERT、UPDATE或DELETE语句的RETURNING INTO子句中使用BULK COLLECT子句。(注意,此处是没有SELECT语句的)
DECLARE
TYPE NumList IS TABLE OF emp.empno%TYPE;
enums NumList;
BEGIN
DELETE FROM emp WHERE deptno = 20
RETURNING empno BULK COLLECT INTO enums;
-- if there were five employees in department 20,
-- then enums contains five employee numbers
END;
八、BULK COLLECT上的限制
1.You cannot bulk collect into an associative array that has a string type for the key. .
2.BULK COLLECT语句只能用于服务器端(server-side),而非客户端
3.在BULK COLLECT INTO语句中的所有目标必须是集合,如下例:
DECLARE
TYPE NameList IS TABLE OF emp.ename%TYPE;
names NameList;
salary emp.sal%TYPE;
BEGIN
SELECT ename, sal BULK COLLECT INTO names, salary -- illegal target
FROM emp WHERE ROWNUM < 50;
...
END;.
4.复合目标(如对象)不能用于RETURNING INTO子句中.
5.当需要隐式数据类型转换时,多复合目标(如对象集合)不能用于bulk collect into子句。
九、同时使用FORALL 和BULK COLLECT
Using FORALL and BULK COLLECT Together
你能使FORALL语句与BULK COLLECT结合,如下例:如果集合depts有3个元素,每个元素导致5行被删除,则语句完成时,集合enums有15个元素:
FORALL j IN depts.FIRST..depts.LAST
DELETE FROM emp WHERE empno = depts(j)
RETURNING empno BULK COLLECT INTO enums;
注意:不能在FORALL语句中使用SELECT ... BULK COLLECT语句。
十、Using Host Arrays with Bulk Binds
客户端程序能用匿名PL/SQL块批挷定输入和输出数组(arrays)。实际上,这是与服务器端传递集合最有效的方式。
Host arrays are declared in a host environment such as an OCI or Pro*C program and must be prefixed with a colon to distinguish them from PL/SQL collections. In the example below, an input host array is used in a DELETE statement. At run time, the anonymous PL/SQL block is sent to the database server for execution.
DECLARE
...
BEGIN
-- assume that values were assigned to the host array
-- and host variables in the host environment
FORALL i IN :lower..:upper
DELETE FROM emp WHERE deptno = :depts(i);
...
END;
限制:以下限制将应用于FORALL语句:
1. 在FORALL循环中,UPDATE语句中的SET子句和WHERE子句中不能指向同一个集合,你可能需要获得另一个集合副本,以在WHERE子句指向新的名称。
2. INSERT,UPDATE或DELETE语句必须至少涉及一个集合。如在LOOP插入一组常量的FORALL语句将引发异常。( FORALL的INSERT之类的语句一定要用in-bind 方式,如:
SQL> BEGIN
2 FORALL i IN 1..100
3 INSERT INTO parts VALUES(i,i);
4 END;
5 /
ORA-06550: 第 3 行, 第 5 列:
PLS-00435: 没有 BULK In-BIND 的 DML 语句在 FORALL 内不能使用
ORA-06550: 第 2 行, 第 12 列:
PL/SQL: Statement ignored
3. 指定范围内的所有集合元素必须存在,如果一元素丢失或删除,你将收到一个错误,如:
DECLARE
TYPE NumList IS TABLE OF NUMBER;
depts NumList := NumList(10, 20, 30, 40);
BEGIN
depts.DELETE(3); -- delete third element
FORALL i IN depts.FIRST..depts.LAST
DELETE FROM emp WHERE deptno = depts(i); -- causes an error
END;
4. 下例显示,复合值的输入集合不能被分解和跳跃数据库列
CREATE TABLE coords (x NUMBER, y NUMBER);
CREATE TYPE Pair AS OBJECT (m NUMBER, n NUMBER);
DECLARE
TYPE PairTab IS TABLE OF Pair;
pairs PairTab := PairTab(Pair(1,2), Pair(3,4), Pair(5,6));
TYPE NumTab IS TABLE OF NUMBER;
nums NumTab := NumTab(1, 2, 3);
BEGIN
/**//* The following statement fails. */
FORALL i IN 1..3
UPDATE coords SET (x, y) = pairs(i)
WHERE x = nums(i);
END;
The workaround is to decompose the composite values manually:
DECLARE
TYPE PairTab IS TABLE OF Pair;
pairs PairTab := PairTab(Pair(1,2), Pair(3,4), Pair(5,6));
TYPE NumTab IS TABLE OF NUMBER;
nums NumTab := NumTab(1, 2, 3);
BEGIN
/**//* The following statement succeeds. */
FORALL i in 1..3
UPDATE coords SET (x, y) = (pairs(i).m, pairs(i).n)
WHERE x = nums(i);
END;
5. 集合下标不能是表达式,示例:
FORALL j IN mgrs.FIRST..mgrs.LAST
DELETE FROM emp WHERE mgr = mgrs(j+1); -- invalid subscript
6. 游标属性%BULK_ROWCOUNT不能分配给其它集合,同样,它也不能作为参数传递到子程序。
发表评论
-
Oracle的Hash Join之探究整理
2012-04-08 15:48 11389Hash join算法原理 自从or ... -
Oracle Event 10046
2012-03-29 23:17 1641下面是一个10046的例子,可以用来分析具体的一些sql执行计 ... -
使用SQL_TRACE进行数据库诊断
2012-03-29 23:08 1014From:http://www.eygle.com/archi ... -
Oracle索引之B-Tree和Bitmap索引对比
2012-03-23 18:32 2364B树索引是所有大型关系 ... -
Oracle性能优化五大工具介绍
2012-03-23 15:55 1597本文介绍了Oracle性能优化工具Oracle数据库在线数据字 ... -
Oracle事物处理中回滚段容量的问题
2012-03-23 15:33 1637在执行大事务时,有时oracle会报出如下的错误: ORA- ... -
Oracle 中条件分歧总结
2011-06-01 23:17 1346Oracle 中条件分歧总结: * Decode * IF ... -
ORACLE CASE WHEN 及 SELECT CASE WHEN的用法
2011-06-01 23:07 2357转载:http://blog.csdn.net/songsen ... -
View的作用
2010-11-11 00:53 3204这篇文章重要讲述下关 ... -
Oracle For Update 行锁
2010-11-08 23:43 1998转自:http://hi.baidu.com/mcj0127/ ... -
Mysql的存储引擎:InnoDB和MyISAM区别
2010-10-05 02:25 1379InnoDB和MyISAM是许多人在 ... -
SSMAを活用してMySQL/AccessからSQL Server/Azureへマイグレーション
2010-09-10 23:03 2351作者 Abel Avram , 翻訳者 (株)ネクストスケープ ... -
Oracle 嵌套事务与自治事务思考
2010-04-22 21:35 6259关键字 嵌套事务和自治事务的概念 嵌套事务的使用 ... -
Oracle中Cursor介绍
2010-04-21 22:09 1734关键字 概念 类型 异常处理 一 概念 游标是SQL ... -
Oracle 10g 中动态性能视图
2010-04-16 19:18 2059动态性能视图用于记录当前例程的活动。启动例程时,oracle会 ... -
Oracle 和 Sql Server中日期的显示问题
2010-03-10 02:01 2885在日常的项目中,经常遇见User需要显示不同的日期格式。当然, ... -
Oracle中RowNum的用法
2010-02-08 22:05 1815ROWNUM,是一种伪列,它根据特定记录返回一个序列化的数字。 ... -
ORACLE 分析函数解析
2010-01-17 21:29 1363分析函数是oracle 8.1.6中就引入的一个全新的概念,为 ... -
ORACLE LOB大对象处理
2010-01-16 21:42 2466ORACLE LOB大对象处理 主要是用来存储大量数据的数据库 ... -
DUMP用法
2010-01-16 09:13 1867一 DUMP():查看表中列在datafile中的存储内容,它 ...
相关推荐
### FORALL 用法小结:提升Oracle性能的关键 #### 引言 在Oracle数据库的开发与维护过程中,性能优化始终是关注的重点之一。其中,`FORALL`语句作为Oracle PL/SQL的一个强大特性,提供了批量绑定和批量收集的能力...
### FORALL 用法小结 #### 一、概述 `FORALL` 是 Oracle PL/SQL 中的一个强大特性,主要用于批量处理数据,如批量插入、更新或删除等操作。使用 `FORALL` 可以显著提升数据库应用程序的性能,尤其是在处理大量数据...
C#中checkboxList控件用法总结 CheckboxList控件是C#中常用的控件之一,主要用于显示多选项列表。以下是关于CheckboxList控件的17种常用方法的总结。 一、添加项 CheckboxList控件可以使用Items.Add方法来添加新...
### MySQL使用小结 在日常工作中,MySQL作为一款广泛使用的开源关系型数据库管理系统,其重要性不言而喻。为了更好地理解和掌握MySQL的基本操作及管理技巧,本文将根据提供的文件内容进行详细的解释与扩展。 #### ...
【IT的用法总结】 IT在英语中的使用非常广泛,既可以作为人称代词,也可以作为非人称代词,并且常常充当形式主语的角色。下面是对这些用法的详细解释: 1. **IT作为人称代词** - **指事物**:IT可以指代除人以外...
总结起来,BULK COLLECT 和 FORALL 都是PL/SQL中减少上下文切换、提升效率的重要工具。它们允许开发者以更高效的方式处理大量数据,降低了系统资源的消耗,提高了应用程序的整体性能。在设计处理大数据量的PL/SQL...
本文将深入探讨虚拟语气在条件从句中的用法。 首先,条件句分为真实条件句和虚拟条件句。真实条件句基于可能发生的情况,而虚拟条件句则涉及不可能或未发生的情况。例如,"If it doesn’t rain tomorrow, we will ...
以下是对"常用介词用法总结.ppt"中提及的一些常见介词的详细解释: 1. **about**: - 表示“论及,谈起,涉及,着手”等意义,如`arrange about`, `argue about`, `complain about`等。例如,"She inquired about ...
【achieve】这个词在英语中是一个非常重要的...总结来说,achieve是一个表达完成或达成目标的核心词汇,其用法丰富多样,需要根据上下文选择合适的搭配。正确理解和运用achieve,能让你在英语表达中更加准确和地道。
- 例句:The kitchen appliances were all made of stainless steel, giving them a sleek, modern look. (厨房电器都是不锈钢制成的,显得既光滑又现代。) 2. **钢铁业的**:与钢铁产业相关的。 - 例句:The ...
- `FOR BROWSE` 子句不适用于包含 `UNION` 或 `UNION ALL` 的语句。 #### 三、其他相关运算符 除了 `UNION` 和 `UNION ALL` 之外,还有一些其他的运算符可以用于处理多个结果集: ##### 1. EXCEPT - **定义**: `...
3. 当陈述部分的主语是"everything", "nothing", "anything", "something"时,反义疑问句的主语通常用"it",如:“Everything seems all right now, doesn’t it?” 和 “Nothing is kept in good order, is it?” ...
总结来说,`mysqldiff` 是一款强大的 MySQL 数据库结构比较工具,通过灵活的参数设置,可以帮助开发者轻松地管理和同步数据库。结合其他类似的工具,我们可以更高效地管理数据库,确保数据的一致性和完整性。在实际...
本文将对英语过去完成时的用法进行总结,并通过实例及练习题帮助读者更深入地理解和掌握这一语法点。 首先,让我们回顾过去完成时的基本结构。过去完成时由助动词“had”加上动词的过去分词形式构成,例如“had ...
以下是对这些连词用法的详细总结: 1. **and**:连接两个并列成分,表示“和”、“并且”的意思。例如题目中的第五题,“Anna, what a nice dress you are wearing today!--Thank you. It fits me well, **and** ...
知识点总结: 1. Mybatis批量foreach merge into的用法可以批量插入时间价格表数据。 2. 使用Oracle 9i及其以后版本支持的merge into语句可以实现insertOrUpdate的功能。 3. Mybatis的动态SQL语法foreach循环插入...
**特殊用法**:双小括号 `((…))` 使得在bash Shell中可以进行算术运算和C语言风格的变量操作,例如: ```bash for ((I=1; I; I++)); do echo $I done ``` **应用实例**: - 计算1-100之间所有正整数的和 ```...
2. 用法: - **影响现在**:过去发生的动作对现在产生了影响。常与副词如"just", "already", "never", "ever", "before", "yet", "once", "twice", "many times", "how many times", "so far", "during the past ...
6. all kinds of:各种各样的,a kind of:一种,例如"We need all kinds of resources to sustain life, including a kind of mineral called iron." 7. along with:与...一起,如同,例如"I will go along with ...