Slip 12 - C) 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. 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)

Solution:

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  /