Oracle PL/SQL之IN OUT NOCOPY

Suppose a subprogram declares an IN parameter, an OUT parameter, and an IN OUT parameter. When you call the subprogram, the IN parameter is passed by reference. That is, a pointer to the IN actual parameter is passed to the corresponding formal parameter. So, both parameters reference the same memory location, which holds the value of the actual parameter.

By default, the OUT and IN OUT parameters are passed by value. That is, the value of the IN OUT actual parameter is copied into the corresponding formal parameter. Then, if the subprogram exits normally, the values assigned to the OUT and IN OUT formal parameters are copied into the corresponding actual parameters.

 

當我們聲明一個參數是IN類型時，進行傳參是將傳給該參數一個實參的指標；

當我們聲明一個參數是OUT或者IN OUT類型時，進行傳參是將傳給該參數一個實參的拷貝；

只有當程式正常結束時，賦給OUT或者IN OUT型別參數的值才會返回（除非使用了NOCOPY）。

將NOCOPY應用在傳遞資料量很大的參數（such as collections, records, and instances of object types）時，可起到最佳化效能的作用。

當參數是OUT或者IN OUT類型時：沒有NOCOPY＝按值傳遞（ByVal）；加上NOCOPY＝按引用傳遞（ByRef）。

 

Test Code:

 

DECLARE<br /> l_1 NUMBER := 10;<br /> l_2 NUMBER := 20;<br /> l_3 NUMBER := 30;<br /> PROCEDURE test_out<br /> (<br /> p1 IN NUMBER<br /> ,x1 IN OUT NUMBER<br /> ,x2 IN OUT NOCOPY NUMBER<br /> ) IS<br /> BEGIN<br /> x1 := p1;<br /> dbms_output.put_line('inside test_out, x1=' || x1);<br /> x2 := p1;<br /> dbms_output.put_line('inside test_out, x2=' || x2);<br /> raise_application_error(-20001, 'test NOCOPY');<br /> END;<br />BEGIN<br /> dbms_output.put_line('before, l_1=' || l_1 || ', l_2=' || l_2 ||<br /> ', l_3=' || l_3);<br /> BEGIN<br /> --the OUT parameter has no value at all until the program terminates successfully,<br /> --unless you have requested use of the NOCOPY hint<br /> test_out(l_1, l_2, l_3);<br /> EXCEPTION<br /> WHEN OTHERS THEN<br /> dbms_output.put_line('SQLCODE => ' || SQLCODE || ', SQLERRM => ' ||<br /> SQLERRM);<br /> END;<br /> dbms_output.put_line('after, l_1=' || l_1 || ', l_2=' || l_2 || ', l_3=' || l_3);<br />END;<br />

 

Output:

 

before, l_1=10, l_2=20, l_3=30<br />inside test_out, x1=10<br />inside test_out, x2=10<br />SQLCODE => -20001, SQLERRM => ORA-20001: test NOCOPY<br />after, l_1=10, l_2=20, l_3=10<br />

 

Ref:

http://download.oracle.com/docs/cd/B10500_01/appdev.920/a96624/08_subs.htm#12813