CHAPTER 7. Subprograms

PL/SQL User s Guide and Reference V2.3

 

 

7.1    Subprograms

 

          A subprogram is a named block that can be called once defined. A subprogram can accept parameters and pass values back to the caller.

          There are two types of sub programs; the functions and procedures.

          Subprograms provide a way of modularizing the code.

 

 

7.2    Procedures

 

  A procedure consists of two parts, the specification part and body. The specification part begins with PROCEDURE, and ends with parameter list (if there are parameters) or procedure name (if there is no parameter). The procedure body begins with IS and ends with keyword END.

 

A procedure body consists of optional declaration part, executable part and optional exception handling part.

 

The declaration part starts after IS and ends before the first BEGIN. The reserved word DECLARE cannot be used when defining a subprogram. The declaration part contains local variable, constants, subprograms, and exception declarations.

 

  A procedure can be defined with the following syntax:

 

Syntax:

 

                PROCEDURE  name [ ( paramter[, paramter,  ])] IS

                                [ local declarations ]

                BEGIN

                                Executable statements

                  [EXCEPTION

                                exception handlers ]

                END [name ] ;

 

The parameter has the following format:

 

                parameter_name [ IN | OUT | IN OUT ] datatype [ { := | DEFAULT } expr ]

 

Notice that you cannot impost NOT NULL constraint on the parameter nor you can add length, precision constraint on the parameter data type. That is, the following are illegal:

 

                Procedure  f ( I number(5) NOT NULL ) begin   end ;

 

where 5 and NOT NULL violate the syntax rules.

 

Example: How to define function in PL/SQL block

 

-- The example shows how to write a procedure.

declare

       i integer ;

       procedure addQty( q  IN integer ) IS

              CURSOR c IS select qty from sp where snum = 's1'

                     FOR UPDATE ;

       BEGIN

              open c;

              LOOP

                     fetch c into i ;

                     EXIT WHEN c%NOTFOUND ;

                     UPDATE sp SET qty = qty + q where current of c ;

              END LOOP ;

              commit ;

         EXCEPTION

              WHEN OTHERS THEN

                     dbms_output.put_line(SQLERRM );

       END addQty ;

begin

       addQty( 1 ) ;

end ;

 

7.3    Functions

 

Function returns a value and has at least one return statement in the execution part.

 

Syntax of defining funciton:

 

                FUNCTION  name [ ( paramter[, paramter,  ])] RETURN datatype IS

                                [ local declarations ]

                BEGIN

                                Executable statements

                  [EXCEPTION

                                exception handlers ]

                END [name ] ;

 

The parameters are the same as procedure parameters.

 

Example: Defining Function Subprogram

 

-- The example shows how to write a function.

declare

       i integer ;

       function getPartsTotal( sno IN varchar2 ) RETURN integer

    IS

              total integer ;

       BEGIN

              Select sum(qty) into cnt from sp where snum = sno ;

              return cnt ;

         EXCEPTION

              WHEN OTHERS THEN

                     dbms_output.put_line(SQLERRM );

                     return 0;

       END getPartsTotal;

begin

       i := getPartsTotal('s1') ;

       dbms_output.put_line('Tatot part supplied: ' || i) ;

end ;

/

 

          The major differences between procedures and functions are the RETURN clause in the function declaration part and the return statement in the execution part. The return statement takes a arbitrary expression as parameter.

 

7.4    Declaration of Subprograms

 

          Subprograms must be defined in the declaration part after all variable, constant, cursor and exception definitions

          If a subprogram is called by the other subprograms, the called subprogram must be defined before the others. If they call each other, make a forward declaration, or C like prototype:

 

FUNCTION name ( parameter_list )   RETURN datatype ;

PROCEDURE name ( parameter_list )   ;

 

          When creating a package, all subprogram forward declarations/prototypes are put in the package declaration part and the subprogram definitions must be put into the package implementation part. You may not put the prototype in package declaration part, in that case, the subprogram will be accessible only within the package.

          Syntax of subprogram definition in package declaration and implementation:

 

CREATE PACKAGE   tmp AS  -- Package specification

        PROCEDURE  p1 ;

        PROCEDURE  p2 ( a1 INEGER, a2 VARCHAR2 ) ;

        FUNCTION  f1 RETURN INTEGER ;

        FUNCTION f2 ( a1 NUMBER , a2 VARCHAR2 ) RETURN FLOAT ;

End tmp ;

 

CREATE PACKAGE   BODY  tmp AS  -- Package body

        PROCEDURE  p1 IS

        BEGIN

                         

        END p1 ;

        PROCEDURE  p2 ( a1 INEGER, a2 VARCHAR2 )  IS

        BEGIN

                         

        END p2 ;

 

        FUNCTION  f1 RETURN INTEGER IS

        BEGIN

                         

        END f1 ;

        FUNCTION f2 ( a1 NUMBER , a2 VARCHAR2 ) RETURN FLOAT IS

        BEGIN

                         

        END f2 ;

End tmp ;

 

7.5    Parameter Modes

 

  There are three modes a parameter could have, IN, OUT and IN OUT. IN mode allow the corresponding parameter to be any expression of certain type while OUT and IN OUT mode needs the corresponding actual parameter be variables only. IN mode is used for parameter which pass value into subprogram only and OUT mode is used to pass value back to the subprogram calls. IN OUT mode allow parameter to pass value into subprogram initially and brings value back to the call late.

 

7.6    Default Parameters and Parameter-Passing with Named Notation

PL/SQL allows subprograms to be defined with default parameters. The default parameters likes C++ default parameters and more flexible.

 

          Syntax of Defining Default parameter

 

PROCEDURE?FUNCTION (  p datatype  DEFAULT value )

 

          If a subprogram has N formal parameter and M default parameters ( M <= N), then

 

(1)     The M default parameter could be any M parameters anomg the N parameters. (No position restriction like C++.

(2)     In the call to subprogram, N-M parameters, N-M + 1 , N   M + 2   and N parameter can be supplied. When the number of actual parameters are less than the number of formal parameters, then the default values are used to replacing the corresponding missing actual parameters.

(3)     In case that default parameters are not all on the right of parameter list, named notation of passing parameters must be used.

 

          There are two parameter passing notation can be used in PL/SQL. One is called positional notation, and the other is called named notation. The positional notation is the same the C or Pascal parameter passing notation. The actual parameter is associated with formal parameter based on their position. The named notation will name the formal parameter and actual parameter in the subprogram call:

 

Sub_name ( formal_par_name =>  actual_par_name_or_value ,   ) ;

 

    When named notation is used in passing parameter, the order of actual parameter is not import. The positional and named notation passing method can be used in a single call.

 

 

Example: Default parameters and Named (parameter-passing) Notation

 

-- The example shows:

--          1. Default parameters

--          2. Named notation of associating acutal parameter with formal parameters.

declare

      i integer ;

      function getQty( sno IN varchar2 default 's1', pno IN varchar2 default 'p1') RETURN integer

    IS

            total integer ;

      BEGIN

            select sum(qty) into total from sp where snum = sno and pnum = pno ;

            return total ;

        EXCEPTION

            WHEN OTHERS THEN

                  dbms_output.put_line(SQLERRM );

                  return 0;

      END getQty ;

begin

      i := getQty ; -- show qty of s1 and p1

      dbms_output.put_line('Tatol quantity supplied: ' || i) ;

      i := getQty(sno => 's2') ; -- show qty of s2 and p1

      dbms_output.put_line('Tatol quantity supplied: ' || i) ;

      i := getQty (pno => 'p2', sno => 's2') ;

      dbms_output.put_line('Tatol quantity supplied: ' || i) ;

end ;

/

 

* Parameter Aliasing

 

   If one object/variables appear twice in a subprogram or block, then aliasing occurs. When aliasing occurs, the result is indeterminate. The following situation will cause aliasing.

                A global variable is used in the subprogram and the global is called with the same global passed as one of the parameters. A variable is passed twice as OUT parameters.

Function Overloading

The subprogram name can be used more than once in the same block as long as their formal parameters differ in number, order, or datatype family.

The following restrictions apply when overloading a subprogram:

 

1.        Only local and packaged subprogram can be overloaded.

1.Parameter mode ( IN, OUT, IN OUT ) cannot be used to distinguish the subprograms.

1.If two data type names differ but they are in the same family, they cannot use to overload subprogram.

1.You cannot overload two subprogram if only their return types differ.

 

 

For definitions of data types and their families, see chapter 2.

 

 

7.8          Recursions

 

PL/SQL allows you to write recursive subprograms.