23F-A4S (1)

for S in (select * from Suppliers order by S# ASC) loop DBMS_OUTPUT.PUT_LINE(S.S#||' '||S.NAME||' '||S.STATUS||' '||S.CITY); for P in (select P.P#, P.NAME, P.COLOR, P.WEIGHT, P.CITY from Parts P, SP T where S.S# = T.S# AND T.P# = P.P#) loop DBMS_OUTPUT.PUT_LINE(P.P#||' '||P.NAME||' '||P.COLOR||' '||P.WEIGHT||' '||P.CITY); end loop; end loop; end; / 3. Redo question 2 using a parameterized cursor that takes a supplier name. It should first prompt the user to enter a supplier name and then display the same information as in 2 just for the given customer. Use your own last name in the table to test this program. (15 marks) declare SNO Suppliers.S#%TYPE; NAME Suppliers.NAME%TYPE; STATUS Suppliers.STATUS%TYPE; CITY Suppliers.CITY%TYPE; cursor C (input Suppliers.NAME%TYPE) is select * from Suppliers where input = Suppliers.NAME; begin open C('&NAME'); loop fetch C into SNO, NAME, STATUS, CITY; EXIT when C%notfound; DBMS_OUTPUT.PUT_LINE(SNO||' '||NAME||' '||STATUS||' '||CITY); for P in ( select P.P#, P.NAME, P.COLOR, P.WEIGHT, P.CITY from Parts P, SP T where T.S# = SNO AND T.P# = P.P# ORDER BY P.P#) loop DBMS_OUTPUT.PUT_LINE(P.P#||' '||P.NAME||' '||P.COLOR||' '||P.WEIGHT||' '||P.CITY); END LOOP; end loop; close C; end; / SP S# P# QTY S1 P1 300 S1 P2 200 S1 P3 400 S1 P4 200 S1 P5 100 S1 P6 100 S2 P1 300 S2 P2 400 S3 P2 200 S4 P2 200 S4 P3 300 Suppliers S# NAME STATUS CITY S1 Smith 20 London S2 Jones 30 Paris S3 Blake 30 Paris S4 Last 20 London S5 Adams 30 Athens Parts P# NAME COLOR WEIGHT CITY P1 Nut Red 12.0 London P2 Bolt Green 17.0 Paris P3 Screw Blue 17.0 Oslo