Using Oracle PL/SQL NOCOPY Hint

参考文献：

official document: http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/tuning.htm#LNPLS01208

oracle nocopy hint tips: http://www.dba-oracle.com/plsql/t_plsql_nocopy_hint.htm

http://www.dba-oracle.com/t_pl_sql_nocopy_data_structures.htm

一 概念介绍：

PLSQL运行引擎在存储过程和函数之间传参有2种方法：传值和传引用

默认情况，OUT和IN OUT参数通过传值方式传递，IN参数是传引用方式。程序执行前IN OUT参数的实际值将被复制给形参。程序执行中间，临时变量保存参数的输出值。如果程序正常退出，这些值将被复制回原参数。如果程序异常退出，那么原参数值将不会改变。当IN OUT参数为大数据结构，诸如：集合、记录、对象类型实例时，通过传值方式的COPY动作将会导致程序执行速度下降，使用内存量上升。尤其是该程序被多次调用时更是如此。

为避免此类情况，我们可以指定NOCOPY暗示，告诉PLSQL编译器通过传引用方式传递OUT 和IN OUT参数。这样，形参将不会COPY参数实际值，也就是说形参和ACTUAL VALUE指向同一内存地址（memory location）。以此提高程序执行性能。

下面通过一段脚本对比一下，传值和传引用在程序执行性能上的差异：

二 示例对比：

nocopy.sql

SET SERVEROUTPUT ON
DECLARE
   TYPE     t_tab IS TABLE OF VARCHAR2(32767);
   l_tab    t_tab := t_tab();
   l_start  NUMBER;

   PROCEDURE in_out (p_tab  IN OUT  t_tab) IS
   BEGIN
     NULL;
   END;  

   PROCEDURE in_out_nocopy (p_tab  IN OUT NOCOPY  t_tab) IS
   BEGIN
     NULL;
   END;
BEGIN
   l_tab.extend;
   l_tab(1) := '1234567890123456789012345678901234567890';
   l_tab.extend(999999, 1);  -- Copy element 1 into 2..1000000


   -- Time normal IN OUT
   l_start := DBMS_UTILITY.get_time;

   in_out(l_tab);

   DBMS_OUTPUT.put_line('IN OUT       : ' || 
                        (DBMS_UTILITY.get_time - l_start));

   -- Time IN OUT NOCOPY
   l_start := DBMS_UTILITY.get_time;

   in_out_nocopy(l_tab);  -- pass IN OUT NOCOPY parameter

   DBMS_OUTPUT.put_line('IN OUT NOCOPY: ' || 
                        (DBMS_UTILITY.get_time - l_start));
END;
/

SQL> @nocopy.sql

IN OUT :43
IN OUT NOCOPY: 0

PL/SQL procedure successfully completed.

三 使用NOCOPY限制：

1、如果实参为整个关联数组则该限制不起作用。
2、实参被强制指定精度，比例或not null时，该限制将不适用按最大长度强制的字符串参数。
3、实参和形参都是记录类型，二者存在以隐式方式或使用了%ROWTYPE类型声明时，作用在对应字段的强制说明不一制。
4、传递实参需要隐式类型转换时。
5、子程序涉及到远程调用（RPC---database link or as an external procedure）。

-------------------

Dylan presents.