In programming languages, we can pass parameters by reference and by value to a function. In this recipe, we will see how to make out this difference in PL/SQL functions and procedures.
The following steps will demonstrate passing parameters to functions:
Connect to the
SHschema:CONNECT sh@TESTDB/shCreate the type
TAB_NUMBERS, which is a table of numbers:CREATE OR REPLACE TYPE sh.TAB_NUMBERS AS TABLE OF NUMBER;Create a function called
MY_VALUE, which returns an element of an array:CREATE OR REPLACE FUNCTION MY_VALUE(ATABLE IN OUT TAB_NUMBERS, AIND IN NUMBER) RETURN NUMBER IS L_VALUE NUMBER := 0; BEGIN L_VALUE := ATABLE(AIND); RETURN L_VALUE; END; /
Create the function
MY_VALUE_NOCOPY, which acts as the previous one, but the array parameter is defined asNOCOPY:CREATE OR REPLACE FUNCTION MY_VALUE_NOCOPY( ATABLE IN OUT NOCOPY TAB_NUMBERS, AIND IN NUMBER) RETURN NUMBER IS L_VALUE NUMBER := 0; BEGIN L_VALUE := ATABLE(AIND)...