Oracle Procedure tutorial for beginners

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

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

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.

It should display details like(Somewhat) below.

 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

To call above procedure

 

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.

 

1 Comment Oracle Procedure tutorial for beginners

Leave A Comment

Your email address will not be published. Required fields are marked *