PLSQL Packages
Demonstration of working of PL/SQL Packages:
Aim: To create PL/SQL Package with following procedures and functions
1. Procedures:
a. Print Total Quantity Sales Summary Report(SalesId, Date, Quantity and Total Quantity)
b. Print Total Quantity Sales Summary Report by Date wise
2. Functions:
a. Return employee name who made maximum sales till date
b. Return product name soled maximum quantity till date
Procedure :
STEP-1: Create the package named as ‘Sales package’ consisting of two procedures and two functions as follows:
create or replace package sales_pkg as
PROCEDURE sales_sid_proc;
PROCEDURE sales_date_proc;
FUNCTION max_emp_func RETURN char;
FUNCTION max_prod_func RETURN char;
end sales_pkg;
/
create or replace package body sales_pkg as
procedure sales_sid_proc is
s_sid sales.sid%type;
s_date sales."date"%type;
s_qty sales.qty%type;
tqty sales.qty%type;
CURSOR salescur IS
select sid, "date", qty from sales order by sid, "date";
BEGIN
tqty:=0;
dbms_output.put_line('SID' || ' '|| 'DATE' || ' ' || 'QTY' || ' ' || 'TOTAL QTY');
dbms_output.put_line('---------' || ' '|| '--------' || ' ' || '-------' || ' ' || '---------');
OPEN salescur;
LOOP
FETCH salescur into s_sid, s_date, s_qty;
tqty:=tqty+s_qty;
EXIT WHEN salescur%notfound;
dbms_output.put_line(s_sid || ' '|| s_date || ' ' || s_qty || ' ' || tqty);
END LOOP;
CLOSE salescur;
end sales_sid_proc;
procedure sales_date_proc is
s_sid sales.sid%type;
s_date sales."date"%type;
s_qty sales.qty%type;
tqty sales.qty%type;
temp1 sales.sid%type;
temp2 sales.sid%type;
CURSOR salescur IS
select sid, "date", qty from sales order by "date";
BEGIN
tqty:=0;
dbms_output.put_line('SID' || ' '|| 'DATE' || ' ' || 'QTY' || ' ' || 'TOTAL QTY');
dbms_output.put_line('---------' || ' '|| '--------' || ' ' || '-------' || ' ' || '---------');
OPEN salescur;
LOOP
FETCH salescur into s_sid, s_date, s_qty;
tqty:=tqty+s_qty;
EXIT WHEN salescur%notfound;
dbms_output.put_line(s_sid || ' '|| s_date || ' ' || s_qty || ' ' || tqty);
END LOOP;
CLOSE salescur;
end sales_date_proc;
function max_emp_func return char is
max_emp varchar(20);
BEGIN
Select ename into max_emp from emp inner join sales on emp.eid=sales.eid where sales.qty=(select max(qty) from sales);
RETURN max_emp;
end max_emp_func;
FUNCTION max_prod_func RETURN char iS
max_prod varchar(20);
BEGIN
Select pname into max_prod from prod inner join sales on prod.pid=sales.pid where sales.qty=(select max(qty) from sales);
RETURN max_prod;
END max_prod_func;
END sales_pkg;
/
STEP-2: Call the procedures and functions from the above created package as follows:
set serveroutput on;
begin
dbms_output.put_line('Sales Summary Report');
sales_pkg.sales_sid_proc;
dbms_output.put_line('Sales Summary Report Date wise');
sales_pkg.sales_date_proc;
dbms_output.put_line('Employee who made highest sales: '||sales_pkg.max_emp_func);
dbms_output.put_line('The highest sold product is: '||sales_pkg.max_prod_func);
end;
/
