关于NOCOPY HINT

使用NOCOPY提示可以将过程的IN/IN OUT类型的参数从by value改为by reference
Tuning PL/SQL Procedure Calls with the NOCOPY Compiler Hint
By default, OUT and IN OUT parameters are passed by value. The values of any IN OUT parameters are copied before the subprogram is executed. During subprogram execution, temporary variables hold the output parameter values. If the subprogram exits normally, these values are copied to the actual parameters. If the subprogram exits with an unhandled exception, the original parameters are unchanged.

When the parameters represent large data structures such as collections, records, and instances of object types, this copying slows down execution and uses up memory. In particular, this overhead applies to each call to an object method: temporary copies are made of all the attributes, so that any changes made by the method are only applied if the method exits normally.

To avoid this overhead, you can specify the NOCOPY hint, which allows the PL/SQL compiler to pass OUT and IN OUT parameters by reference. If the subprogram exits normally, the behavior is the same as normal. If the subprogram exits early with an exception, the values of OUT and IN OUT parameters (or object attributes) might still change. To use this technique, ensure that the subprogram handles all exceptions.

谁能写段代码证实一下如下这段话：

If the subprogram exits early with an exception, the values of OUT and IN OUT parameters (or object attributes) might still change. To use this technique, ensure that the subprogram handles all exceptions.

CREATE OR REPLACE PROCEDURE SP_TEST (P_OUT OUT NOCOPY NUMBER)
AS
BEGIN
   P_OUT :=1;
   RAISE_APPLICATION_ERROR(-20001,'ERROR');
   P_OUT :=2;
   RETURN;
END;
/


DECLARE
   V_OUT NUMBER :=0;
BEGIN
   DBMS_OUTPUT.PUT_LINE('BEFORE CALLING SP_TEST: V_OUT = '||V_OUT);
   SP_TEST(V_OUT);
EXCEPTION
   WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('AFTER CALLING SP_TEST: V_OUT = '||V_OUT);
END;
/

输出:
BEFORE CALLING SP_TEST: V_OUT = 0
AFTER CALLING SP_TEST: V_OUT = 1

去掉NOCOPY的输出：
BEFORE CALLING SP_TEST: V_OUT = 0
AFTER CALLING SP_TEST: V_OUT = 0

谢谢，明白了！我以前是在存储过程中捕获异常了，应该在调用时捕获异常，我以前这样写的，用不用nocopy结果都一样：

CREATE OR REPLACE PROCEDURE test_proc (p_out IN OUT NOCOPY VARCHAR2)
IS
my_EXCEPTION EXCEPTION;
BEGIN
p_out:='111';
dbms_output.put_line('1:'||p_out);
SELECT region_id INTO p_out  FROM region_host WHERE ROWNUM=1;
RAISE my_EXCEPTION;
EXCEPTION
WHEN my_EXCEPTION THEN
   dbms_output.put_line('2:'||p_out);
END test_proc;

调用：

  declare
        l_invar       VARCHAR2(10);
  begin
       test_proc( l_invar);
       dbms_output.put_line(l_invar);
  end;

pl/sql中对out，in out参数使用的？默认形参会复制一份实参的副本，然后在内部传递，修改等，发生异常，不会赋值给实参，控制权交还调用环境，而实参值不变，还是调用前的值。而使用了nocopy后，形参将获得一个指向实参的指针，然后在内部传递，赋值都直接修改实参了，此时如果异常发生，控制权交还调用环境，但是实参已经被修改了。无法还原成调用前的值。

对参数I N使用nocopy将会产生编译错误，这是因为参数I N总是按引用传递.
NOCOPY的主要优点是可以提高程序的效率。当我们传递大型pl/sql表时，其优越性特别显著.使用NOCOPY的限制在某些情况下，NOCOPY将被编译器忽略，这时的参数仍将按值传递。这时，编译器不会报告编译错误。由于NOCOPY是一个提示项(Hint),编译器可以决定是否执行该项。在下列情况下，编译器将忽略NOCOPY项：
1.实参是索引表(index-by table)的成员时。如果该实参是全表，则该限制不起作用。
2.实参被强制指定精度，比例或NOT NULL时。该限制将不适用按最大长度强制的字符串参数。
3.实参和形参都是记录类型，二者是以隐含方式或使用了%ROWTYPE类型声明时，作用在对应字段的强制说明不一致。
4.传递实参需要隐式类型转换时。
5.子程序涉及到远程过程调用(PRC)。远程过程调用就是跨越数据库对远程服务器的过程调用。