PL-SQL Procedures in Oracle – Ridiculously Simple! Read This

PL-SQL-Procedures Learn procedures in PL-Sql with simple explanation with examples.

PLSQL PROCEDURES

Procedure is a PL/SQL block/group of statements that can be called by name. We can give stored procedure a name how we give name to a function.
Before seeing the syntax let us answer to question why it is called as stored procedures? Answer is that we can write a group of statements and store them into a oracle data base catalogue, so, we can reuse it whenever is required in the program.

PL/SQL PROCEDURES Syntax

CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
{IS | AS}
BEGIN
< procedure_body > --EXEECUTION PART
EXCEPTION
-- EXCEPTION HANDLERS --HANDLES EXCEPTIONS
END procedure_name;

PROCEDURES has 2 parts

• HEADER of the Procedure – contains the PROCEDURE NAME and PARAMETER LIST
• Body of the Procedure.– contains Execution Part and Exception Handling Part.

Lets have few important points before further explanation of stored procedures.

• Procedure accepts zero or more parameters as input (IN), output (OUT), or both (INOUT).
• Procedures do not return a value; instead the INOUT parameter or OUT parameter may be used to pass a value from the procedure.
• Procedures cannot be used in SQL statements; they are invoked using the EXECUTE command or called inside a PL/SQL block.
  There can be multiple OUT parametes to recieve the values from procedures.

Executing Stored Procedure

Executing without parameter

syntax:

Execute/Exec PROCEDURE_NAME();
OR
Execute/Exec PROCEDURE_NAME;

Executing with parameters

Execute/Exec PROCEDURE_NAME(param1,param2.....paramn);

PL SQL Stored Procedure Examples

Example 1 with no parameters

CREATE OR REPLACE PROCEDURE PRINT

AS

BEGIN
DBMS_OUTPUT.PUT_LINE('Hello World');
END PRINT;

Here is how we call store procedure named PRINT
Execute PRINT();

Output:

Hello World

Example 2 with IN parameter:

Send two IN parameters to procedure ADDITION and display the addition.

create or replace
PROCEDURE ADDITION(a IN NUMBER , B IN NUMBER)
AS
D NUMBER;
BEGIN

D:=A+B;
DBMS_OUTPUT.PUT_LINE('Addition of two numbers is '|| D);

END;

Execute ADDITION(2,3); — call addition stored procedure.

Output:

Addition of two numbers is 5

Example 3 Using IN and OUT parameter

Send two IN parameters to procedure ADDITION and get the addition in OUT parameter.

create or replace
PROCEDURE ADDITION(a IN NUMBER , B IN NUMBER , C OUT NUMBER)
AS
BEGIN

C:=A+B;

END;
--Lets call this procedure in a simple pl sql block
DECLARE
D NUMBER
BEGIN
Execute ADDITION(2,3, D );
DBMS_OUTPUT.PUT_LINE('Addition of two numbers is '|| D);
END;

A Side Note:

• parameters are used to pass the values To and From of Calling Environments in the oracle server
• parameters are the values that will be processed or returned via the execution of procedure or function
• RETURN keyword used in procedure to halt the execution of the procedure and not for Returning the value.
• Return statement in procedure does not take any expression or constant.
  parameters have 3 different modes
  1. IN MODE – Default mode of the subprogram ,used to pass the values from calling environment.
  2. OUT MODE-used to return the value from the program to calling environment. For this default values cannot be assigned. Values can be assigned if the program is successful
  3. INOUT MODE-can pass a value into a program from calling environment and can return a value from a program to its calling environment.

DROPPING A PROCEDURE.

Syntax:

DROP PROCEDURE PROCEDURE_NAME;

Example

DROP PROCEDURE PRINT

To VIEW THE PROCEDURE AVAILABILITY

Syntax:

SELECT * from USER_OBJECTS WHERE OBJECT_NAME ='PROCEDURE_NAME';

Syntax:

SELECT * FROM USER_SOURCE WHERE NAME='PROCEDURE_NAME';