PL/SQL stored procedure in oracle database

PL/SQL procedure:

In simple words, a stored procedure is a database object which is used to implement the specific business logic of the application, unlike other objects like table, index it is not used to store data. So it is a named block stored as a schema object in the Oracle database.

For example if we want to implement a fixed annual increment process for the employees of a particular department we can write a procedure for the same. And we can this procedure every year to increment salary.

the basic syntax of creating a procedure(All optional parts are highlighted in italic):

CREATE [OR REPLACE] PROCEDURE procedure_name
    [ (parameter [,parameter]) ] 

IS or AS
    [declaration_section] 

BEGIN
    executable_section

[EXCEPTION
    exception_section] 

END [procedure_name] 

Explanation:

PL/SQL procedure header:

CREATE [OR REPLACE] PROCEDURE procedure_name
    [ (parameter [,parameter]) ] 

IS or AS

In this section a procedure name and its optional parameters list are declared. Generally we use create or replace to create or change(option to overwrite the current procedure with the new code) the definition of an existing procedure. If we want to change the definition or try to create a procedure that already exists in the system with only create procedure statement then it will throw an error as an object already exists. To avoid such an issue one should always use create or replace.

There are three types of parameters as mentioned below.

IN:

Oracle uses IN as the default mode, It means that if no mode is specified explicitly, Oracle will use the IN mode. An IN parameter is read-only, it means it cannot change its value. It means that if you don’t specify the mode for a parameter explicitly, Oracle will use the IN mode.

OUT:

An OUT parameter is writable. OUT parameter is used to return values to the calling program. Note that a procedure ignores the value that is supplied for an OUT parameter.

INOUT:

An INOUT parameter is both readable and writable. The procedure can read and also modify it.

PL/SQL procedure body:

[declaration_section] 

BEGIN
    executable_section

[EXCEPTION
    exception_section] 

END [procedure_name] 

This part is very similar to the anonymous block, it has three parts. The executable part is mandatory whereas the declarative and exception-handling parts are optional. The executable part must contain at least one executable statement.

1) Declarative part

In this part variables, constants,cursors, etc can be declared. Unlike an anonymous block, a declaration part of a procedure does not start with the DECLARE keyword.

2) Executable part

This part contains one or more statements that implement specific business logic.

3) Exception-handling part

This optional part contains all the code required to handle exceptions.

Example of basic procedure(Executable in Oracle live sql):

create table employees as 
select * from hr.employees;

create table departments as
select * from hr.departments; 

Table created.

Table created.

select * from employees fetch first 10 rows only;

EMPLOYEE_ID
FIRST_NAME
LAST_NAME
EMAIL
PHONE_NUMBER
HIRE_DATE
JOB_ID
SALARY
COMMISSION_PCT
MANAGER_ID
DEPARTMENT_ID
100
Steven
King
SKING
515.123.4567
17-JUN-03
AD_PRES
24000
- 
- 
90
101
Neena
Kochhar
NKOCHHAR
515.123.4568
21-SEP-05
AD_VP
17000
- 
100
90
102
Lex
De Haan
LDEHAAN
515.123.4569
13-JAN-01
AD_VP
17000
- 
100
90
103
Alexander
Hunold
AHUNOLD
590.423.4567
03-JAN-06
IT_PROG
9000
- 
102
60
104
Bruce
Ernst
BERNST
590.423.4568
21-MAY-07
IT_PROG
6000
- 
103
60


select * from departments fetch first 10 rows only;

DEPARTMENT_ID
DEPARTMENT_NAME
MANAGER_ID
LOCATION_ID
10
Administration
200
1700
20
Marketing
201
1800
30
Purchasing
114
1700
40
Human Resources
203
2400
50
Shipping
121
1500

create or replace procedure p_sal_increment(p_dep_name departments.department_name%type, p_increment in number) 
as

v_dep_name departments.department_name%type;

cursor cur_employees 
is 
select employee_id from employees emp inner join departments dep on emp.department_id=dep.department_id where dep.department_name=p_dep_name;
----fetching all eligible employees for increment 
begin

select department_name into v_dep_name from departments where department_name=p_dep_name;
-----validating user input, if department name is not valid an exception will be thrown
for v_cur in cur_employees
loop
    update employees set salary=salary+salary*(p_increment/100) where employee_id=v_cur.employee_id;
end loop;
----updates the employees table with new salary
exception
    when others then
    dbms_output.put_line('please check the exception error message'||sqlerrm||'\n error code :'||sqlcode);
    
commit;

end p_sal_increment;

Before execution:
select employee_id,department_name,salary from employees emp inner join departments dep on emp.department_id=dep.department_id where dep.department_name='Marketing'
     

EMPLOYEE_ID
DEPARTMENT_NAME
SALARY
201
Marketing
13000
202
Marketing
6000


Execution:
exec p_sal_increment('Marketing',5);-- 5% increment will be done for Marketing department.
 
After Execution:
select employee_id,department_name,salary from employees emp inner join departments dep on emp.department_id=dep.department_id where dep.department_name='Marketing'
    

EMPLOYEE_ID
DEPARTMENT_NAME
SALARY
201
Marketing
13650
202
Marketing
6300