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
