Stored Procedure in Oracle

 

In Oracle PLSQL procedure is a named PLSQL block that performs a specific task or set of tasks.  The procedure is a self-contained block of code that can accept input parameter, perform operations and returns the values or output parameter.

Procedures are stored and executed on database server.

Syntax:

          Create Or Replace Procedure Procedure_Name(parameter_list)

          IS

--declaration optional

Begin

--Procedure Body

Exception

--Exception Handling(Optional)

END Procedure_Name;

/

In above syntax

Create or Replace Procedure:

The Create or Replace Procedure in a Oracle database is used to create the stored procedure or replace the stored procedure. In database if the stored procedure is already existing then this will be replaced with new procedure.

Procedure_Name:

This is the name of the procedure given by user. User can give any name for the procedure. But always give meaningful name that describes the purpose of the procedure.

Parameter List:

The parameter list is the input and output parameters that procedure can accepts. Parameters are the optional and it allows you to pass the values to perform the tasks and return the values from it.

--declaration (optional):

This part is used to declare the local variables and constants that procedures can use. This section is optional.

BEGIN:

This keyword is used to start the actual PLSQL procedure body. Here the code is written to perform the task of the procedure.

--Procedure Body:

Procedure body is the portion where you can write the actual code to perform the operations and the statements are executed by procedure. This can include the SQL statements, assignments, conditional statements, looping statement etc.

EXCEPTION:

Exception is a keyword used to handle the exceptions in  the procedure.

--Exception Handling(Optional):

This section is used to handle the exception that may occur in the procedure.

END Procedure_Name:

Here your procedure is ends.

/:

The forward slash is used to execute the SQL commands. When you run the code then Procedure is created or replaced in the database.

 

For Example:

SQL> Create Or Replace Procedure Addition(

  2  n1 in number,

  3  n2 in number,

  4  result out number

  5  )

  6  IS

  7  begin

  8  result:=n1+n2;

  9  End Addition;

 10  /

 

Procedure created.

In above example,

The PLSQL procedure Addition is created with N1 and N2 as input number variables and Result is output number variable.

The Addition procedure calculate the sum of N1 and N2 variables and assigns the sum to Result variable.

IS Keyword:

The IS keyword in the stored procedure is used to begin the procedure body. Inside the procedure body you can write actual logic of the procedure. This logic is used to perform the operation of the input parameter and stores the result.

Result:=N1+N2 is the logic used in procedure body to perform the addition on N1 and N2 parameters and store the result in Result parameter.

End Addition:

This is the End of the procedure Addition.

To call and execute the procedure one another PLSQL block should be created that is called Anonymous block as follows,

 

SQL> Declare

  2  result number;

  3  begin

  4  addition(100,50,result);

  5  dbms_output.put_line('The addition is:' || result);

  6  End;

  7  /

The addition is:150

PL/SQL procedure successfully completed.

The result parameter is declared with number data type using Declare keyword.

Then Addition procedure is called in the Begin and END block. In the procedure call two different numeric values are passed for N1 and N2 parameter and Result variable is also passed to display the result into the result variable.

The  stored result is displayed with DBMS_Output.Put_line function.

Block is terminated with End keyword and semicolon.

Then forward / slash is used to execute the procedure.

 

Another example to create procedure to perform Arithmetic operation

SQL> Create Or Replace Procedure Arith(

  2  n1 in number,

  3  n2 in number,

  4  add out number,

  5  sub out number,

  6  mult out number,

  7  div out number

  8  )

  9  IS

 10  begin

 11  add:=n1+n2;

 12  sub:=n1-n2;

 13  mult:=n1*n2;

 14  div:=n1/n2;

 15  End Arith;

 16  /

 

Procedure created.

To call and execute Arith procedure  create one another Anonymous block

SQL> Declare

  2  add number;

  3  sub number;

  4  mult number;

  5  div number;

  6  begin

  7  Arith(10,5,add,sub,mult,div);

  8  dbms_output.put_line('The addition is:' || add);

  9  dbms_output.put_line('The subtraction is:' || sub);

 10  dbms_output.put_line('The multiplication is:' || mult);

 11  dbms_output.put_line('The division is:' || div);

 12  End;

 13  /

The addition is:15

The subtraction is:5

The multiplication is:50

The division is:2

 

PL/SQL procedure successfully completed.