SUB PROGRAMS, PROCEDURES, FUNCTIONS

S U B P R O G R A M S
The PL/SQL blocks discussed until now are called as Anonymous blocks or Named blocks. These blocks are not stored within the database and hence they are compiled each time they are executed and they can’t be called from any other PL/SQL block. Procedures and Functions in PL/SQL are the named blocks that are stored within the database. They can be called from other PL/SQL blocks and these are combinely called as SUBPROGRAMS.
Functions and Procedures are similar to functions and Procedures of other third generation languages. The difference between a Function and Procedure is a function must return a value while a Procedure must not return a value.
FUNCTIONS :
Function is a PL/SQL block that performs a specified task and returns a value to the user.

For creating a Function the following syntax is used.
Syntax :
CREATE OR REPLACE FUNCTION FUNCTIONNAME ( ARG1 IN/OUT/IN OUT DATATYPE, ARG2 IN/OUT/IN OUT DATATYPE, … , ARG N INM/OUT/IN OUT DATATYPE ) RETURN TYPE IS/AS
VARIABLE DECLARATION
BEGIN
EXECUTABLE STATEMENTS
EXCEPTION
ERROR HANDLING ROUTINES
END FUNCTIONNAME;


MODES OF PARAMETERS : To a function or procedure in PL/SQL, parameters can be passed in three ways. They are In, OUT and IN OUT. The purpose of these modes is as follows.

1.When a parameter is passed in “ IN ” mode then the value of that parameter is read only within that function or procedure.

2.When a parameter is passed in “ OUT ” mode then that parameter is write only within that function or procedure. Any changes made to the “ OUT ” parameter within a function or procedure will automatically change the value of its corresponding argument.

3.When a parameter is passed in “ IN OUT ” mode then that parameter can be read and write within that function or procedure. Like OUT parameter any changes made to the IN OUT parameter will automatically reflect in corresponding argument.

CREATE OR REPLACE FUNCTION TESTMODE ( A_IN IN NUMBER, A_OUT OUT NUMBER, A_INOUT IN OUT NUMBER ) RETURN BOOLEAN IS TEMP NUMBER(3);
BEGIN
TEMP := A_IN; VALID
A_IN := TEMP; INVALID
A_OUT := TEMP; VALID
TEMP := A_OUT; INVALID
A_INOUT := TEMP; VALID
TEMP := A_INOUT; VALID
RETURN(TRUE);
END TESTMODE;

NOTE : A function or procedure created with OUT or IN OUT parameters must be called from another PL/SQL block using variables as arguments in function or procedure call corresponding to OUT and IN OUT parameters.

SOME PROGRAMMES ARE TO BE WRITTEN AS EXAMPLES.

POSITIONAL AND NAMED NOTATIONS : CREATE OF REPLACE FUNCTION TOTAL
( M1 NUMBER(10),
M2 NUMBER(10),
M3 NUMBER(10)) RETURN NUMBER IS
BEGIN
RETURN( M1 + M2 + M3 );
END;

If we take the above function as an example, it can be called by using following statement.
T := TOTAL(10, 20, 30 );
When the total function is called by using the above syntax, the value 10 is passed to the parameter M1, 20 is passed to M2 and the value 30 is passed to the third parameter M3. This type of argument passing is called as “ Positional Notation ” as argument is passed to the parameter depending on their position.
The same example can be called by using the following statement.
T := TOTAL( M1 => 10, M3 => 30, M2 => 20 );
When the function “TOTAL” is called by using the above statement, value 10 is assigned to the parameter M1, 20 is assigned to M2 and 30 to M3. Notice that even the value 20 is in third position it is assigned to M2. This type of argument passing is called “ NAMED NOTATION ” as values are passed depending on the name of the parameters.

DEFAULT VALUES FOR PARAMETERS :
We can assign default values to the parameters during the creation of procedure or function itself. When default values are specified for parameters then passing values to those parameters during function or procedure call becomes optional.

SQL> CREATE OR REPLACE FUNCTION TOTAL(M1 NUMBER, M2 NUMBER, M3 NUMBER, M4 NUMBER := 0, M5 NUMBER := 0 ) RETURN IS
BEGIN
RETURN ( M1 + M2 + M3 + M4 + M5 );
END;

In the above function TOTAL, last two parameters m4, m5 are given the default value 0.Hence during the total function call, passing values to these two parameters is optional. Hence we use the same TOTAL function for calculating totals of either three numbers or four numbers or five numbers. If the total function has M1 as default parameter and M2 and M3 are compulsory, again M4 as default and M5 as compulsory then to call the total function with three values we must use named notation as follows.

T := TOTAL( M2 => 50, M3 => 40, M5 => 60 );

P R O C E D U R E S
Procedures are the PL/SQL blocks that performs the action specified by the user but doesn’t return any value. Procedures are also subprograms like functions. The main difference between Function and Procedure is a function should return a value while a procedure must not. A procedure is created by using the following Syntax.

Syntax :

CREATE OR REPLACE PROCEDURE PNAME ( PARAM 1 IN/OUT/IN OUT DATATYPE,
PARAM 2 IN/OUT/IN OUT DATATYPE, … …, PARAM N IN/OUT/IN OUT DATATYPE) IS / AS
LOCAL VARABLE DECLARATION
BEGIN
EXECUTABLE STATEMENTS
EXCEPTION
ERROR HANDLING ROUTINES
END PROCEDURE.

Write a Procedure to accept the details of a student and insert then into student table.

CREATE OR REPLACE PROCEDURE STUPRO( S_SNO STUDENT.SNO%TYPE, S_SNAME STUDENT.SNAME%TYPE, S_COURSE STUDENT.COURSE%TYPE) AS
BEGIN
INSERT INTO STUDENT VALUES(S_SNO, S_SNAME, S_COURSE);
END STUPRO;

If we want to call the procedure then we follow as follows…
SQL> EXECUTE STUPRO(1, ‘RAVI’, ‘ORACLE’ );

Write a procedure to accept marks in three subjects, calculate total, average, grade and insert those values into main table.

CREATE OR REPLACE PROCEDURE FIND
( S_M1 NUMBER, S_M2 NUMBER, S_M3 NUMBER ) AS
( S_TOT NUMBER(5), S_AVG NUMBER(5,2), S_GRADE VARCHAR2(10) )
BEGIN
S_TOT := TOTAL( S_M1, S_M2, S_M3, S_TOT, S_AVG, S_GRADE );
INSERT INTO STUDENT VALUES ( S_M1, S_M2, S_M3,S_TOT,S_AVG,S_GRADE);

LOCAL SUBPROGRAMS
A function or procedure can be declared within the declaration section of another function or procedure. When a function or procedure is declared within another function or procedure then those functions and procedures are called as “LOCAL SUB PROGRAMS”. Local sub programs can be used within the function or procedure in which they are declared. But they can’t be used outside that procedure or function.

à CREATE OR REPLACE PROCEDURE ADDMARKS( P_M1 MARKS.M1%TYPE,
P_M2 MARKS.M2%TYPE, P_M3 MARKS.M3%TYPE, P_SNO MARKS.SNO%TYPE ) AS

FUNCTION TOTAL ( M1 NUMBER, M2 NUMBER, M3 NUMBER ) RETURN NUMBER IS
BEGIN
RETURN ( M1 + M2 + M3 );
END TOTAL;

FUNCTION AVEG ( T NUMBER ) RETURN NUMBER IS
BEGIN
RETURN(T/3);
END AVEG;

FUNCTION GRADE ( A NUMBER ) RETURN VARCHAR2 IS
G VARCHAR2(15);
BEGIN
IF A > 80 THEN G := ‘DISTINCTION’ ;
ELSIF A > 60 THEN G := ‘FIRST ’;
ELSIF A > 50 THEN G := ‘SECOND’;
ELSIF A > 35 THEN G := ‘THIRD’;
ELSE G := ‘FAIL’;
END IF;
RETURN(G);
END GRADE;

TOT NUMBER(5);
A NUMBER;
G VARCHAR2(15);
BEGIN
TOT := TOTAL( P_M1, P_M2, P_M3 );
A := AVEG(TOT);
G := GRADE(A);
INSERT INTO MARKS VALUES( P_SNO, P_M1, P_M2, P_M3, TOT, A, G );
END ADDMARKS;