NOCOPY参数的使用.doc

Passing Large Data Structures with the NOCOPY Compiler HintSuppose 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.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 the following example, you ask the compiler to pass IN OUT parameter my_staff by reference instead of by value:DECLARE TYPE Staff IS VARRAY(200) OF Employee; PROCEDURE reorganize (my_staff IN OUT NOCOPY Staff) IS .Remember, NOCOPY is a hint, not a directive. So, the compiler might pass my_staff by value despite your request. Usually, however, NOCOPY succeeds. So, it can benefit any PL/SQL application that passes around large data structures.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 ONSQL 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: 21With NOCOPY: 0DECLARE TYPE EmpTabTyp IS TABLE OF emp%ROWTYPE; emp_tab EmpTabTyp := EmpTabTyp(NULL); - initialize t1 NUMBER(5); t2 NUMBER(5); t3 NUMBER(5); PROCEDURE get_time (t OUT NUMBER) IS BEGIN SELECT TO_CHAR(SYSDATE,SSSSS) INTO t FROM dual; 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 emp WHERE empno = 7788; emp_tab.EXTEND(249999, 1); - copy element 1 into 2.250000 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); dbms_output.put_line(With NOCOPY: | TO_CHAR(t3 - t2);END;/Call Duration (secs) -250000条记录，即25万条记录- Just IN OUT: 2With NOCOPY: 0PL/SQL 过程已成功完成。Call Duration (secs) -2500000条记录，即250万条记录-Just IN OUT: 381With NOCOPY: 0PL/SQL 过程已成功完成。The Trade-Off for Better Performance with NOCOPYNOCOPY lets you trade well-defined exception semantics for better performance. Its use affects exception handling in the following ways: Because NOCOPY is a hint, not a directive, the compiler can pass NOCOPY parameters to a subprogram by value or by reference. So, if the subprogram exits with an unhandled exception, you cannot rely on the values of the NOCOPY actual parameters. By default, if a subprogram exits with an unhandled exception, the values assigned to its OUT and IN OUT formal parameters are not copied into the corresponding actual parameters, and changes appear to roll back. However, when you specify NOCOPY, assignments to the formal parameters immediately affect the actual parameters as well. So, if the subprogram exits with an unhandled exception, the (possibly unfinished) changes are not rolled back. Currently, RPC protocol lets you pass parameters only by value. So, exception semantics can change silently when you partition applications. For example, if you move a local procedure with NOCOPY parameters to a remote site, those parameters will no longer be passed by reference. Also, the use of NOCOPY increases the likelihood of parameter aliasing. For more information, see Understanding Subprogram Parameter Aliasing.Restrictions on NOCOPYIn 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 index-by table. This restriction does not apply to entire index-by tables. The actual parameter is constrained (by scale or NOT NULL for example). This restriction does not exte