当运行一个pl/sql程序时, pl/sql语句引擎会执行pl/sql语句。但如果在这个过程中引擎遇到sql语句,它会把这个语句传给sql引擎(后台发生上下文切换)。
在PL/SQL和SQL引擎(engines)中,太多的上下文切换(context switches)会影响性能。这个会发生在当一个循环为集合中的每个元素执行一个单个SQL语句时。而使用批挷定能显著提高性能。
在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 。
作用:BULK COLLECT提供对数据的高速检索。
优点:可以将多个行引入一个或多个集合中,而不是单独变量或记录中,减少了上下文切换,性能高。
缺点:1.消耗更多的内存(PGA);由于该数集合据存储在每个会话中,假设一个会话多消耗5M,内存,那么1000个就消耗约5G内存。
2.BULK COLLECT INTO的目标对象必须是集合类型。
3.不能对使用字符串类型作键的关联数组使用BULK COLLECT子句。
4.如果有一个隐式的数据类型转换,复合目标的集合(如对象类型集合)就不能用于 BULK COLLECTINTO 子句中。
bulk collect语句可以使用三种方式:
1在select into语句中使用bulk collect
2在fetch into中使用bulk collect
3在returning into中使用bulk collect
create table empl_tbl(last_name varchar2(20),
first_name varchar2(10),
salary number(10));
--创建表
create table demo_t(
id number(5),
name varchar2(50),
sales number
);
--在select into语句中使用bulk collect
DECLARE
TYPE contractList IS TABLE OF sad.cm_contracts_t.contract_number%TYPE;
contracts contractList;
BEGIN
-- Limit the number of rows to 100.
SELECT contract_number BULK COLLECT INTO contracts FROM sad.cm_contracts_t
WHERE ROWNUM <= 100;
--在fetch into中使用bulk collect
DECLARE
TYPE contracts_list IS TABLE OF sad.cm_contracts_t%ROWTYPE;
contracts_l contracts_list;
CURSOR c1 IS
SELECT contract_number,contract_id FROM contracts_l WHERE deptno > 10;
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT INTO dept_recs;
END;
--在returning into中使用bulk collect
--CREATE TABLE item AS SELECT * FROM sad.sad_prm_item_ti;
declare
-- Local variables here
TYPE lineid IS TABLE OF item.lineid%TYPE;
line_id lineid;
begin
DELETE FROM item t WHERE t.lineid='1'
RETURNING lineid BULK COLLECT INTO line_id;
dbms_output.put_line('Deleted ' || SQL%ROWCOUNT || ' rows:');
FOR i IN line_id.FIRST .. line_id.LAST
LOOP
dbms_output.put_line('item : '||line_id(i));
END LOOP;
end;
-- Created on 2013/12/4 by ZWX190516
DECLARE
-- Local variables here
all_row NUMBER(10);
all_rows_bluk NUMBER(10);
temp_last_name empl_tbl.last_name%TYPE;
--首先,定义一个Index-by表数据类型
TYPE last_name_tab IS TABLE OF empl_tbl.last_name%TYPE INDEX BY BINARY_INTEGER;
last_name_arr last_name_tab;
--定义一个Index-by表集合变量
dis_count NUMBER;
--数据量
data_count NUMBER :=100000;
--记录时间
t1 NUMBER;
t2 NUMBER;
t3 NUMBER;
t4 NUMBER;
BEGIN
all_row := 0;
all_rows_bluk := 0;
temp_last_name := ' ';
--写入1W笔数据
FOR i IN 1 .. data_count LOOP
INSERT INTO empl_tbl
(last_name, first_name, salary)
VALUES
('carl' || (i), 'wu' || (data_count-1), i);
END LOOP;
COMMIT;
--记录时间
SELECT DBMS_UTILITY.get_time INTO t1 FROM DUAL;
--查询不相同数据使用时间
SELECT COUNT(DISTINCT last_name) "Distinct Last Name"
INTO dis_count
FROM empl_tbl;
--记录时间
SELECT DBMS_UTILITY.get_time INTO t2 FROM DUAL;
--使用简单游标实现
BEGIN
FOR cur IN (SELECT last_name FROM empl_tbl ORDER BY last_name) LOOP
IF cur.last_name != temp_last_name THEN
all_row := all_row + 1;
END IF;
temp_last_name := cur.last_name;
END LOOP;
dbms_output.put_line('all_rows are ' || all_row);
END;
--记录时间
SELECT DBMS_UTILITY.get_time INTO t3 FROM DUAL;
--使用Bulk Collect来实现
BEGIN
SELECT last_name BULK COLLECT INTO last_name_arr FROM empl_tbl;
FOR i IN 1 .. last_name_arr.count LOOP
IF temp_last_name != last_name_arr(i) THEN
all_rows_bluk := all_rows_bluk + 1;
END IF;
temp_last_name := last_name_arr(i);
END LOOP;
dbms_output.put_line(' BULK COLLECT all_rows are ' || all_rows_bluk);
END;
--记录时间
SELECT DBMS_UTILITY.get_time INTO t4 FROM DUAL;
DBMS_OUTPUT.put_line('Execution Time (hsecs)');
DBMS_OUTPUT.put_line('---------------------');
DBMS_OUTPUT.put_line('distince: ' || TO_CHAR(t2 - t1));
DBMS_OUTPUT.put_line('cursor one by one : ' || TO_CHAR(t3 - t2));
DBMS_OUTPUT.put_line('cursor BULK COLLECTe : ' || TO_CHAR(t4 - t3));
END;
FORALL
作用:FORALL可大大改进INSERT、UPDATE和DELETE操作的性能。
在发送语句到SQL引擎前,FORALL语句告知PL/SQL引擎批挷定输入集合。尽管FORALL语句包含一个迭代(iteration)模式,它并不一是个FOR循环。
优点:一次性绑定数据写进行操作。减少上下文切换,提高数据库性能。
缺点:1.操作逻辑单一,只能写一个sql。
2.若出现异常,必须捕获异常后提交才能保证数据正常操作。
forall语句使用三种方式(只允许一条sql):
1.FORALL 下标变量(只能当作下标被引用) IN下限..上限。
2.INDICES OF collection_name (引用特定集合元素的下标(该集合可能为稀疏))。
3.VALUES OF colletion_name (把该集合中的值当作下标,且该集合值的类型只能是PLS_INTEGER BINARY_INTEGER)
)。
--批量插入演示简单;必须顺序
declare
type tb_table_type is table of demo_t%rowtype
index by binary_integer;
tb_table tb_table_type;
begin
for i in 1..10 loop
tb_table(i).id:=i;
tb_table(i).name:='NAME'||i;
end loop;
forall i in 1..tb_table.count
insert into demo_t values tb_table(i);
end;
--批量修改演示
declare
type demo_t_type is table of demo_t%rowtype
index by binary_integer;
demo_t demo_t_type;
begin
for i in 1..10 loop
demo_t(i).id:=i;
demo_t(i).name:='NAMES'||i;
end loop;
forall i in 1..demo_t.count
update tb1 t set row = demo_t(i) where t.id = demo_t(i).id;
end;
--批量删除演示
declare
type demo_t_type is table of demo_t%rowtype
index by binary_integer;
demo_t demo_t_type;
begin
for i in 1..10 loop
demo_t(i).id:=i;
demo_t(i).name:='NAMES'||i;
end loop;
forall i in 1..demo_t.count
delete tb1 where id = demo_t(i).id;
end;
select * from demo_t
--批量写入不连续的数组
declare
type demo_t_table_type is table of demo_t%rowtype
index by binary_integer;
demo_t_table demo_t_table_type;
begin
for i in 1..10 loop
demo_t_table(i).id:=i;
demo_t_table(i).name:='NAME'||i;
end loop;
demo_t_table.delete(3);
demo_t_table.delete(6);
demo_t_table.delete(9);
forall i in indices of demo_t_table
insert into demo_t values demo_t_table(i);
end;
--按照下标写入数组数据
declare
type index_poniter_type is table of pls_integer;
index_poniter index_poniter_type;
type demo_t_table_type is table of demo_t%rowtype
index by binary_integer;
demo_t_table demo_t_table_type;
begin
index_poniter:=index_poniter_type(1,3,5,7);
for i in 1..10 loop
demo_t_table(i).id:=i;
demo_t_table(i).name:='NAME'||i;
end loop;
forall i in values of index_poniter
insert into demo_t values demo_t_table(i);
end;
-- Created on 2013/12/5 by ZWX190516
DECLARE
-- Local variables here
TYPE numtab IS TABLE OF NUMBER(20) INDEX BY BINARY_INTEGER;
TYPE nametab IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
pnums numtab;
pnames nametab;
t1 NUMBER;
t2 NUMBER;
t3 NUMBER;
BEGIN
FOR j IN 1 .. 1000000 LOOP
pnums(j) := j;
pnames(j) := 'Seq No. ' || TO_CHAR(j);
END LOOP;
--记录时间
SELECT DBMS_UTILITY.get_time INTO t1 FROM DUAL;
--普通游标写入
FOR i IN 1 .. 1000000 LOOP
INSERT INTO blktest VALUES (pnums(i), pnames(i));
END LOOP;
--记录时间
SELECT DBMS_UTILITY.get_time INTO t2 FROM DUAL;
--forall写入
FORALL i IN 1 .. 1000000
INSERT INTO blktest VALUES (pnums(i), pnames(i));
--记录时间
SELECT DBMS_UTILITY.get_time INTO t3 FROM DUAL;
DBMS_OUTPUT.put_line('Execution Time (hsecs)');
DBMS_OUTPUT.put_line('---------------------');
DBMS_OUTPUT.put_line('FOR loop: ' || TO_CHAR(t2 - t1));
DBMS_OUTPUT.put_line('FORALL: ' || TO_CHAR(t3 - t2));
END;
异常:Forall在出现异常情况后,捕获到异常不进行事物处理,那么将会自动回滚。
其他用法:
1.用%BULK_ROWCOUNT属性计算FORALL迭代影响行数 。
1.2用%BULK_EXCEPTIONS属性处理FORALL异常
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 Developer Test script 3.0
12
DECLARE
TYPE NumList IS TABLE OF NUMBER;
depts NumList := NumList(10, 20, 50);
BEGIN
FORALL j IN depts.FIRST..depts.LAST
UPDATE demo_t t SET sales = sales * 1.10 WHERE t.id=depts(j);
dbms_output.put_line( SQL%BULK_ROWCOUNT(3) );
END;
在进行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属性使用相同下标。
0
0
PL/SQL Developer Test script 3.0
27
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 demo_t WHERE sales > 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;
执行期间引发的所有异常都被保存游标属性 %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
注意: 1.bulk collect 消耗的是PGA内存。每个会话都会占内存,所以尽可能使用limit限制。
2.当pl/sql优化等级为2(默认)或更高,把游标内容直接写在for循环之中有利于提高性能,除非其他特殊情况。
3.注意cursor%notfond的正确使用。
PL/SQL Developer Test script 3.0
26
-- Created on 2013/11/25 by ZWX190516
declare
-- Local variables here
i integer;
TYPE type_t IS TABLE OF sad.cm_contracts_t%ROWTYPE;
type_table type_t;
CURSOR cur_test IS
SELECT * FROM sad.cm_contracts_t t
WHERE rownum=1;
begin
-- Test statements here
OPEN cur_test;
LOOP
FETCH cur_test BULK COLLECT INTO type_table LIMIT 3;
EXIT WHEN cur_test%NOTFOUND; --该句判断放在前边,导致漏掉数据。
FOR i IN type_table.first..type_table.last
LOOP
dbms_output.put_line(type_table(i).contract_id);
END LOOP;
END LOOP;
CLOSE cur_test;
end;
0
0