PL/SQL Procedure in Oracle Database and Advantages of Stored Procedures #Diksha Oracle Training Centre

 PL/SQL Procedure in Oracle Database and Advantages of Stored Procedures.

 

Hello Everyone,

Today I will discuss about named Block in PL/SQL that is Stored Procedure However Named block in PL/SQL is Procedure, function, packages, trigger etc that I will cover in next blogs. In my previous Blogs we know about PL/SQL Anonymous Block, Cursors and %TYPE and %ROWTYPE attribute. PL/SQL blocks are of two types Anonymous Block and Named Block . Anonymous Block we already discussed in last blog

Let’s Start with Procedure in PL/SQL before that you can go through my previous blog for your reference.

Procedure in PL/SQL is database object which is mainly used for reusability in other word Procedure is as subprogram unit/module that performs a particular task. These subprograms are combined to form larger programs. This is basically called the 'Modular design'.Procedures can be invoked by another subprogram or program which is called the calling program.

Procedure can be created:

Schema Level

In Database Packages

Inside a PL/SQL block

In schema level, Procedure is a standalone subprogram by using CREATE PROCEDURE statement. It is stored in the database and can be deleted with the DROP PROCEDURE statement.

Procedure created inside a package is a packaged subprogram. It is stored in the database and can be deleted only when the package is deleted with the DROP PACKAGE statement. We will discuss different type of Packages in another blog.

PL/SQL Procedures are named PL/SQL blocks that can be invoked with a set of parameters used in database.

PL/SQL Procedures has name and also have parameter list, Like Anonymous Block the named block i.e. Procedure also have three parts as below. Only thing we need to use here for named block is create and pass the parameter as per our requirement .

DECLARE

   <declarations section>

BEGIN

   <executable command(s)>

EXCEPTION

   <exception handling>

END;

A procedure is created by using CREATE statement command in above syntax and also pass the parameter in procedure.

Syntax for Stored Procedure.

 

CREATE [OR REPLACE] PROCEDURE procedure_name

 

[(parameter_name [IN | OUT | IN OUT]

 type [, ...])]

{IS | AS}

BEGIN

  < procedure_body >

EXCEPTION

   <exception handling>

END procedure_name;

 

Here we use OR Replace because if any procedure exists with same name it will drop and recreate it again. Procedure name specifies the name of the procedure. The optional parameter list contains name, mode and types of the parameters. IN represents the value that will be passed from outside and OUT represents the parameter that will be used to return a value outside of the procedure. Procedure body contains multiple things as executable parts. The AS keyword is used instead of the IS keyword for creating a standalone procedure.

Parameters in Procedures: The following parameter used in Stored Procedure

IN

IN parameter use to pass a value to the procedure it’s read-only parameter. Inside the subprogram, an IN parameter acts like a constant. It cannot be assigned a value. You can pass a constant, literal, initialized variable, or expression as an IN parameter. It is the default mode of parameter passing. Parameters are passed by reference.

OUT

OUT parameter returns a value to the calling program. Inside the Procedure/subprogram, an OUT parameter acts like a variable. You can change its value and reference of value after assigning it.

IN OUT

IN OUT parameter passes an initial value to a subprogram and returns an updated value to the caller. It can be assigned a value and the value can be read. The actual parameter corresponding to an IN OUT formal parameter must be a variable, not a constant or an expression. Formal parameter must be assigned a value. Actual parameter is passed by value.

 Example of Simple Procedure that will give output of sum of Salary for department  passed by user as department  name:

CREATE OR REPLACE PROCEDURE dotc (p_deptid IN department.department_name%type)

AS

CURSOR C1 IS

SELECT SUM(A.SALARY) TOTAL_SALARY,B.DEPARTMENT_NAME

FROM EMPLOYEE A , DEPARTMENT B

WHERE A.EMP_ID=B.EMP_ID

AND B.DEPARTMENT_NAME =p_deptid

GROUP BY B.DEPARTMENT_NAME;

BEGIN

FOR I IN C1

LOOP

DBMS_OUTPUT.PUT_LINE(I.TOTAL_SALARY||' '||I.DEPARTMENT_NAME);

END LOOP;

END;

/

 

To run this procedure we need to write below command.

EXECUTE dotc(IT);

Or in Oracle Apex you can run the procedure as below .

BEGIN

DOTC('IT');

END;

Once you run it will display the output as total salary of IT department and this procedure  can be use to get total salary for any department and since it stored in database we can use it any time, this Procedure can be called from another PL/SQL block.

Output of above procedure is as below :

 

Dropping Procedure : If we want to delete this procedure we simply use below command to remove this procedure.

DROP procedure dotc;

Simple Use of Procedure by using IN and OUT Parameter to compute the square of value of a passed value, same parameter to accept a value and then return another result:

DECLARE

  i number;

PROCEDURE square(x IN OUT number) IS

BEGIN

  x := x * x;

END;

BEGIN

   i:= 15;

   squareNum(i);

   dbms_output.put_line(' Square of (15): ' || i);

END;

/

Here x act as both IN and OUT parameter.

Below is the output of above procedure for your reference.

 

Some more Procedure created by using SQL queries and table in Oracle for your reference by using both IN and OUT Parameter.

 Below is the Program:

CREATE OR REPLACE PROCEDURE DOTC1

(P_Empnumber  IN  EMPLOYEE.Emp_ID%TYPE,

                       P_TAB     OUT EMPLOYEE%ROWTYPE) IS

BEGIN

   SELECT EMP_ID, NAME, SALARY, DESIGNATION,

 WORKING_YEAR,ADDRESS

      INTO P_TAB

      FROM EMPLOYEE

      WHERE EMP_ID = P_Empnumber;

  END;

/

 

Here in above Program we use both IN and OUT parameter in one procedure . To run above procedure we need to run the procedure DOTC1 is as below:

 

DECLARE

 P_TAB      EMPLOYEE%ROWTYPE;    

BEGIN    DOTC1(2000, P_TAB); 

DBMS_OUTPUT.PUT_LINE(P_TAB.EMP_ID||' '||P_TAB.NAME||' '||P_TAB.SALARY||' '||P_TAB.DESIGNATION);

END;

Below is the Output of above Procedure:

 

Simple Procedure for Passing one parameter and getting multiple records from the table by using cursor and loop. Below is the Program for your reference.

CREATE OR REPLACE PROCEDURE DOTC2

(P_Empnumber  IN  EMPLOYEE.Emp_ID%TYPE

                       ) IS

CURSOR C1 IS

 SELECT EMP_ID, NAME, SALARY, DESIGNATION,

 WORKING_YEAR,ADDRESS

      FROM EMPLOYEE

      WHERE EMP_ID = P_Empnumber;

BEGIN

FOR I IN C1

LOOP

DBMS_OUTPUT.PUT_LINE(I.EMP_ID||' '||I.NAME||' '||I.SALARY||' '||I.DESIGNATION||' '||I.WORKING_YEAR||' '||I.ADDRESS);

  END LOOP;

  END;

/

To Run procedure DOTC2 we need to issue below command to get the data against employee number pass by user .

EXECUTE DOTC2(3000);

OR

BEGIN

DOTC2(3000);

END;

 

Below is the output of above program for your reference.

 

Note: Procedure is permanently saved to the database and you can call it any time for reusability. Also you can write Procedure as per your requirement and can use multiple conditions so try to write as much procedure as you can to get the advantage of using stored procedure in PL/SQL program.

Important: Procedure may or may not return a value but if condition is true it can return multiple records.

Please go through this blog carefully as Procedure play important role in PL/SQL and also invoked in PL/SQL packages. This blog is important for candidate who is preparing for interview as database developer. In next blog I will come up with more named block like Stored Functions, Triggers, Packages etc. If you have any questions related to this blog please let me know.

Thanks.