我有一个表作为empl,记录作为
EMPID SDATE SAL DESIGNATION
=======================================
1001 21-SEP-17 55000 Technology Analyst
1002 22-SEP-19 52000 Technology Analyst
1003 15-SEP-17 65000 Technology Lead
1004 21-SEP-15 72000 Technology Lead
1005 11-MAR-20 55000 SSE
1006 22-JAN-20 55000 SSE
我们需要将所有员工的startDate更新为下一个Date(作为sysdate或1周后(,在PLSQL中按批次(按指定(,同时考虑性能。同时更新->在一些任何表(emp_log(中记录更新状态如下的记录。如果特定员工的更新成功,则状态将为Success&如果某些员工的更新失败,则状态为失败。
empid Update_Status
=====================
1001 Success
1002 Failed
1003 Success
.....
用empid获取这种状态的PL/SQL块语句是什么?
如果这是您只更新日期的真实用例,那么在您对其进行任何此类约束或任何触发器验证之前,我看不到会发生任何错误。
然而,如果这只是一个例子,并且您有更大的逻辑,那么我会在下面的代码中提供我方的注释,这可能会让您看到继续操作的前景。
因为您谈论性能,所以我会使用bulk collect
和forall
这样的集合,limit
使用save exceptions
捕获DML错误
declare
-- cursor to fetch the data which we want to update
-- additional wherr clauses can be added as per need
cursor cur_emp
is
select *
from emp;
-- rowtype variablr to hold the record
-- we can also use emp%rowtype
type emp_tab is table of cur_emp%rowtype;
-- local variable for the table type above
l_emp_data emp_tab;
-- user defined exception to catch bulk exception error
dml_errors exception;
-- actual ORA error number map to dml_errors
pragma exception_init(dml_errors, -24381);
-- othet local variables for convenience purpose
l_errors number;
l_errno number;
l_msg varchar2(4000);
l_idx number;
begin
open cur_emp;
loop
-- using limit as 100 which is recommended when we use bulk collect
fetch cur_emp bulk collect
into l_emp_data limit 100;
begin
-- updating 100 records at one shot
-- saving exception per record in case of failure
forall i in 1 .. l_emp_data.count
save exceptions
update emp
set sdate = sysdate
where empid = l_emp_data(i).empid;
-- we can insert all records as successful intially
-- which further in the exception section will be updated in case of any dml error
forall i in 1 .. l_emp_data.count
insert into emp_log
values(l_emp_data(i).empid,'Success',null);
exception
-- handling the user defined exception
-- and updating erroneous records
when dml_errors then
l_errors := sql%bulk_exceptions.count;
for i in 1 .. l_errors
loop
l_errno := sql%bulk_exceptions(i).error_code;
l_msg := sqlerrm(-l_errno);
l_idx := sql%bulk_exceptions(i).error_index;
-- i added additional error message as well
update emp_log
set status = 'Failed'
,err_msg = l_errno||'-'||l_msg
where empid = l_emp_data(l_idx).empid;
end loop;
end;
exit when cur_emp%notfound;
end loop;
close cur_emp;
end;
/
然而,我觉得如果你只想批量更新每个designation
的日期,那么一个简单的更新就足够了,因为每个员工都是唯一标识的,即使你每个职位有10000条记录,我也看不到任何绩效问题。(如果你这么认为的话,你可以试试(
最后,我还想介绍一下如何利用dml error logging
,它从10g开始就可用,您可以在这里找到。您将有一个系统生成的表,该表将记录DML操作期间的所有错误记录,您可以根据需要创建一个视图来查看成功或失败。
我还提供db<gt;fiddle供您参考,它具有上述代码的执行结果。我没有模拟错误部分,我把它留给你。