While wring PL/SQL code, keep in mind that
- if I need any calculations, create a function.
- if any DB operations which does not return any values from it, create a procedure.
Following ways we can use the parameters passed and returned from functions and procedures:
- All IN parameters of procedure are passed by pointer. Variable is not copied when program block is called.However PL/SQL block is not allowed to modify the variable value.
- All OUT and IN/OUT parameters are passed by value. Variable is copied when program block is called, so the pl/sql code can modify a copy of the variable.When pl/sql block completes without error, the original variable is overwritten with the modified copy of variable. If the program block raises exception, the original value remains unchanged.
- The OUT and IN/OUT parameters can be passed by reference if we use the NOCOPY directive for them.However its not available for function return value.
A function that returns variable as pass by value.
CREATE OR REPLACE FUNCTION func_VarIN(p1 IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN p1;
END;
/
An empty procedure using the IN/OUT parameter.
CREATE OR REPLACE PROCEDURE prc_VarINOUT(p2 IN OUT VARCHAR2) IS
BEGIN
NULL;
END;
/
An empty procedure using IN/OUT parameter with NOCOPY directive.
CREATE OR REPLACE PROCEDURE prc_VarINOUT_NOCOPY(p3 IN OUT NOCOPY VARCHAR2) IS
BEGIN
NULL;
END;
/
BEGIN
NULL;
END;
/
PL/SQL Code Observations:
- Function calls perform similar to procedure calls with IN/OUT parameters.
- NOCOPY directive reduces the copy overhead and causes the call to be twice as fast then the function or regular procedure.
- using NOCOPY, bigger value of variable gives bigger boost on performance.
Using of procedures with NOCOPY rather than functions, it pefroms really good - upto 20 -25 times faster and gives us good practices for PL/SQL code clarity.
