A procedure is a group of PL/SQL statements that you can call by name which performs one or more specific task.
A procedure has a header and a body.
- The header consists of the name of the procedure and the parameters or variables passed to the procedure.
- The body consists or declaration section, execution section and exception section similar to a general PL/SQL Block.
The syntax for creating a procedure:
How to pass parameters in procedure:
We can pass parameters to procedures in three ways.
1) IN-parameters: The IN parameter can be referenced by the procedure or function. The value of the parameter cannot be overwritten by the procedure or the function.
2) OUT-parameters: The OUT parameter cannot be referenced by the procedure or function, but the value of the parameter can be overwritten by the procedure or function.
3) IN OUT-parameters: The INOUT parameter can be referenced by the procedure or function and the value of the parameter can be overwritten by the procedure or function.
- A procedure may or may not return any value.
- IS – marks the beginning of the body of the procedure and is similar to DECLARE in anonymous PL/SQL Blocks. The code between IS and BEGIN forms the Declaration section.
- The AS keyword is used instead of the IS keyword for creating a standalone procedure.
How to execute a Stored Procedure
There are two ways to execute a procedure.
1) From the SQL prompt.
EXECUTE [or EXEC] procedure_name;
2) Within another procedure – simply use the procedure name.
procedure_name;
PL/SQL Create Procedure
To view the Procedure availability
Recompile an Existing Procedure.
Dropping Procedure.
Similar to dropping a table, the procedure can also be dropped.
IN & OUT Mode Example 1
This program finds the minimum of two values. Here, the procedure takes two numbers using the IN mode and returns their minimum using the OUT parameters.
IN & OUT Mode Example 2
This procedure computes the square of the value of a passed value. This example shows how we can use the same parameter to accept a value and then return another result.
Methods for Passing Parameters
Actual parameters can be passed in three ways
Positional notation
It is simply an association of the values by POSITION of the arguments at call time with that of declaration in the header of the procedure creation. The order of the parameters used when executing the procedure should match the order in the procedure header exactly.
Ex: findmin(a, b, c);
Named notation
It is an explicit association using the symbol =>
In Named notation, the order of the parameters are doesn’t matter.
Ex: findmin(x => a, y => b, z => c);
Mixed notation
In mixed notation, you can mix both notations in procedure call; however, the positional notation should precede the named notation.
Ex: findmin(a, b, z => c); — is valid
Ex: findmin(X => a, b, c); — is not valid