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):
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 | 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 |
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 |
EMPLOYEE_ID | DEPARTMENT_NAME | SALARY |
---|---|---|
201 | Marketing | 13650 |
202 | Marketing | 6300 |
0 Comments
Post a Comment