java2db

PL/SQL Procedures



A procedure is a block structure which is identified by its own specific name.PL/SQL Procedures are stored as a schema objects in the database and implements business logic in the applications. For this reason, procedures are often referred to as business managers of PL/SQL which not only maintain the business logic repository, but also demonstrate solution scalability and a modular way of programming.

PL/SQL Procedures  The characteristics of PL/SQL procedures :

PL/SQL Procedures  A procedure can neither be called from a SELECT statement nor can it appear as a right-hand operand in an assignment statement.It has to be invoked from the executable section of a PL/SQL block as a procedural statement.

PL/SQL Procedures  They can optionally accept parameters in IN ,OUT or IN OUT mode.

PL/SQL Procedures  This implies that the only possibility for a procedure or return a value is through OUT parameters, but not through the RETURN (value) statement.The RETURN statement in a procedure is used to exit the procedure and skip the further execution.

For recapitulation, the following table differentiates between the IN,OUT and IN OUT parameters.

PL/SQL Procedures IN

PL/SQL Procedures Default parameter mode.

PL/SQL Procedures Parameter’s value is passed into the program from the calling environment

PL/SQL Procedures Parameters are passed by reference

PL/SQL Procedures May be constant, literal, or initialized variable

PL/SQL Procedures Can hold the default value.

PL/SQL ProceduresOUT

PL/SQL Procedures Has to be explicitly defined

PL/SQL Procedures Parameters returns a value back  to the calling environment

PL/SQL Procedures Parameters are passed by value

PL/SQL Procedures Uninitialized variable

PL/SQL Procedures Default value cannot be assigned.

PL/SQL ProceduresIN OUT

PL/SQL Procedures Has to be explicitly defined

PL/SQL Procedures Parameter may pass a value from the calling environment to the program or return a value to the calling environment.

PL/SQL Procedures Parameters are passed by value

PL/SQL Procedures Initialized variable

PL/SQL Procedures Default value cannot be assigned.

 

Syntax:

CREATE [OR REPLACE] PROCEDURE [Procedure Name] [Parameter List]
[AUTHID DEFINER | CURRENT_USER]
IS/AS
[Declaration Statements]
BEGIN
[Executable Statements]
EXCEPTION
[Exception handlers]
END [Procedure Name];

 

Create the below two  tables in the database.

 

Insert few records  in the EMP_SAL_DETAILS  table .

E_NUM
E_NAME
GROSS_SAL
1 Rose 17000
2 Rock 15000
3 Androo 16000
4 Max 18000

Example PL/SQL Procedures :

The below procedure will calculate the  tax, net salary and  insert a record if the tax value is > 2500 in the TAX table. If the record already exist with the same number in the TAX table , it will update the values.

 

Now run the procedure form the another procedure or with EXECUTE  exe_procedure(1);  command.
Once the procedure executed , it will insert the record in TAX table , bz  the cal_tax > 2500.So the result table  like this:

T_NUM
T_NAME
T_TAX
T_NET_SAL
1 Rose 2550 14450



Related Posts :
PL/SQL Arrays and nested tables ( Collections )
IF THEN ELSE and CASE in PL/SQL
PL/SQL FOR and WHILE loop controls
PL/SQL Operators
PL/SQL Triggers
PL/SQL Functions
PL/SQL Variables
   [will not be published]



^ <