PL/SQL PROCEDURES

A subprogram is a program unit/module that performs a particular task. These subprograms are combined to form larger programs. This is basically called the 'Modular design'. A subprogram can be invoked by another subprogram or program which is called the calling program.

A subprogram can be created:

·        At schema level

·        Inside a package

·        Inside a PL/SQL block

Parts of a PL/SQL Subprogram

Each PL/SQL subprogram has a name, and may have a parameter list. Like anonymous PL/SQL blocks and, the named blocks a subprograms will also have following three parts:

S.N.	Parts & Description
1	Declarative Part It is an optional part. However, the declarative part for a subprogram does not start with the DECLARE keyword. It contains declarations of types, cursors, constants, variables, exceptions, and nested subprograms. These items are local to the subprogram and cease to exist when the subprogram completes execution.
2	Executable Part This is a mandatory part and contains statements that perform the designated action.
3	Exception-handling This is again an optional part. It contains the code that handles run-time errors.

 PL/SQL subprograms have three parameter modes:

1.   IN parameter mode is used to send values into the subprogram from the calling program. An IN parameter can only be read, its value cannot be modified inside the subprogram.

2.   OUT parameter mode is used to return values from the subprogram to the calling program.

3.   IN OUT parameter mode is a hybrid of IN and OUT. It is used to send values into the subprogram, and its value can be modified within the subprogram. On exit, the subprogram returns the updated value of the IN OUT parameter to the calling program.A major difference between IN, OUT and IN OUT parameter modes is the way in which they pass a parameter to a subprogram: by reference or by value.

4.   NOCOPY is a hint to the PL/SQL compiler to pass OUT and IN OUT parameters by reference instead of by value. The use of NOCOPY saves on the processing and memory overhead of copying data from subprogram to calling program.

Parameter passing methods: by Reference vs. by Value

Pass by reference: The compiler sends to the subprogram a pointer to the actual parameter of the calling program. Any change to the parameter value inside the subprogram reflects in the calling program, since the parameter inside and outside the subprogram refer to the same memory location.

Pass by value: The compiler sends to the subprogram a copy of the actual parameter value from the calling program. A parameter passed by value can be modified independently of its value in the calling program. If the subprogram completes successfully, the parameter value is copied back to the calling program. If an exception occurs, the parameter value is not copied back to the calling program.

Passing by value – and the problem of performance

When pass-by-value parameters (i.e. OUT and IN OUT parameters) are large data structures like collections, records, and object types, the copying of data from subprogram to calling program slows down execution and uses up memory. For smaller data structures, the difference in performance between passing by value vs passing by reference would be insignificant, but the overhead can be large for large data structures.

Create PROCEDURE

SQL> CREATE or REPLACE PROCEDURE pro1(no in number,temp out emp1%rowtype)

IS

BEGIN

    SELECT * INTO temp FROM emp1 WHERE eno = no;

END;

/

Execute PROCEDURE

SQL>@pro1

PL/SQL procedure successfully completed.

PL/SQL Program to Calling Procedure

SQL>ed
DECLARE
    temp emp1%rowtype;
    no number :=&no;
BEGIN
    pro1(no,temp);
    dbms_output.put_line(temp.eno||'     '||
                           temp.ename||'   '||
                           temp.edept||'   '||
                           temp.esalary||' '||);
END;
/

So when should you use NOCOPY?

Use NOCOPY when both of these conditions are true:

§  the OUT or IN OUT parameters of a subprogram use large data structures causing performance issues in parameter passing

§  the calling program can ignore the parameter values returned by the subprogram if the subprogram exits with error