FOR ALL
语法
image.png
bounds_clause ::=
image.png
关键字与参数介绍
==index_name：一个无需声明的标识符，作为集合下标使用； ==sql_statement：静态语句，例如：UPDATE或者DELETE；或者动态（EXECUTE IMMEDIATE）DML语句。 ==SAVE EXCEPTIONS：可选关键字，表示即使一些DML语句失败，直到FORALL loop执行完毕才抛出异常。可以使用SQL%BULK_EXCEPTIONS 查看异常信息。 ==lower_bound .. upper_bound：数字表达式，来指定一组连续有效的索引数字。该表达式只需解析一次。 ==INDICES OF collection_name：用于指向稀疏数组的实际下标 ==VALUES OF index_collection_name：用于指向集合的一个子集的下标数组
语法1：
FORALL 下标变量(只能当作下标被引用) IN 下限..上限
sql 语句; --只允许一条 sql 语句
语法2：
FORALL 下标变量 IN INDICES OF(跳过没有赋值的元素，例如被 DELETE 的元素，NULL 也算值) 集合
[BETWEEN 下限 AND 上限]
sql 语句;
语法3：
FORALL 下标变量 IN VALUES OF 集合(把该集合中的值当作下标，且该集合值的类型只能是 PLS_INTEGER BINARY_INTEGER)
sql 语句;
- 语法演示（语法1演示）
#测试表
create table tb1
(
id number(5),
name varchar2(50)
);
----------------------------------------------
#批量插入演示
declare
type tb_table_type is table of tb1%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 tb1 values tb_table(i);
end;
----------------------------------------------
#批量修改演示
declare
type tb_table_type is table of tb1%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:='NAMES'||i;
end loop;
forall i in 1..tb_table.count
update tb1 t set row = tb_table(i) where t.id = tb_table(i).id;
end;
----------------------------------------------
#批量删除演示
declare
type tb_table_type is table of tb1%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:='NAMES'||i;
end loop;
forall i in 1..tb_table.count
delete tb1 where id = tb_table(i).id;
end;
- 语法演示（语法2演示）
--delete from tb1;
declare
type tb1_table_type is table of tb1%rowtype
index by binary_integer;
tb1_table tb1_table_type;
begin
for i in 1..10 loop
tb1_table(i).id:=i;
tb1_table(i).name:='NAME'||i;
end loop;
tb1_table.delete(3);
tb1_table.delete(6);
tb1_table.delete(9);
forall i in indices of tb1_table
insert into tb1 values tb1_table(i);
end;
---------------------------------------------
SQL> select * from tb1;
ID NAME
---------- ------------
1 NAME1
2 NAME2
4 NAME4
5 NAME5
7 NAME7
8 NAME8
10 NAME10
7 rows selected.
- 语法演示（语法3演示）
--delete from tb1;
declare
type index_poniter_type is table of pls_integer;
index_poniter index_poniter_type;
type tb1_table_type is table of tb1%rowtype
index by binary_integer;
tb1_table tb1_table_type;
begin
index_poniter:=index_poniter_type(1,3,5,7);
for i in 1..10 loop
tb1_table(i).id:=i;
tb1_table(i).name:='NAME'||i;
end loop;
forall i in values of index_poniter
insert into tb1 values tb1_table(i);
end;
-------------------------------------------------
SQL> select * from tb1;
ID NAME
---------- ---------
1 NAME1
3 NAME3
5 NAME5
7 NAME7
- 创建测试表
CREATE TABLE forall_test
(
id NUMBER(10),
code VARCHAR2(10),
description VARCHAR2(50)
);
ALTER TABLE forall_test ADD
(
CONSTRAINT forall_test_pk PRIMARY KEY (id)
);
ALTER TABLE forall_test ADD
(
CONSTRAINT forall_test_uk UNIQUE (code)
);
- for 和 for all 对比insert
DECLARE
TYPE t_forall_test_tab IS TABLE OF forall_test%ROWTYPE;
l_tab t_forall_test_tab := t_forall_test_tab();
l_start NUMBER;
l_size NUMBER := 10000;
BEGIN
-- Populate collection.
FOR i IN 1 .. l_size LOOP
l_tab.extend;
l_tab(l_tab.last).id := i;
l_tab(l_tab.last).code := TO_CHAR(i);
l_tab(l_tab.last).description := 'Description: ' || TO_CHAR(i);
END LOOP;
EXECUTE IMMEDIATE 'TRUNCATE TABLE forall_test';
-- Time regular inserts.
l_start := DBMS_UTILITY.get_time;
FOR i IN l_tab.first .. l_tab.last LOOP
INSERT INTO forall_test (id, code, description)
VALUES (l_tab(i).id, l_tab(i).code, l_tab(i).description);
END LOOP;
DBMS_OUTPUT.put_line('Normal Inserts: ' || (DBMS_UTILITY.get_time - l_start));
EXECUTE IMMEDIATE 'TRUNCATE TABLE forall_test';
-- Time bulk inserts.
l_start := DBMS_UTILITY.get_time;
FORALL i IN l_tab.first .. l_tab.last
INSERT INTO forall_test VALUES l_tab(i);
DBMS_OUTPUT.put_line('Bulk Inserts : ' || (DBMS_UTILITY.get_time - l_start));
COMMIT;
END;
-------------------------------------------------
Normal Inserts: 60 #即 600毫秒
Bulk Inserts : 7 #即 70毫秒
- for 和 for all 对比 update
DECLARE
----TYPE t_id_tab IS TABLE OF forall_test.id%TYPE;
TYPE t_forall_test_tab IS TABLE OF forall_test%ROWTYPE;
----l_id_tab t_id_tab := t_id_tab();
l_tab t_forall_test_tab := t_forall_test_tab ();
l_start NUMBER;
l_size NUMBER := 10000;
BEGIN
-- Populate collections.
FOR i IN 1 .. l_size LOOP
----l_id_tab.extend;
l_tab.extend;
----l_id_tab(l_id_tab.last) := i;
l_tab(l_tab.last).id := i;
l_tab(l_tab.last).code := TO_CHAR(i);
l_tab(l_tab.last).description := 'Description: ' || TO_CHAR(i);
END LOOP;
-- Time regular updates.
l_start := DBMS_UTILITY.get_time;
FOR i IN l_tab.first .. l_tab.last LOOP
UPDATE forall_test
SET ROW = l_tab(i)
WHERE id = l_tab(i).id;
END LOOP;
DBMS_OUTPUT.put_line('Normal Updates : ' || (DBMS_UTILITY.get_time - l_start));
l_start := DBMS_UTILITY.get_time;
-- Time bulk updates.
FORALL i IN l_tab.first .. l_tab.last
UPDATE forall_test
SET ROW = l_tab(i)
WHERE id = l_tab(i).id; ----l_id_tab(i);
DBMS_OUTPUT.put_line('Bulk Updates : ' || (DBMS_UTILITY.get_time - l_start));
COMMIT;
END;
-------------------------------------------------
Normal Updates : 31 #310毫秒
Bulk Updates : 7 #70毫秒
- for all 中 SQL%BULK_ROWCOUNT(i) 使用
#创建测试表
CREATE TABLE bulk_rowcount_test AS
SELECT *
FROM all_objects;
#查询测试数据
SQL> select OWNER,count(1)
from bulk_rowcount_test
where OWNER in ('ADMIN', 'SYS','SYSTEM', 'DBSNMP', 'BANANA')
group by OWNER;
-------------------------
OWNER COUNT(1)
ADMIN 94
SYSTEM 435
DBSNMP 55
SYS 47471
---------------------------------------------
DECLARE
TYPE t_array_tab IS TABLE OF VARCHAR2(30);
l_array t_array_tab := t_array_tab('ADMIN', 'SYS','SYSTEM', 'DBSNMP', 'BANANA');
BEGIN
-- Perform bulk delete operation.
FORALL i IN l_array.first .. l_array.last
DELETE FROM bulk_rowcount_test
WHERE OWNER = l_array(i);
dbms_output.put_line(sql%rowcount||'行记录被更新！');
-- Report affected rows.
FOR i IN l_array.first .. l_array.last LOOP
DBMS_OUTPUT.put_line('Element: ' || RPAD(l_array(i), 15, ' ') || ' Rows affected: ' || SQL%BULK_ROWCOUNT(i));
END LOOP;
DBMS_OUTPUT.put_line(' Rows affected: ' || SQL%ROWCOUNT);
END;
-------------------------
48055行记录被更新！
Element: ADMIN Rows affected: 94
Element: SYS Rows affected: 47471
Element: SYSTEM Rows affected: 435
Element: DBSNMP Rows affected: 55
Element: BANANA Rows affected: 0
Rows affected: 48055
- 删除的数据BULK COLLECT
CREATE TABLE emp_temp AS SELECT * FROM emp;
DECLARE
TYPE NumList IS TABLE OF NUMBER;
depts NumList := NumList(10,20,30);
TYPE empno_t IS TABLE OF emp_temp.empno%TYPE;
TYPE dept_t IS TABLE OF emp_temp.deptno%TYPE;
e_ids empno_t;
d_ids dept_t;
BEGIN
FORALL j IN depts.FIRST..depts.LAST
DELETE FROM emp_temp WHERE deptno = depts(j)
RETURNING empno, deptno BULK COLLECT INTO e_ids, d_ids;
DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows:');
FOR i IN e_ids.FIRST .. e_ids.LAST
LOOP
DBMS_OUTPUT.PUT_LINE('Employee #' || e_ids(i) || ' from dept #' || d_ids(i));
END LOOP;
END;
----------------------------------
Deleted 14 rows:
Employee #7782 from dept #10
Employee #7839 from dept #10
Employee #7934 from dept #10
Employee #7369 from dept #20
Employee #7566 from dept #20
Employee #7788 from dept #20
Employee #7876 from dept #20
Employee #7902 from dept #20
Employee #7499 from dept #30
Employee #7521 from dept #30
Employee #7654 from dept #30
Employee #7698 from dept #30
Employee #7844 from dept #30
Employee #7900 from dept #30
- SAVE EXCEPTIONS
CREATE TABLE exception_test
(
myid NUMBER(10) NOT NULL,
myname varchar2(20) not null
);
--------------------------------------------
DECLARE
TYPE t_tab IS TABLE OF exception_test%ROWTYPE;
l_tab t_tab := t_tab();
l_error_count NUMBER;
ex_dml_errors EXCEPTION;
PRAGMA EXCEPTION_INIT(ex_dml_errors, -24381);
l_errno number;
l_msg varchar2(4000);
l_idx number;
BEGIN
-- Fill the collection.
FOR i IN 1 .. 10 LOOP
l_tab.extend;
l_tab(l_tab.last).myid := i;
l_tab(l_tab.last).myname := 'name ' || i;
END LOOP;
-- Cause a failure.
l_tab(5).myname := NULL;
l_tab(7).myid := NULL;
EXECUTE IMMEDIATE 'TRUNCATE TABLE exception_test';
-- Perform a bulk operation.
BEGIN
FORALL i IN l_tab.first .. l_tab.last SAVE EXCEPTIONS
INSERT INTO exception_test VALUES l_tab(i);
EXCEPTION
WHEN ex_dml_errors THEN
l_error_count := SQL%BULK_EXCEPTIONS.count;
DBMS_OUTPUT.put_line('Number of failures: ' || l_error_count);
FOR i IN 1 .. l_error_count LOOP
l_errno := sql%bulk_exceptions(i).error_code;
l_msg := sqlerrm(-l_errno);
l_idx := sql%bulk_exceptions(i).error_index;
DBMS_OUTPUT.put_line('Error: ' || i
|| '--Array Index: ' || l_idx
|| '--Message: ' || l_msg
|| '--myid: ' || l_tab(l_idx).myid
|| '--myname: ' || l_tab(l_idx).myname
);
END LOOP;
END;
commit;
END;
--------------------------------------------
Number of failures: 2
Error: 1--Array Index: 5--Message: ORA-01400: 无法将 NULL 插入 ()--myid: 5--myname:
Error: 2--Array Index: 7--Message: ORA-01400: 无法将 NULL 插入 ()--myid: --myname: name 7
- 根据rowid分批量更新，可以极大的提高速率
create or replace noneditionable procedure prc_fwa_bill_info_01 is
maxrows number;
v_rowid dbms_sql.Urowid_Table; --定义rowid类型的数组
v_id_bill dbms_sql.Varchar2_Table; --定义字符串类型的数组
cursor cur_x is
select /*+ use_hash(t1,t2) parallel(t1,4) parallel(t2,4) */ --使用并发处理
t1.id_bill, t2.rowid
from fwa_bill_info t1, fwa_bill_info_detail t2
where t1.bill_no = t2.bill_no
and t2.standard_code is null
order by t2.rowid;
begin
maxrows := 5000; --定义数组长度
open cur_x;
loop
exit when cur_x%notfound;
fetch cur_x bulk collect
into v_id_bill, v_rowid limit maxrows;
forall i in 1 .. v_rowid.count
update fwa_bill_info_detail r
set r.id_bill = v_id_bill(i),r.standard_code = 1
where rowid = v_rowid(i);
commit;
end loop;
close cur_x;
end;
