Slip:-23
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.
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
