how to create PL/SQL stored procedure

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_IDFIRST_NAMELAST_NAMEEMAILPHONE_NUMBERHIRE_DATEJOB_IDSALARYCOMMISSION_PCTMANAGER_IDDEPARTMENT_ID


100StevenKingSKING515.123.456717-JUN-03AD_PRES24000- - 90


101NeenaKochharNKOCHHAR515.123.456821-SEP-05AD_VP17000- 10090


102LexDe HaanLDEHAAN515.123.456913-JAN-01AD_VP17000- 10090


103AlexanderHunoldAHUNOLD590.423.456703-JAN-06IT_PROG9000- 10260


104BruceErnstBERNST590.423.456821-MAY-07IT_PROG6000- 10360




select * from departments fetch first 10 rows only;



DEPARTMENT_IDDEPARTMENT_NAMEMANAGER_IDLOCATION_ID


10Administration2001700


20Marketing2011800


30Purchasing1141700


40Human Resources2032400


50Shipping1211500







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_IDDEPARTMENT_NAMESALARY


201Marketing13000


202Marketing6000




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_IDDEPARTMENT_NAMESALARY


201Marketing13650


202Marketing6300