Subroutine programs: – Subroutine programs are used to provide modularity and encapsulation in application. Through sub programs, we can have built application using small building blocks. These building blocks are easy to construct and easy to maintain. We can break a large program into manageable small module, which can easily we constructed. It is called top down approach.
Sub programs also provide reusability. Once sub programs are built and validated, they can be used in number of application. Sub programs also provides abstraction and user of sub program should only know what sub program is doing. It is not necessary for him to know implementation details of the sub programs. There are two types of sub programs used in PL/SQL programming. They are called procedure and function.
Procedure: – A procedures are sub program used in PL/SQL for calling it. It is a separate block and is declared before the main block. The procedure is having two parts called specification and buddy. The specification consists of procedure name and parameter declaration. Parameter declaration is optional. If procedure is not having parameter then small bracket is not used. The body, on the other hand, of the procedure consist of local declaration, executable part and exception handling part. Exception handling part is optional. The executable part can have any number of PL/SQL executable statement and at least one executable statement must be used. Syntax of the procedure of PL/SQL is –
Procedure <Procedure Name> (Parameter name1 [IN/OUT/IN OUT] parameter type1,Parameter name2 _ _ _ ) is local declarations; begin PL/SQL executable statements; _ _ _ _ _ _ _ _ _ _ exception exception handler; end <Procedure Name>;
Function: – A function is subroutine program and is used to return single value. Function are specially used in PL/SQL programming for mathematical calculation. Some mathematical calculations are processed and the define function. It collects input value from main source and return output to that location. The function specification consists of function name, parameter declaration and return data type. The parameter declarations are optional and if it is not in use, parenthesis is not allowed. If parameter mode is not given then by default IN mode is assumed.
The body of the function consist of three part same as the procedure of PL/SQL program. The function should have at least one return statement. When the return statement is executed then execution of sub program is terminated and controlled returns to caller routine. The return statement need not be the last statement in the program, but if the return statement is executed then other statement are not executed by the PL/SQL program. Syntax of the function declaration is –
FUNCTION <Function Name> (ParameterName1 [IN/OUT/IN OUT] ParameterDataType1,ParameterName2 [IN/OUT/IN OUT] _ _ _ ) IS RETURN ReturnDataType BEGIN PL/SQL executable statement; EXCEPTION exception handler; END;