What is Procedure

Procedure is a subprogram which is used to perform a specific action.

Procedure has two parts.

• Specification(Spec)  : Specs begin with PROCEDURE keyword and ends with procedure name or parameter list.
• Body   : Body begins with IS/AS keyword and ends with END keyword.

Procedure statement can be executed from SQL*Plus or can be executed from programe using native dynamic SQL.

Syntax of Procedure

CREATE <OR REPLACE>
PROCEDURE procedure_name(argument1 <IN/OUT/INOUT> <nocopy>, argument2<IN/OUT/INOUT>...)
<AUTHID [DEFINER / CURRENT_USER]> {IS / AS}
<PRAGMA AUTONOMOUS_TRANSACTION;>
<local declarations>
BEGIN
executable statements
<EXCEPTION
exception handlers>
END;

Keywords used in procedure

CREATE OR REPLACE

 Used to create standalone procedure with given “procedure_name”, if there is any procedure exists with given name then procedure will be replaced.

CREATE :

Used to create standalone procedure with given “procedure_name”,if any procedure exists with given name then error will be thrown by PL/SQL Compiler.Error : ORA-00955: name is already used by an existing object>

Note*: If procedure exists then it can be dropped and recreated or above command(CREATE OR REPLACE) can be used. But command have advantage that EXECUTE privilege granted to previous procedure will remain in place.

AUTHID

 Used to identify weather stored procedure executed with the privilege of its owner(Default Behavior) or Current User.

AUTONOMOUS_TRANSACTION

Used to identify a procedure as Autonomous.

IN/OUT/INOUT

These qualifiers are used for arguments.

(Default)IN

This qualifier identifies that argument must have value while calling the procedure.

OUT

This qualifier identifies that argument will be used by procedure to pass value to the caller.

IN/OUT

This qualifier identifies that argument is both IN & OUT.

NOCOPY

It’s a hint to ORACLE. Ask oracle to pass variable back to user asap.

Example of Procedure

CREATE OR REPLACE PROCEDURE JBT (arg1 VARCHAR2, arg2 NUMBER)
IS
   v_test   VARCHAR (20);
BEGIN
   DBMS_OUTPUT.put_line ('arg1 ' || arg1);

     SELECT TRUNC (DBMS_RANDOM.VALUE (1, 9))
          INTO v_test
          FROM DUAL;

   DBMS_OUTPUT.put_line ('Normal Execution-' || v_test);

EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Inside Exception');
END;
/

Compile and check if given procedure is created or not.

How to check if procedure has been created?

To know fire below query it will show the details if any procedure has been created.

SELECT *
  FROM user_objects
 WHERE object_name = 'JBT'

It should display details like(Somewhat) below.

OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED   LAST_DDL_ TIMESTAMP           STATUS  T G S
--------- --------- ------------------- ------- - - -
JBT
                                   167295                PROCEDURE
29-OCT-12 30-OCT-12 2012-10-30:19:02:07 VALID   N N N

 How to Execute Procedure

Once you created the procedure and checked that procedure has been created successfully. It’s time to execute the given procedure.

Syntax

procedure_name(argument1, argument2...)

To call above procedure

exec JBT(9, '10');

 

Procedure Vs Function

• Procedure do not have(May or May not) to return a value to caller but this is compulsary in case of function. In other words Function needs to have return keyword while this is not necessary in case of procedure.
• Functions can be directly refrenced in queries while procedure can’t be.