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;
/
Refer the following video for more details:
Refer the following video for more details:
