Sub Programs are named PL/ SQL blocks, used to solve some particular tasks.
There are 2 types of sub programs supported by Oracle.
- Procedures
- Functions
Note: Procedures May or May not return a value.
Functions must return a value.
Stored Procedure :
This is a named PL/ SQL block, used to solve some particular task and also procedures may or may not return a value
In General, Procedures are used to improve the performance of Application. Because procedures internally implements "One time Compilation".
We can create/ Alter a procedure using " Create or Replace Procedure ", When we create this any of the oracle tools, the PL/ SQL engine compiles the program Automatically and stores them in the Database.
Hence these procedures are called " Stored Procedures ".
Every Procedure have 2 sections:
- Procedure Specification
- Procedure Body
a) Procedure Specification
Here we specify name of the Procedure and Type of the Parameters.
b) Procedure Body
Here we are solving the actual task or Business logic.
Executing a Stored Procedure:
Method 1:
SQL> exec <procedure_name> (parameters)
or
execute <procedure_name> (parameters)
Method 2:
SQL> begin
<procedure_name> (parameters )
end;
Method 3:
SQL> call <procedure_name> ( parameters)
To View Errors:
SQL> SHOW ERRORS;
Or
SHOW ERRS;
