两个匿名块唯一的区别是:第一个没有使用NOCOPY hint,第二个使用了:

SQL> set serverout on;
SQL> declare
2 lv_int   PLS_INTEGER := 1;
3 procedure p_test(p_int IN OUT PLS_INTEGER)
4 is
5    exp exception;
6 begin
7   p_int := p_int +2;
8   raise exp;
9 exception
10    when others then
11     dbms_output.put_line('1:'||p_int);
12     raise;
13 end p_test;
14 begin
15   p_test(lv_int);
16 exception
17    when others then
18      dbms_output.put_line('2:'||lv_int);
19 end;
20 /
1:3
2:1

PL/SQL procedure successfully completed.

SQL>
SQL> declare
2 lv_int   PLS_INTEGER := 1;
3 procedure p_test(p_int IN OUT NOCOPY PLS_INTEGER)
4 is
5     exp exception;
6 begin
7   p_int := p_int +2;
8   raise exp;
9 exception
10    when others then
11     dbms_output.put_line('1:'||p_int);
12     raise;
13 end p_test;
14 begin
15   p_test(lv_int);
16 exception
17    when others then
18      dbms_output.put_line('2:'||lv_int);
19 end;
20 /
1:3
2:3

PL/SQL procedure successfully completed.

对于unhandled exception，OUT/IN OUT的形参的值不会传递给实参,所以第一个匿名块第二步输出初始值1, 但是如果有NOCOPY hint,OUT/IN OUT的形参的值会传递给实参,也就是在这种情况下是会影响实参的值的,第二个匿名块输出更新后的值3;

ITPub博客

unhandled exception with NOCOPY

最新文章