Databases Reference
In-Depth Information
BEGIN
SELECT CUST_ID INTO MY_IDS(1) FROM CUSTOMERS
WHERE ROWNUM < 2;
MY_IDS.EXTEND(9999999, 1);
get_time(t1);
FOR J IN 1..9999999 LOOP
L_VALUE := L_VALUE + MY_VALUE(MY_IDS, J);
END LOOP;
get_time(t2);
DBMS_OUTPUT.PUT_LINE('RESULT ' || TO_CHAR(L_VALUE));
DBMS_OUTPUT.PUT_LINE('TIME (CALL BY VALUE): ' || (t2 - t1));
L_VALUE := 0;
get_time(t1);
FOR J IN 1..9999999 LOOP
L_VALUE := L_VALUE + MY_VALUE_NOCOPY(MY_IDS, J);
END LOOP;
get_time(t2);
DBMS_OUTPUT.PUT_LINE('RESULT ' || TO_CHAR(L_VALUE));
DBMS_OUTPUT.PUT_LINE('TIME (NOCOPY - CALL BY REF): ' ||
(t2 - t1));
END;
/
SET SERVEROUTPUT OFF
6.
Clean the database:
DROP TYPE TAB_NUMBERS;
DROP FUNCTION MY_VALUE;
DROP FUNCTION MY_VALUE_NOCOPY;
How it works...
In step 3, we have created a simple function that accepts an array as the first parameter with
the
IN
OUT
clause. This parameter is then passed by value to the function. When a parameter
is passed by value, the actual value of the parameter is copied in memory before launching
the function, and this copy is used by the function during execution.