Slip 23 - 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 procedure which will display details of products supplied by “Mr. Patil” 2) Write a cursor which will display wholesaler wise product details.(Use Parameterized cursor)

Solution:

SQL> create table wholesaler0 (wno int primary key,wname varchar(20),addr varchar(20),city varchar(20));

SQL> insert into wholesaler0 values(100,'aaa','ab chowk','pune');

SQL> create table product3 (pno int primary key,pname varchar(20),rate int);

SQL> insert into product3 values(001,'mmm',500);

SQL> create table wp0 (wno int ,pno int);

SQL> insert into wp0 values(100,001);

1)Write a procedure which will display details of products supplied by “Mr. Patil”

SQL> set serveroutput on

SQL> create or replace procedure p1(t in varchar)as cursor c1 is select wname,pname,rate from

  2  wholesaler,product,wp where wholesaler.wno=wp.wno  and product.pno=wp.pno and wname='patil';

  3  c c1%rowtype;

  4  begin

  5  open c1;

  6  dbms_output.put_line('wname'||''||'pname'||''||'rate');

  7  loop

  8   fetch c1 into c;

  9  exit when c1 %notfound;

 10  if(c.wname=t)then

 11   dbms_output.put_line(c.wname||''||c.pname||''||c.rate);

 12  end if;

 13  end loop;

 14  close c1;

 15  end;

 16  /

Procedure created.

SQL> begin

  2  p1('patil');

  3  end;

  4  /

wnamepnamerate

patilbooks30

2)Write a cursor which will display wholesaler wise product details.(Use Parameterized cursor)

SQL>  set serveroutput on

SQL>  declare

 2   cursor c2(yyyy wholesaler0.wname %type) is select wname,pname from wholesaler0,product3,wp0 where wholesaler0.wno=wp0.wno and product3.pno=wp0.pno order by wname;

  3   c c2 %rowtype;

  4  begin

  5  open c2('&yyyy');

  6  loop

  7  fetch c2 into c;

  8  exit when c2 %notfound;

  9   dbms_output.put_line(c.wname||''||c.pname);

 10  end loop;

 11  close c2;

 12  end;

 13  /

Enter value for yyyy: 500

old   5: open c2('&yyyy');

new   5: open c2('500');

aaammm

bbbnnn

cccppp