Subprograms

The PL/SQL programs can be stored in the database as stored programs and can be invoked whenever required. This avoids repassing when multiple users invoke it. This also provides security and integrity control by allowing access on the sub program and not on the database objects directly. Actually subprograms are named PL/SQL blocks that can take parameters and be invoked.

What is subprograms?

Subprograms are PL/SQL blocks that can take parameter and can be invoked. PL/SQL has two types of subprograms PROCEDURES and FUNCTIONS. A procedure is used to perform an action and a function to compute a value.

Why use subprograms?

Provides extensibility
Provides modularity
Promotes reusability and manageability

How to use subprograms?
Using subprograms

Subprograms have a declarative part, an executable part and an optional exception handling part. The declarative part contains declarations of types, cursors, constants, variables, exceptions, and nested subprograms. These objects are local and cease to exist when exited from the subprogram. The executable part contains statements that assign values, control execution, and manipulate ORACLE data. The exception handling part contains exception handlers, which deal with exceptions raised during exception.

• procedure is a subprogram that performs a specific action. The syntax Is: ,

PROCEDURE name [parameter [,parame ter IS local declaration
BEGIN
executable statements
[EXCEPTIONS]
END [name];
where parameter stands for
var_name [IN/OUT/IN OUT] datatype
:=!DEFAULT) value]

• procedure has two parts the SPECIFICATION and the BODY. A specification begins with the keyword PROCEDURE and ends with the procedure name or parameter list. A body begins with the keyword IS and ends with the keyword END. A body has three parts a DECLARATIVE PART, an EXECUTABLE part, and an optional EXCEPTION handling part.

Example: PROCEDURE RAISE SAL (EMP ID INTEGER, INCREASE REAL) IS
CURRENT_SAL REAL; BEGIN
SELECT SAL INTO CURRENT_SAL FROM EMP
WHERE EMPNO = EMP_ID;
IF CURRENT SAL IS NULL
THEN _HANDLE IT
ELSE
UPDATE EMP SET SAL = SAL + INCREASE WHERE EMPNO=EMPID;
END IF;
END RAISE SAL;

A procedure is called as a PL/SQL statement. The procedure RAISE SAL is called as

RAISE SAL (EMP_NUM, AMOUNT);
RAISE SAL (538, 1000) Functions:

A function is a subprogram that computes a value. It differs from procedure as function returns a value. The syntax is

FUNCTION name [(argument [,argument...]) ]
RETURN. datatype IS
[local declaration]
BEGIN
executable statements
[EXCEPTION]
END [name];
Where argument stands for the following

var_name [in out | in out] datatype
[{:=|DEFAULT}VALUE]

A function has two parts SPECIFICATION and BODY. The specification begins with the keyword FUNCTION and ends with the RETURN clause which specified the datatype of the result value. Function BODY begins with the keyword IS and ends with the keyword END with an optional name. BODY has three parts as that of the procedure.

FUNCTIONAL SAL_OK (SALARY REAL, TITLE REAL)
RETURN BOOLEAN IS
MIN_SAL REAL;
MAX_SAL REAL;
BEGIN

SELECT LOSAL, HISAL INTO MIN_SAL, MAX SAL
FROM SALS WHERE JOB = TITLE;
RETURN (SALARY > MIN_SAL) AND
(SALARY < =MAX SAL);
END SAL_OK;

If the salary is out of range sal_ok is set to false; otherwise, sal ok is set to tr ue.

A function is called as a part of an expression. The function Sal ok is called as IF SAL_OK (NEW_SAL, NEW TITLE) THEN
......
ELSE
....
END IF;

Functions should not be called inside SQL statements.

Return statement

A RETURN statement immediately completes the execution of subprogram and returns control to the caller. A subprogram can contain several. return statement. Executing any one of them completes the program.

FUNCTION BALANCE (ACCT_ID INTEGER)
RETURN REAL IS ACCT_BAL REAL;
BEGIN
SELECT BAL INTO ACCT_BAL FROM ACCTS
WHERE ACCT NO = ACCT_ID;
RETURN ACCT_BAL; END BALANCE;
Forward Declaration You have to declare a subprogram before calling it. Suppose the subprograms are mutually recursive then it will be a problem. This is solved by FORWARD DECLARATION. By this we can define subprograms in logical order or alphabetical and mutually recursive.

A forward declaration consists of a subprogram specification terminated by a semicolon.

DECLARE
PROCEDURE CALC_RATING ;
FORWARD DECLARATION
PROCEDURE AWARD_BONUS(..) IS
BEGIN
......
.....
END AWARD_BONUS;
PROCEDURE CALC_RATING( ... )IS
BEGIN
.....
.....
END CAL_RATING;

Although the formal parameter list appears in the forward declaration, it must also appear in the subprogram body. The subprogram body can be placed anywhere after the forward declaration, but they must appear in the same block, subprograms, or package.

Parameter Modes

There are three parameter modes IN, IN OUT, OUT. Avoid out and inout modes.

IN: An IN parameter lets you pass values to the subprogram being called. Inside the subprogram, an IN parameter acts like a constant. Thus value cannot be assigned to it. The actual parameter that corresponds to an IN formal parameter can be a constant, literal, initialized variable, or expression.

OUT: The OUT parameter lets you returns value to the caller of a subprogram being called. Inside the Subprogram, an OUT parameter acts like an uninitialised variable. Therefore, its value cannot be assigned to another variable or reassigned to itself. The actual parameter that corresponds to an OUT formal parameter must be a variable.

IN OUT: The INOUT parameter lets you pass initial values to the subprogram being called an, return updated values to the caller. Inside the subprogram, an IN OUT parameter acts like an initialized variable. Therefore, it can be assigned a value and its value can be assigned to another variable.

Domain Name Search