在Oracle中使用FORALL更新和插入

Question

我是PL/SQL的新手。我有这样一个程序:

create or replace procedure insert_charge is
v_count       number;
begin

for i in (select t.name, t.hire_date, t.salary
from emp t
where t.create_date >= (sysdate - 30)
and t.salary = 0) loop

insert into charge
(name, hire_date, salary)
values
(i.name, hire_date, salary);
commit;

update emp l
set l.status = 1
where l.name = i.name
and l.status = 0
and l.hire_date = i.hire_date;
commit;
end loop;
exception
when others then
rollback;
end insert_charge;

如何使用FORALL语句来代替这个?

Answer 1

FORALL还有一些额外的任务;即定义一个集合来定义大容量区域，并定义该集合类型的变量来包含实际数据。作为一个安全值，您应该对一次获取的数目设置一个限制。Bulk Collect/Forall是速度与内存之间的一种权衡。在某一点(取决于你的配置)收益递减。除了您使用的内存之外，数据库中的其他进程无法使用该内存。好好对待你的同伴。然后，正如@Littlefoot指出的那样，不要压制异常，记录它们并重新引发。最后，关于提交的说明。* *不提交每个DML语句之后,你可以花一些时间调查[交易][1]。考虑到这一点，您的过程变得类似于:

create or replace procedure insert_charge is
cursor c_emp_cur is 
select t.name, t.hire_date, t.salary
from emp t
where t.create_date >= (sysdate - 30)
and t.salary = 0; 

type c_emp_array_t is table of c_emp%rowtype ;  -- define collection for rows selected

k_emp_rows_max constant integer := 500;         -- defines the maximum rows per fetch
l_emp_list     c_emp_array_t;                   -- define variable of rows collection 
begin
open c_emp_cur; 

loop 
fetch c_emp_cur                              -- fetch up to LIMIT rows from cursor
bulk collect 
into l_emp_collect
limit k_emp_rows_max; 

forall i in 1 .. l_emp_collect.count         -- run insert for ALL rows in the collection
insert into charge(name, hire_date, salary)
values( l_emp_collect(i).name 
, l_emp_collect(i).hire_date
, l_emp_collect(i).salary);

forall i in 1 .. l_emp_collect.count        -- run update for ALL rows in the collection          
update emp l
set l.status = 1
where l.name = l_emp_collect(i).name
and l.status = 0
and l.hire_date = l_emp_collect(i).hire_date;

exit when c_emp_cur%notfound;                     -- no more rows so exit
end loop;

close c_emp_cur; 
commit;                                          -- JUST 1 COMMIT; 
exception
when others then
generate_exception_log ('insert_charge', sysdate, sql_errm );    --ASSUMED Anonomous Transaction procedure for exception log table. 
raise;
end insert_charge;      

免责声明:不测试。[1]: https://www.techopedia.com/definition/16455/transaction-databases

Answer 2

你不能。

FORALL语句运行一次DML语句多次出现

一个>DML语句。您有两个(更新和插入)。

---

你写的代码:

• 将COMMIT移出循环
• 删除when others"处理器"因为它什么都做不了。如果发生错误，Oracle将静默回滚并报告过程成功完成，而实际上是失败的
。