优化PL/SQL过程调用,使用NOCOPY提示
来源:互联网 发布:安卓5.0 自动接听源码 编辑:程序博客网 时间:2024/06/17 21:56
使用NOCOPY提示可以将过程的IN/IN OUT类型的参数从by value改为by reference
Tuning PL/SQL Procedure Calls with the NOCOPY Compiler Hint
By default, OUT
and IN
OUT
parameters are passed by value. The values of any IN OUT
parameters are copied before the subprogram is executed. During subprogram execution, temporary variables hold the output parameter values. If the subprogram exits normally, these values are copied to the actual parameters. If the subprogram exits with an unhandled exception, the original parameters are unchanged.
When the parameters represent large data structures such as collections, records, and instances of object types, this copying slows down execution and uses up memory. In particular, this overhead applies to each call to an object method: temporary copies are made of all the attributes, so that any changes made by the method are only applied if the method exits normally.
To avoid this overhead, you can specify the NOCOPY
hint, which allows the PL/SQL compiler to pass OUT
and IN
OUT
parameters by reference. If the subprogram exits normally, the behavior is the same as normal. 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.
The following example asks the compiler to pass IN
OUT
parameter v_staff
by reference, to avoid copying the varray on entry to and exit from the subprogram:
DECLARE
TYPE Staff IS VARRAY(200) OF Employee;
PROCEDURE reorganize (v_staff IN OUT NOCOPY Staff) IS ...
Example 11-17 loads 25,000 records into a local nested table, which is passed to two local procedures that do nothing. A call to the procedure that uses NOCOPY
takes much less time.
Example 11-17 Using NOCOPY With Parameters
DECLARE TYPE EmpTabTyp IS TABLE OF employees%ROWTYPE; emp_tab EmpTabTyp := EmpTabTyp(NULL); -- initialize t1 NUMBER; t2 NUMBER; t3 NUMBER; PROCEDURE get_time (t OUT NUMBER) IS BEGIN t := DBMS_UTILITY.get_time; END; PROCEDURE do_nothing1 (tab IN OUT EmpTabTyp) IS BEGIN NULL; END; PROCEDURE do_nothing2 (tab IN OUT NOCOPY EmpTabTyp) IS BEGIN NULL; END;BEGIN SELECT * INTO emp_tab(1) FROM employees WHERE employee_id = 100; emp_tab.EXTEND(49999, 1); -- copy element 1 into 2..50000 get_time(t1); do_nothing1(emp_tab); -- pass IN OUT parameter get_time(t2); do_nothing2(emp_tab); -- pass IN OUT NOCOPY parameter get_time(t3); DBMS_OUTPUT.PUT_LINE('Call Duration (secs)'); DBMS_OUTPUT.PUT_LINE('--------------------'); DBMS_OUTPUT.PUT_LINE('Just IN OUT: ' || TO_CHAR((t2 - t1)/100.0)); DBMS_OUTPUT.PUT_LINE('With NOCOPY: ' || TO_CHAR((t3 - t2))/100.0);END;/
Restrictions on NOCOPY
The use of NOCOPY
increases the likelihood of parameter aliasing. For more information, see "Understanding Subprogram Parameter Aliasing".
Remember, NOCOPY
is a hint, not a directive. In the following cases, the PL/SQL compiler ignores the NOCOPY
hint and uses the by-value parameter-passing method; no error is generated:
-
The actual parameter is an element of an associative array. This restriction does not apply if the parameter is an entire associative array.
-
The actual parameter is constrained, such as by scale or
NOT
NULL
. This restriction does not apply to size-constrained character strings. This restriction does not extend to constrained elements or attributes of composite types. -
The actual and formal parameters are records, one or both records were declared using
%ROWTYPE
or%TYPE
, and constraints on corresponding fields in the records differ. -
The actual and formal parameters are records, the actual parameter was declared (implicitly) as the index of a cursor
FOR
loop, and constraints on corresponding fields in the records differ. -
Passing the actual parameter requires an implicit datatype conversion.
-
The subprogram is called through a database link or as an external procedure.
- 优化PL/SQL过程调用,使用NOCOPY提示
- 使用 Oracle PL/SQL NOCOPY 提示
- PL/SQL Nocopy
- Oracle PL/SQL之IN OUT NOCOPY
- 【PL/SQL】用SQL调用存储过程
- jdbc调用pl/sql存储过程
- pl/sql developer调用存储过程
- [oracle]pl/sql in/out变量的使用和过程中过程的调用
- [oracle]pl/sql in/out变量的使用和过程中过程的调用
- PL/SQL --> 动态SQL调用包中函数或过程
- pl/sql创建并使用存储过程
- pl/sql 有返回值的存储过程 java调用pl/sql过程 实例
- 使用pl/sql来调用java
- SQL优化:使用查询提示
- NOCOPY之使用限制
- Oracle NOCOPY使用
- Oracle PL/SQL如何动态调用存储过程
- Oracle PL/SQL如何动态调用存储过程 收藏
- 你心中的幸福是什么
- J2ME程序开发新手入门九大要点
- 使用OPENGL显示中文的类
- 查看表的高水位标志的方法
- asp中使用流方式下载文件的代码
- 优化PL/SQL过程调用,使用NOCOPY提示
- .NET Tools...每个.NET 开发人员应该下载的十个必备工具
- ListView排序问题
- 使asp页面不缓存
- 打开,另存为,属性,打印"等14个JS代码
- Ibatisnet Quick Start
- 支持中文的把普通字符串转成二进制字符串
- 瑞风虚拟摄像头1.40版 Build 0503发布!
- 支持中英文字符的字符串截取函数