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?
- What are benefits of modularized program?
- What are Subprograms in PL SQL?
- 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 :
- Easy maintainace : We can easily do the maintainace activities with less programming.
- Security improvement : The modularized program improves the data security and integrity.
- Improved performance : The modularized program will improve the performance.
- 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;
BEGINUPDATE 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.