Sample Stored Procedure in Oracle

A Stored Procedure is a named PL/SQL code block. It is a logically grouped set of SQL and PL/SQL statements that perform a specific task. A Procedure can be compiled and stored in database as schema object. Procedures promote reusability and maintainability. when validated, they can be used in any number of application.

 

Syntax of stored procedure

CREATE OR REPLACE PROCEDURE Procedure_Name

(parameter_name1 IN/OUT/IN OUT datatype1,

  parameter_name2 IN/OUT/IN OUT datatype2,

  .......................................................................)

IS/AS

          variable declarations;

          constant declarations;

BEGIN

         PL/SQL statements block;

EXCEPTION

        exception PL/SQL block;

END;

 

NOTE:

1. You can create procedure without any parameter.

2. Variable and constant declarations is also optional.

3. Exception is also an optional statement in procedure.

Sample Procedure#1 : Display Simple Message

SQL> CREATE OR REPLACE PROCEDURE Proc_DisplayMsg

  2  IS

  3  BEGIN

  4  DBMS_OUTPUT.PUT_LINE('Welcome to oracle.readerviewer.com');

  5  END;

  6  /

NOTE : On Set serveroutput to display message on sql prompt.

SQL> SET SERVEROUTPUT ON

SQL> EXEC PROC_DisplayMsg;

Result : Welcome to oracle.readerviewer.com 

Sample Procedure#2 : Display Message using IN Parameter

SQL> CREATE OR REPLACE PROCEDURE 

     Proc_DisplayMsg_IN(vName IN varchar2)

  2  IS

  3  BEGIN

  4  DBMS_OUTPUT.PUT_LINE('You are watching ' || vName);

  5  END;

  6  /

SQL> EXEC PROC_DISPLAYMSG_IN('STAR MOVIES');

Result : You are watching STAR MOVIES                               

Sample Procedure#3 : Addition of two No using IN Parameter

SQL> CREATE OR REPLACE PROCEDURE

     Proc_Addition(vNo1 IN Number,vNo2 IN number)

  2  IS

  3  BEGIN

  4  DBMS_OUTPUT.PUT_LINE('Addition of two no is ' || (vNo1+vNo2));

  5  END;

  6  /

SQL> EXEC PROC_ADDITION(10,20);

Result : Addition of two no is 30  

Sample Procedure#4 : Multiplication of two No using IN Parameter

SQL> CREATE OR REPLACE PROCEDURE

     Proc_Multiplication(vNo1 IN Number,vNo2 IN number)

  2  IS

  3  BEGIN

  4  DBMS_OUTPUT.PUT_LINE('Multiplication of two no is ' |(vNo1*vNo2));

  5  END;

  6  /

SQL> EXEC PROC_MULTIPLICATION(10,20);

Result : Multiplication of two no is 200

  

Sample Procedure#5 : Division of  No using IN Parameter

SQL> CREATE OR REPLACE PROCEDURE

     Proc_Division(vNo1 IN Number,vNo2 IN number)

  2  IS

  3  BEGIN

  4  DBMS_OUTPUT.PUT_LINE('Division of two no is ' || (vNo1/vNo2));

  5  END;

  6  /

SQL> EXEC Proc_Division(100,20);

Result : Division of two no is 5