Stored procedure:
==================
–Stored as a precompiled object
–Compile once and execute multiple times
–Provides reusability
–Procedure are explicitly executed by user
PROCEDURE
Function
Package
Trigger
2 Types of procedure
====================
1) Static procedure
–will not contain any arguments
–always display same output
2) Dynamic procedure
with argument variable
Procedure contains 2 parts:
- Procedure specification
- procedure body specification
create or replace procedure <name> (arg_variable IN datatype, arg variable2 datatype)
Is
CREATE [OR REPLACE] PROCEDURE proc_name [list of parameters]
AS|IS
Declaration section
BEGIN
Execution section
EXCEPTION
Exception section
END;
IS – marks the beginning of the body of the procedure
—————————————————————-
CREATE OR REPLACE PROCEDURE greetings
AS
BEGIN
dbms_output.put_line(‘Welcome to PLSQL Session!’);
END;
exec greetings;
—————————————————————-
Drop procedure procedure_name;
—————————————————————-
How to execute procedure:
exec greetings;
execute greeetings;
begin
greetings;
end;
/
——————————————————————
CREATE OR REPLACE PROCEDURE greetings1(p_name IN varchar2)
AS
BEGIN
dbms_output.put_line(‘Hello’||p_name);
END;
exec greetings1(‘Welcome to PLSQL’);
—————————————————————————
CREATE OR REPLACE PROCEDURE total1(N1 IN number,N2 IN number,total out number)
AS
BEGIN
total:=N1+N2;
END;
declare
x number;
begin
total1(7,8,x);
dbms_output.put_line(‘Total: ‘||x);
end;
CREATE OR REPLACE PROCEDURE total1(N1 IN number,N2 IN number)
AS
total1 number;
BEGIN
total1:=N1+N2;
dbms_output.put_line(‘Total: ‘||total1);
END;
exec total1(41,57);
—————————————————————————————-
create procedure INOUT_Multiplication(x IN OUT number)
As
begin
x:=x*5;
end;
declare
x number;
begin
x:=6;
INOUT_Multiplication(x);
dbms_output.put_line(‘multiplication: ‘||x);
end;
create or replace procedure total_salary(in_emp_id in number)
is
v_salary number(10);
begin
select salary+(salary*nvl(commission_pct,0)) into v_salary from
employees where employee_id=in_emp_id;
dbms_output.put_line(‘The Total salary of employee ‘|| in_emp_id ||’ is : ‘||v_salary);
end;
execute total_salary(165);
—————————————————————————————
create or replace procedure pro1
(emp_no in number,temp out employees%rowtype)
is
begin
select * into temp from employees where employee_id=emp_no;
end;
declare
emp_no number;
temp employees%rowtype;
begin
emp_no:=105;
pro1(emp_no,temp);
dbms_output.put_line(‘The employee details: ‘||temp.employee_id);
dbms_output.put_line(‘The employee details: ‘||temp.first_name);
dbms_output.put_line(‘The employee details: ‘||temp.salary);
end;
Data dictionary table:
User_procedures;
User_objects;
user_source;
select * from all_procedures where owner=’HR’;
select text from all_source where owner=’HR’ and type=’PROCEDURE’ and name=’ADD_JOB_HISTORY’;
————————————————————
Procedure with Cursor:
=====================
create or replace PROCEDURE get_employees
AS
emp_first_name employees.first_name%type;
emp_salary employees.salary%TYPE;
CURSOR c1 IS
SELECT first_name,salary FROM employees where rownum <=3;
BEGIN
open c1;
LOOP
FETCH c1 INTO emp_first_name, emp_salary;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(emp_first_name);
end loop;
close c1;
end;
create or replace procedure get_employees(in_dept_id IN employees.department_id%type)
is
v_first_name employees.first_name%type;
v_salary employees.salary%type;
cursor c1 is select first_name,salary from employees where department_id=in_dept_id;
begin
open c1;
loop
fetch c1 into v_first_name, v_salary;
exit when c1%notfound;
DBMS_OUTPUT.PUT_LINE(v_first_name);
DBMS_OUTPUT.PUT_LINE(v_salary);
end loop;
close c1;
end;
exec get_employees(60);
———————————————————————–
Write a procedure (oracle plsql) to do any one of the following:
(a) update the table course and set the fee of the input course name equal to fee of java course.
(b) insert a new row for given input course and set the fee lowest of all courses available in the table. Condition is: do (a) if the input course name is already present in the table otherwise do (b) if the input course name is not in the table.
create table course(cid number primary key, cname varchar2(100), duration number, fee number);
insert into course (CID, CNAME, DURATION, FEE)
values (101, ‘java’, 30, 13000);
insert into course (CID, CNAME, DURATION, FEE)
values (102, ‘c’, 20, 5000);
insert into course (CID, CNAME, DURATION, FEE)
values (104, ‘oracle’, 20, 20000);
insert into course (CID, CNAME, DURATION, FEE)
values (105, ‘python’, 20, 30000);
insert into course (CID, CNAME, DURATION, FEE)
values (106, ‘sql’, 20, 1000);
Commit;
create or replace procedure proc_CourseFeeUpdateTry(coursename in course.cname%type,
java_fee out number) is
n_fee number;
j_fee number;
l_course_name course.cname%type;
begin
begin
select c.cname
into l_course_name
from course c
where c.cname = coursename;
exception
when no_data_found then
null;
end ;
if l_course_name = coursename then
select t.fee into j_fee from course t where t.cname = ‘java’;
java_fee := j_fee;
update course t set t.fee = java_fee where t.cname = coursename;
dbms_output.put_line(‘course fee updated’); — course was not added just updted
else
dbms_output.put_line(sqlerrm || ‘-‘ || sqlcode);
select min(t.fee) into n_fee from course t;
java_fee := n_fee;
insert into course values (103, coursename, 40, java_fee); — leave message
dbms_output.put_line(‘new course added’);
end if;
commit;
end;
————————————————————————————————–
— insert statement in procedure
select * from customer;
drop table customer;
create table customer
(
cust_id number,
cust_name varchar2(30),
mobile number(10),
age number,
city_id number
) ;
create or replace procedure insert_cust
(in_cust_id number, in_cust_name varchar2,in_mobile_no number,in_age number,in_city_id number)
as
begin
insert into customer values( in_cust_id,in_cust_name,in_mobile_no,in_age,in_city_id);
COMMIT;
end;
select * from customer;
truncate table customer;
exec insert_cust(100,’NIC IT Academy’,7010080468,30,23);
exec insert_cust(101,’NIC IT Academy’,7010080468,30,23);
=========================================================================================
Name Null? Type
——— —– ————
CUST_ID NUMBER(8)
CUST_NAME VARCHAR2(40)
DOB DATE
MOBILE_NO VARCHAR2(20)
CITY VARCHAR2(4
select * From customer;
create or replace procedure proc_insert_cust
(in_cust_id number, in_cust_name varchar2,in_mobile_no varchar2,in_dob varchar2,in_city_id varchar2)
as
begin
insert into customer values( in_cust_id,in_cust_name,in_mobile_no,in_dob,in_city_id);
COMMIT;
end;
exec proc_insert_cust(100,’NIC IT Academy’,to_date(’12-12-1990′,’mm-dd-yyyy’),’+91-9090909090′,’Chennai’);
exec insert_cust(101,’NIC IT Academy’,7010080468,30,23);
–Function in PLSQL
A function is a named PL/SQL Block which is similar to a procedure. The major difference between PL/SQL function or procedure, function return always value where as procedure may or may not return value.
A function is the subprogram that returns a value when called. It is stored in database or declared within a block.
Functions can return more than one value via OUT parameter. Fuctions can accept default values
CREATE [OR REPLACE] FUNCTION function_name [(
parameter_1 [IN] [OUT] data_type,
parameter_2 [IN] [OUT] data_type,
parameter_N [IN] [OUT] data_type]
RETURN return_data_type
IS
–the declaration statements
BEGIN
— the executable statements
return return_value;
EXCEPTION
— the exception-handling statements
END;
—————————————————————————–
An IN parameter is a read-only parameter. If the function tries to change the value of the IN parameters, the compiler will issue an error message. You can pass a constant, literal, initialized variable, or expression to the function as the IN parameter.
An OUT parameter is a write-only parameter. The OUT parameters are used to return values back to the calling program. An OUT parameter is initialized to default value of its type when the function begins regardless of its original value before being passed to the function.
An IN OUT parameter is read and write parameter. It means the function reads value from an IN OUT parameter, change its value and return it back to the calling program.
The function must have at least one RETURN statement in the execution section. The RETURN clause in the function header specifies the data type of returned value.
————————————————————————————–
create or replace function f_count
return number
is
v_count number;
begin
select count(*) into v_count from employees;
return v_count;
end;
select f_count() from dual;
—————————————————————————————-
create or replace function f_count(v_department_id out number,v_count out number)
return number
is
v_count number;
v_department_id number;
begin
select department_id,count(*) into v_count from employees
where department_id is not null group by department_id;
return v_count;
end;
—————————————————————
create or replace function hello_function(p_name IN varchar2)
return varchar2
as
v_result varchar2(100);
begin
v_result:=’Hello ‘||p_name;
return v_result;
end hello_function;
select HELLO_FUNCTION(‘chandra’) from dual;
———————————————————————————–
create or replace function salary_hike(p_emp_id IN number)
return number
as
v_job employees.job_id%type;
v_sal employees.salary%type;
v_raise number(3,2);
v_new_sal number(6);
begin
select job_id,salary into v_job,v_sal from employees where employee_id=p_emp_id;
case
when v_job=’AD_VP’ then
IF v_sal < 20000 then v_raise:=0.50;
else v_raise:=0;
end IF;
when v_job=’SA_REP’ then
IF v_sal < 1500 then v_raise:=0.20;
else v_raise:=0;
end IF;
else
v_raise:=0;
end case;
if(v_raise >0) then
v_new_sal:=v_sal+v_sal*v_raise;
else
v_new_sal:=v_sal;
end if;
return v_new_sal;
end salary_hike;
select employee_id,first_name,salary,salary_hike(employee_id) from employees;
select salary_hike(103) from dual;
————————————————————————————–
employee_id IN
salary, >10000 10%
salary <10000 20%
create or replace function salary_incr(in_employee_id in number)
return number
is
v_salary number(10);
v_incr_salary number(10);
begin
select salary into v_salary from employees where employee_id=in_employee_id;
if v_salary >10000 then
v_incr_salary:=v_salary+(v_salary*0.1);
else
v_incr_salary:=v_salary+(v_salary*0.2);
end if;
return v_incr_salary;
end salary_incr;
select employee_id,salary,emp_fun(employee_id) from employees;
— alternative way
create or replace function salary_incr(in_employee_id in number)
return number
is
v_salary number(10);
begin
select case when salary >10000 then salary+(salary*0.1)
else salary+(salary*0.2) end into v_salary from employees where employee_id=in_employee_id;
return v_salary;
end salary_incr;
——————————————-
— write a PLSQL Function to to find given year is leap year or non leap year
create or replace function IS_LEAP_YEAR (nYr in number) return varchar2 is
v_day varchar2(2);
begin
select to_char(last_day(to_date( ’01-FEB-‘|| to_char(nYr), ‘DD-MON-YYYY’)), ‘DD’)
into v_day from dual;
if v_day = ’29’ then — if v_day = 29 then it must be a leap year, return TRUE
return ‘LEAP YEAR’;
else
return ‘NON LEAP YEAR’; — otherwise year is not a leap year, return false
end if;
end;
— =============================================
— write a PLSQL Function to select city of the given employee_id
CREATE OR REPLACE FUNCTION get_city(in_emp_id number)
return varchar2
as
result varchar2(50);
BEGIN
select l.city into result
from employees e, departments d, locations l
where e.department_id=d.department_id and d.location_id=l.location_id
and e.employee_id = in_emp_id;
return result;
END;
/
select employee_id,first_name,get_city(employee_id) from employees;
— Write a function to get employees details based on department_id
CREATE OR REPLACE FUNCTION fn_emp_per_dept
(f_dept_id IN NUMBER,f_emp_id OUT NUMBER,f_first_name OUT VARCHAR,f_last_name OUT VARCHAR,f_job_title OUT VARCHAR)
RETURN BOOLEAN
AS
BEGIN
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,JOB_TITLE
INTO f_emp_id,f_first_name,f_last_name,f_job_title
FROM EMPLOYEES INNER JOIN JOBS ON JOBS.JOB_ID = EMPLOYEES.JOB_ID
INNER JOIN DEPARTMENTS ON DEPARTMENTS.DEPARTMENT_ID = EMPLOYEES.DEPARTMENT_ID
WHERE EMPLOYEES.DEPARTMENT_ID = f_dept_id;
RETURN TRUE;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.PUT_LINE(‘Employee not found’);
RETURN FALSE;
END fn_emp_per_dept;
DECLARE
f_dept_id NUMBER;
f_emp_id NUMBER;
f_first_name VARCHAR(30) ;
f_last_name VARCHAR(30) ;
f_job_title VARCHAR(30);
f_return BOOLEAN;
BEGIN
f_dept_id := 10;
f_return := fn_emp_per_dept(f_dept_id,f_emp_id,f_first_name,f_last_name,f_job_title);
DBMS_OUTPUT.PUT_LINE(‘Employee_ID: ‘ || f_emp_id);
DBMS_OUTPUT.PUT_LINE(‘First Name: ‘ || f_first_name);
DBMS_OUTPUT.PUT_LINE(‘Last Name: ‘ || f_last_name);
DBMS_OUTPUT.PUT_LINE(‘Job: ‘ || f_job_title);
END;
/
———————————————————————————————————–
Function overloading:
=====================
create or replace function ADD_NUM(A NUMBER, B NUMBER) return number
IS
c number;
BEGIN
c:=A+B;
return c;
END;
create or replace function ADD_NUM(A NUMBER, B NUMBER, C NUMBER) return number
IS
d number;
BEGIN
d:=A+B+C;
return d;
END;
select add_num(5+10) from dual;
— How to over come this?
We have to create a package.
create or replace package pkg_test as
function area (a in number) return number;
function area (a in number, b in number) return number;
end;
/
create or replace package body pkg_test as
function area(a in number)
return number is
begin
return a*a;
end;
function area(a in number, b in number)
return number is
begin
return a*b;
end;
end;
/
begin
dbms_output.put_line(pkg_test.area(3, 4));
dbms_output.put_line(pkg_test.area(5));
end;
/
