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 OUTactual 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.

When the parameters hold large data structures such as collections, records, and instances of object types, all this copying slows down execution and uses up memory. To prevent that, you can specify the NOCOPY hint, which allows the PL/SQL compiler to pass OUT and IN OUT parameters by reference.

当我们声明一个参数是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）。

In the example below, 25000 records are loaded into a local nested table, which is passed to two local procedures that do nothing but execute NULL statements. However, a call to one procedure takes 21 seconds because of all the copying. With NOCOPY, a call to the other procedure takes much less than 1 second.

SQL> SET SERVEROUTPUT ON
SQL> GET test.sql
  1  DECLARE
  2     TYPE EmpTabTyp IS TABLE OF emp%ROWTYPE;
  3     emp_tab EmpTabTyp := EmpTabTyp(NULL);  -- initialize
  4     t1 NUMBER(5);
  5     t2 NUMBER(5);
  6     t3 NUMBER(5);
  7     PROCEDURE get_time (t OUT NUMBER) IS
  8     BEGIN SELECT TO_CHAR(SYSDATE,'SSSSS') INTO t FROM dual; END;
  9     PROCEDURE do_nothing1 (tab IN OUT EmpTabTyp) IS
 10     BEGIN NULL; END;
 11     PROCEDURE do_nothing2 (tab IN OUT NOCOPY EmpTabTyp) IS
 12     BEGIN NULL; END;
 13  BEGIN
 14     SELECT * INTO emp_tab(1) FROM emp WHERE empno = 7788;
 15     emp_tab.EXTEND(24999, 1);  -- copy element 1 into 2..25000
 16     get_time(t1);
 17     do_nothing1(emp_tab);  -- pass IN OUT parameter
 18     get_time(t2);
 19     do_nothing2(emp_tab);  -- pass IN OUT NOCOPY parameter
 20     get_time(t3);
 21     dbms_output.put_line('Call Duration (secs)');
 22     dbms_output.put_line('--------------------');
 23     dbms_output.put_line('Just IN OUT: ' || TO_CHAR(t2 - t1));
 24     dbms_output.put_line('With NOCOPY: ' || TO_CHAR(t3 - t2));
 25* END;
SQL> /
Call Duration (secs)
--------------------
Just IN OUT: 21
With NOCOPY: 0

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