What is Procedure in PL SQL? | Examples of PL SQL Procedure

In my previous articles I have given what is subprograms in PL SQL as well as difference between anonymous block and subprogram. In this article I would like to give information about most asked question in PL SQL interviews i.e. What is Procedure in PL SQL with examples? As you all know that PL SQL is modularized programming language where we are using modular program development. We can used Procedures, functions, triggers, anonymous block to do modularized programs in PL SQL.

What we will see in this article?

1. What are benefits of modularized program?
2. What are Subprograms in PL SQL?
3. What is procedure in PL SQL with examples?

What are benefits of modularized program?

Before we can see what is procedure in PL SQL we require to know about modularized program and the benefits of modularized program in detail. The modularized program is nothing but we are using modules to do the programs or to add logic in PL SQL. There are following benefits of modularized program :

1. Easy maintainace : We can easily do the maintainace activities with less programming.
2. Security improvement : The modularized program improves the data security and integrity.
3. Improved performance : The modularized program will improve the performance.
4. Improve Code clarity : It will improve the code understanding and clarity of code.

What are subprograms in PL SQL?

In this section you will get more information about the subprograms in PL SQL in bullet points.

Point 1 : Definition of Subprograms

The subprograms are named PL SQL blocks which user can reuse again and again for specified purpose.

Point 2 : Calling Mechanism

User can call the set of subprograms with using the parameters.

Point 3 :Declaration of subprograms

Users can declare of define PL SQL blocks within the subprograms or another subprograms.

Point 4 : Examples of subprograms

There are two type of subprograms in PL SQL : 1. Procedures 2. Functions

Point 5 :Usage of subprograms

The key use of procedures is to perform specific action and key use of function is to perform computation and return the value.

What is procedure in PL SQL with examples?

In this section we can see what is procedure in PL SQL with real examples.

Definition of Procedure in PL SQL?

The PL SQL procedure is nothing but the subprogram to perform the specific action.

The PL SQL procedure can be stored in the specified schema.

User can reuse the procedure or call the procedure again and again so reusability is important feature of procedure in PL SQL.

User can easily do maintainace activities on PL SQL procedures. The key features of PL SQL procedures are reusability and maintainability.

You can start or use naming convention as P_Procedure_name which is standard naming convention to create procedures.

How to Create procedure in PL SQL with example?

Syntax to create procedure in PL SQL :

Create or replace procedure Procedure_name(Parameters) is/as

Define variables

Begin

Actual business Logic

Exception

Define exceptions

End;

You can use the above syntax to create the procedure. We are using 3 parameters IN, OUT,INOUT which we will discuss in seperate article.

Real life example :

If you require to update the salary of employees passing Employee number and amount. Here we require to use input parameters as employee_id and Amount.

CREATE OR REPLACE PROCEDURE P_UPDATE_SAL
(P_EMP_ID IN NUMBER, P_AMOUNT IN NUMBER)
IS
–here you define variables
n number;
BEGIN

UPDATE employees
set salary=salary+P_AMOUNT
where employee_id=P_EMP_ID;
commit;

exception

WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE (SQLCODE);
DBMS_OUTPUT.PUT_LINE (SQLERRM);

END;

You can execute the procedure using Exec statement.

Exec P_UPDATE_SALARY (100,1000);

select * from employees
where employee_id=100;

You can also call procedure in PL SQL Anonymous block :

begin

P_UPDATE_SAL (&emp_id,&amount); –UPDATE_SAL (100,5000)
end;

The above statement will update the salary by 5000 for employee number 100.

Video for Procedure in PL SQL :

I hope you like this article on procedures in PL SQL with real life examples. If you like this article or if you have some issues with the same kindly comment in comments section.