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 .相关阅读：Oracle PL/SQL之自定义函数的读一致性 http://www.linuxidc.com/Linux/2011-07/39199.htm当我们声明一个参数是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
l_1 NUMBER := 10;
l_2 NUMBER := 20;
l_3 NUMBER := 30;
PROCEDURE test_out
（
p1 IN NUMBER
,x1 IN OUT NUMBER
,x2 IN OUT NOCOPY NUMBER
） IS
BEGIN
x1 := p1;
dbms_output.put_line（"inside test_out, x1=" || x1）;
x2 := p1;
dbms_output.put_line（"inside test_out, x2=" || x2）;
raise_application_error（-20001, "test NOCOPY"）;
END;
BEGIN
dbms_output.put_line（"before, l_1=" || l_1 || ", l_2=" || l_2 ||
", l_3=" || l_3）;
BEGIN
--the OUT parameter has no value at all until the program terminates successfully,
--unless you have requested use of the NOCOPY hint
test_out（l_1, l_2, l_3）;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line（"SQLCODE => " || SQLCODE || ", SQLERRM => " ||
SQLERRM）;
END;
dbms_output.put_line（"after, l_1=" || l_1 || ", l_2=" || l_2 || ", l_3=" || l_3）;
END;

Output:

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

Ref:http://download.oracle.com/docs/cd/B10500_01/appdev.920/a96624/08_subs.htm#12813 Oracle PL/SQL之GROUP BY GROUPING SETS简单介绍一下Oracle数据库的三个进程相关资讯 Oracle教程

Oracle中纯数字的varchar2类型和（07/29/2015 07:20:43）
Oracle教程：Oracle中查看DBLink密（07/29/2015 07:16:55）
[Oracle] SQL*Loader 详细使用教程（08/11/2013 21:30:36）

Oracle教程：Oracle中kill死锁进程（07/29/2015 07:18:28）
Oracle教程：ORA-25153 临时表空间（07/29/2015 07:13:37）
Oracle教程之管理安全和资源（04/08/2013 11:39:32）

本文评论查看全部评论（0）