CHAPTER 7. Subprograms
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;
fetch c into i ;
EXIT WHEN c%NOTFOUND ;
UPDATE sp SET qty = qty + q
where current of c ;
END
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.