/* CS 532 Project 2 */
/*Using PL/SQL to create an application software for managing a small*/
/*business(MB ) */
/*BY Wenxia Zheng */
set serveroutput on
/* PACKAGE SPECIFICATION */
create or replace package MB as
procedure show_employees ;
procedure show_suppliers ;
procedure show_products ;
procedure show_customers ;
procedure show_transactions ;
procedure show_log1 ;
procedure report_purchase(cust_id in transactions.cid%type );
procedure add_employees (ename in employees.ename%type,
address in employees.address%type,
tel in employees.telephone%type,
salary in employees.salary%type,
str_date in employees.start_date%type ) ;
procedure add_suppliers (sname in suppliers.sname%type,
city in suppliers.city%type,
tel in suppliers.telephone%type);
procedure add_products(pname in products.pname%type, qtonhand in products.qty_on_hand%type,
purprice in products.purchase_price%type,
salprice in products.sale_price%type ,
supplier in products.supplier%type ,
des in products.description%type) ;
procedure add_customers(cname in customers.cname%type,
address in customers.address%type,
tel in customers.telephone%type ) ;
procedure add_transactions (incid in transactions.cid%type,
inpid in transactions.pid%type,
ineid in transactions.eid%type ,
inqty in transactions.qty_purchased%type,
date in transactions.purchase_date%type);
procedure report_stats ;
procedure report_stats(prod_id in transactions.pid%type) ;
procedure report_profit;
end;
/
show errors
/*PACKAGE MB BODY*/
create or replace package body MB as
/**************************************************************/
/*Show table including following procedures: */
/*show_employees */
/*show_suppliers */
/*show_products */
/*show_customers */
/*show_transactions */
/*show_log1 */
/**************************************************************/
procedure show_employees is
cursor c is select * from employees;
c_rec c%rowtype;
begin
for c_rec in c loop
dbms_output.put_line( c_rec.eid || ' ' ||
c_rec.ename|| ' ' ||
c_rec.address||' ' ||
c_rec.telephone || ' ' ||
c_rec.salary || ' ' ||
c_rec.start_date );
end loop;
end;
procedure show_suppliers is
cursor c is select * from suppliers;
c_rec c%rowtype;
begin
for c_rec in c loop
dbms_output.put_line( c_rec.sid || ' ' ||
c_rec.sname || ' ' ||
c_rec.city ||' ' ||
c_rec.telephone );
end loop;
end;
procedure show_products is
cursor c is select *from products;
c_rec c%rowtype;
begin
for c_rec in c loop
dbms_output.put_line( c_rec.pid || ' ' ||
c_rec.pname || ' ' ||
c_rec.qty_on_hand ||' ' ||
c_rec.purchase_price || ' '||
c_rec.sale_price || ' ' ||
c_rec.supplier || ' ' ||
c_rec.description );
end loop;
end;
procedure show_customers is
cursor c is select * from customers;
c_rec c%rowtype;
begin
for c_rec in c loop
dbms_output.put_line( c_rec.cid || ' ' ||
c_rec.cname || ' '||
c_rec.address ||' ' ||
c_rec.telephone || ' ' ||
c_rec.number_of_visits );
end loop;
end;
procedure show_transactions is
cursor c is select * from transactions;
c_rec c%rowtype;
begin
for c_rec in c loop
dbms_output.put_line( c_rec.tid || ' '||
c_rec.cid || ' ' ||
c_rec.pid ||' ' ||
c_rec.eid || ' ' ||
c_rec.qty_purchased || ' ' ||
c_rec.total_price || ' ' ||
c_rec.purchase_date );
end loop;
end;
procedure show_log1 is
cursor c is select * from log1;
c_rec c%rowtype;
begin
for c_rec in c loop
dbms_output.put_line( c_rec.who || ' ' ||
c_rec.when1 || ' ' ||
c_rec.what1 );
end loop;
end;
/*****************************************************************/
/*inserting tuple into tables including following procedures: */
/*add_employees(...) inserting tuples into employees table */
/*add_suppliers(...) inserting tuples into suppliers table */
/*add_products(...) inserting tuples into products table */
/*add_customers(...) inserting tuples into customers table */
/*add_transactions(...) inserting tuples into transactions table */
/*****************************************************************/
/*Insert tuple into table employees*/
/*Check ename value, if it is NULL print out the problem message */
/*after tuple is inserted into table, adding tuple into log1*/
procedure add_employees(ename in employees.ename%type,
address in employees.address%type,
tel in employees.telephone%type,
salary in employees.salary%type,
str_date in employees.start_date%type ) is
begin
if(ename is NULL) then
dbms_output.put_line('employee name can not be NULL');
else
insert into employees values(eid_seq.nextval,ename,address,tel,salary,str_date);
insert into log1 values(user, sysdate, ' A new employee is added to the employees table. ');
end if;
end;
/*Insert tuple into table suppliers, make sure sname is unique and NOT NULL*/
/*if sname exist,print out message.after insert tuple into table suppliers */
/*insert tuple into log1*/
procedure add_suppliers(sname in suppliers.sname%type,
city in suppliers.city%type,
tel in suppliers.telephone%type) is
cursor c is select * from suppliers ;
c_rec c%rowtype;
exist number :=0;
begin
for c_rec in c loop
if(c_rec.sname=sname) then
dbms_output.put_line('The sname already exist.');
exist:=1;
end if;
end loop ;
if(exist=0)then
insert into suppliers values(sid_seq.nextval,sname,city,tel);
insert into log1 values(user, sysdate, ' A new supplier is added to the suppliers table. ');
end if;
end;
/*Insert tuple into table products */
/*Check foreign key supplier ,if sname don't exist in table suppliers*/
/* ,print out message */
/*Check purchase_price < sale_price ,otherwise raise exception*/
/*After insert tuple into products,a tuple is added into log1 */
procedure add_products(pname in products.pname%type,
qtonhand in products.qty_on_hand%type,
purprice in products.purchase_price%type,
salprice in products.sale_price%type ,
supplier in products.supplier%type ,
des in products.description%type) is
invalid_price exception;
counter number;
begin
select count(*) into counter
from suppliers
where sname=supplier;
if((counter >0) and (purprice < salprice)) then
insert into products values(pid_seq.nextval,pname ,qtonhand,purprice,salprice,supplier,des);
insert into log1 values(user, sysdate, ' A new product is added to the products table. ');
elsif(counter=0) then
dbms_output.put_line('No such supplier name in our list.');
else
raise invalid_price;
end if;
exception
when invalid_price then
dbms_output.put_line('purchase_price must be less than sale_price ');
end;
/*Insert tuple into table customers*/
/*Check cname is not NULL,number_of_visits first are set to 0 and will be*/
/*changed along with the table transactions,after tuple is inserted,a new */
/*tuple is added to log1*/
procedure add_customers(cname in customers.cname%type,
address in customers.address%type,
tel in customers.telephone%type ) is
begin
if(cname is NULL) then
dbms_output.put_line('customers name can not be NULL');
else
insert into customers values(cid_seq.nextval,cname ,address,tel ,0);
insert into log1 values(user, sysdate, ' A new customer is added to the customers table. ');
end if ;
end;
/*Insert tuple into transactions*/
/*Check the foreign key cid,pid,eid,if they don't exist yet,print out message */
/*Make sure qty_purchased (to report bad content: archivehelp @ gmail)