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;
/
Watch video PLSQL Packages online without registration, duration hours minute second in high quality. This video was added by user Hassle Free Labs 23 April 2019, don't forget to share it with your friends and acquaintances, it has been viewed on our site 13 once and liked it people.