Slip
12
Consider
the following entities and their relationships.
Wholesaler
(w_no, w_name, address, city)
Product
(product_no, product_name, rate)
Relation
between Wholesaler and Product is Many to Many with quantity as descriptive
attribute.
Constraint:
Primary key, rate should be > 0.
Create
a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1)Write a function which will accept
wholesaler name from user and will display total number of items supplied by
him.
SQL>
set serveroutput on;
SQL>
create or replace function fun1(pn in varchar) return number as pm number;
2
begin
3
select sum(ws_product.no_of_item) into pm from ws,product,ws_product
where ws.wno=ws_product.wno
and product.pno=ws_product.pno and ws.wname='riya';
4 if
sql %found then
5
return(pm);
6 else
7
return null;
8 end
if;
9 end;
10 /
Function
created.
SQL>
begin
2
dbms_output.put_line('no_of_item-'||fun1('riya'));
3 end;
4 /
no_of_item-5
PL/SQL
procedure successfully completed.
2)Write a trigger which will fire
before insert or update on product having rate less than or equal to zero
(Raise user defined exception and give appropriate message)
SQL> set serveroutput
on
SQL> create or
replace trigger t1 before insert or update on product
2 for
each row
3 begin
4 if(:new.rate<=0)then
5 raise_application_error(-20001,'rate>0');
6 end
if;
7 end;
8 /
