关闭

使用 Oracle PL/SQL NOCOPY 提示

标签: oracleplsqlnocopy
2377人阅读 评论(0) 收藏 举报
分类:

参考文献:

      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 oras an external procedure)。


四 关于使用NOCOPY过程中的异常处理

    虽说NOCOPY带来了性能上的优势,但是不容忽略的一点是当我们调用的子程序(带有NOCOPY提示)发生异常时,一旦处理不当很容易造成实参被修改。这是我们不愿看到的。下面引用官方的一段话并结合newkid大师的例子给予说明,详细请见ITPUB版:

   http://www.itpub.net/thread-1147041-1-1.html

   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 p_nocopy_test(p_id OUT NOCOPY NUMBER)
AS
BEGIN
    p_id := 1;
    raise_application_error(-20201,'NULL');  --程序终止
    p_id := 2;
    
    RETURN;
END;
/


DECLARE
    v_id NUMBER := 0;
BEGIN
    dbms_output.put_line('Before call p_nocopy_test: '||v_id);
    p_nocopy_test(v_id);
EXCEPTION 
    WHEN OTHERS THEN
       dbms_output.put_line('After call p_nocopy_test: '||v_id);  
    
END;
/

Before call p_nocopy_test: 0
After call p_nocopy_test: 1


再来看一下去掉NOCOPY参数的子程序调用在遇到异常时的表现:

Before call p_nocopy_test: 0
After call p_nocopy_test: 0


那么针对NOCOPY子程序的异常处理该如何做?Oracle前面说了应该在子程序内部捕捉处理掉异常,例如:

CREATE OR REPLACE PROCEDURE p_nocopy_test(p_id OUT NOCOPY NUMBER)
AS
BEGIN
    p_id := 1;
    raise_application_error(-20201,'NULL');  --程序终止
    p_id := 2;
    
    --RETURN;
EXCEPTION 
   WHEN OTHERS THEN
    p_id := 0;
    RETURN;
END;
/

DECLARE
    v_id NUMBER := 0;
BEGIN
    dbms_output.put_line('Before call p_nocopy_test: '||v_id);
    p_nocopy_test(v_id);
/*
EXCEPTION 
    WHEN OTHERS THEN
*/
    dbms_output.put_line('After call p_nocopy_test: '||v_id);  
    
END;
/

Before call p_nocopy_test: 0
After call p_nocopy_test: 0



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

Dylan presents.


  

1
0
查看评论

Oracle 子程序参数模式,IN,OUT,NOCOPY

Oracle 子程序参数模式主要有IN,OUT,NOCOPY,IN和OUT可以组合,OUT和NOCOPY也可以组合使用. IN主要用于传入参数,可以是变量,常量,表达式,在子程序内部不能改变其值.   DECLARE n NUMBER := 10; PROCEDURE do_so...
  • kkdelta
  • kkdelta
  • 2009-10-19 14:39
  • 5316

NOCOPY

说明:将NOCOPY应用在传递数据量很大的参数 (such as collections, records, andinstancesofobjecttypes)时,可起到优化性能的作用。当参数是OUT或者IN OUT类型时:没有NOCOPY=按值传递(ByVal);加上NOCOPY=按...
  • wanpiyufeng
  • wanpiyufeng
  • 2014-03-15 22:41
  • 559

oracle NOCOPY 用法

PL/SQL中对out,in out参数使用的?默认形参会复制一份实参的副本,然后在内部传递,修改等,发生异常,不会赋值给实参,控制权交还调用环境,而实参值不变,还是调用前的值。 而使用了NOCOPY后,形参将获得一个指向实参的指针,然后在内部传递,赋值都直接修改实参了,此时如果异常发生,控制权交...
  • u011287994
  • u011287994
  • 2014-01-15 14:41
  • 839

Oracle NOCOPY使用

PLSQL运行引擎在存储过程和函数之间传参有2种方法:传值和传引用       默认情况,OUT和IN OUT参数通过传值方式传递,IN参数是传引用方式。程序执行前IN OUT参数的实际值将被复制给形参。程序执行中间,临时变量保存参数的输出值。如果程序正常退出,这些...
  • pursuer211
  • pursuer211
  • 2014-01-11 15:18
  • 540

使用 Oracle PL/SQL NOCOPY 提示

参考文献:       official document: http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/tuning.htm#LNPLS01208   &...
  • IndexMan
  • IndexMan
  • 2013-12-03 14:21
  • 2377

IN OUT NOCOPY 分析

当我们声明一个参数是IN类型时,进行传参是将传给该参数一个实参的指针; 当我们声明一个参数是OUT或者IN OUT类型时,进行传参是将传给该参数一个实参的拷贝; 只有当程序正常结束时,赋给OUT或者IN OUT类型参数的值才会返回(除非使用了NOCOPY)。 将NOCOPY应用在传递数据量很大...
  • SMJ3901080114
  • SMJ3901080114
  • 2013-08-27 16:50
  • 1019

oracle pl sql NO_DATA_FOUND

先创建表: CREATE TABLE TB_USER ( ID INTEGER PRIMARY KEY, USER_NAME VARCHAR2(20) NOT NULL, USER_AGE INTEGER NOT NULL ); CREATE SEQUENCE SEQ_U...
  • feier7501
  • feier7501
  • 2014-03-09 22:33
  • 1356

Oracle 系统包

Oracle 系统包
  • bbliutao
  • bbliutao
  • 2013-08-28 17:37
  • 4972

Oracle 常用系统包(转)

Oracle 系统包 Oracle 10g提供的系统包多达几百个,此处只介绍一些常用的系统包。 一、DBMS_OUTPUT 1、概述 作用:用于输入和输出信息,使用过程PUT和PUT_LINES可以将信息发送到缓冲区,使用过程GET_LINE和GET_LINES可以显示缓冲区信息...
  • suixufeng
  • suixufeng
  • 2016-03-04 17:20
  • 827

ORACLE11g关于用户密码过期问题的解决

oracle11g,会遇到sqlplus登陆oracle数据库时提示“口令将过期 ”等情况。 解决方案: 按照如下步骤进行操作: 1、查看用户proifle,一般是default: sql>Select username,PROFILE FROM dba_users; ...
  • qin_zhimou
  • qin_zhimou
  • 2016-09-06 11:00
  • 592
    个人资料
    • 访问:1457428次
    • 积分:10740
    • 等级:
    • 排名:第1818名
    • 原创:181篇
    • 转载:49篇
    • 译文:22篇
    • 评论:113条
    博客专栏
    最新评论
    My Favorite Music