oracle之nocopy关键词

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;
/

使用了nocopy之后，如果在被调用过程中没有很好的异常处理机制，会导致out参数返回的值产生不确定性，反而会影响程序结果的正确性。所以有些场景还是不使用nocopy为好。



输出:
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