NOCOPY参数的使用.docVIP

Passing Large Data Structures with the NOCOPY Compiler Hint 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 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 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 N