Stored Procedures in PL/SQL
A Procedure is a named PL/SQL subprogram that can be optionally parameterized and is used to implement business logic. It is permanently stored in the database as schema object and can be invoked any number of times on specified set of data.
Advantages of procedures
- Maintain the business layer
- Promote reusability of business logic and reduce code redundancy
[IS | AS]
END [procedure name];
REPLACE preserves the object privileges upon the redefinition of the object.
- User must have CREATE [ANY] PROCEDURE privilege to create a stored procedure.
- Parameters are declared in the subprogram header, after the name and before the declaration section for local variables.
Parameters can be passed to a subprogram in three modes
- IN - It is the default pass mode.
- OUT - Used as assignment variable
- IN OUT - Used as assignment variable
SET JOB_ID = P_JOB
4. Execution of the procedure
In SQL* Plus, procedures can be executed using EXECUTE command.
PL/SQL PROCEDURE successfully completed.
It can be invoked as PL/SQL statement inside an anonymous PL/SQL block.
Using the OUT Parameters: Example
Below procedure P_GET_SALARY returns the salary of an employee as OUT parameter.
(P_EMPID IN NUMBER,
P_SAL OUT NUMBER)
Executing the above procedure from SQL* Plus requires a bind value to capture the OUT parameter result. Note that it must of data type compatible with the OUT parameter.
SQ> VARIABLE G_SAL NUMBER;
Now, the program must be executed giving actual parameters.
Please note that the actual parameters are fixed values. They can be passed through session bind variables, but need to be declared and assigned in SQL* Plus.
SQL> PRINT G_SAL
5. Syntax for Passing Parameters
Actual Parameters can be passed following three ways
Positional: All the actual parameters are listed in the same sequence as formal parameters. Error is thrown, if there is any change in the parameter sequence. Example: P_UPD_JOB (100, 'HR');
Named: Each format parameter is associated with the actual parameter using ‘=>’. Order may or may not be maintained. It is easy to maintain and convenient to read also. Example: P_UPD_JOB (P_EMPID => 100, P_JOB => 'HR');
Mixed: This is combination of positional and named ways of passing parameters. Proper flow of notation must be maintained while passing the parameters. Example: P_UPD_JOB (100, P_JOB => 'HR');
6. DEFAULT values for parameters
Parameters can be assigned with default values during procedure definition. If procedure is invoked without passing any actual value, oracle takes the default value and resumes the execution of the procedure. Note that it can be assigned only to IN mode parameters.
Default value can be assigned in two ways
- The assignment operator (:=)
- The DEFAULT keyword option
It is suggested from coding point of view to have parameters with default value must at the end of the parameters listing.
For example, above procedure P_UPD_JOB is redefined with P_JOB carrying a default value as below.
(P_EMPID IN NUMBER, P_JOB IN VARCHAR2 DEFAULT 'DEV') .... .... END;
Above procedure takes two parameters department id and job id and updates in the EMP table. It can be invoked in below ways.
Above call assigns 20 to P_DEPT_ID and takes default value of P_JOBID
Above call assigns actual value to both the parameters.
7. Removing Procedures
A stored procedure can be dropped from database using DROP command. Note that all dependent objects are rendered to INVALID state.