Question

1. Create an PL/SQL anonymous block. Using a “while” loop, display on the console those rows where a quantity of shipments for a project are greater than the average quantity of shipments for that project.

2. Repeat using a FOR loop.

// TABLE INFO : SHIPMENTS(SUPPLIERNO, PARTNO, PROJECTNO, QUANTITY, SHIPDATE, ARRIVEDATE)

Answer 1

Using While

DECLARE

s_sno shipments.supplierno%type;

s_pno shipments.partno%type;

s_projectno shipments.projectno%type;

s_quantity shipments.quantity%type;

s_shipdate shipments.shipdate%type;

s_arriveddate shipments.arriveddate%type;

CURSOR t1cursor IS

SELECT supplierno,partno,projectno,quantity,shipdate,arriveddate

FROM shipments

WHERE quantity>10000;

BEGIN

OPEN t1cursor;

WHILE t1cursor%FOUND LOOP

FETCH t1cursor INTO s_sno,s_pno,s_projectno,s_quantity,s_shipdate,s_arriveddate;

dbms_output.putline(s_sno || s_pno || s_projectno || s_quantity || s_shipdate || s_arriveddate);

ENDLOOP;

END;

Using FOR

DECLARE

s_sno shipments.supplierno%type;

s_pno shipments.partno%type;

s_projectno shipments.projectno%type;

s_quantity shipments.quantity%type;

s_shipdate shipments.shipdate%type;

s_arriveddate shipments.arriveddate%type;

CURSOR t1cursor IS

SELECT supplierno,partno,projectno,quantity,shipdate,arriveddate

FROM shipments

WHERE quantity>10000;

recordpresent shipmnents.quantity%type;

BEGIN

OPEN t1cursor;

FOR recordpresent IN t1cursor LOOP

FETCH t1cursor INTO s_sno,s_pno,s_projectno,s_quantity,s_shipdate,s_arriveddate;

dbms_output.putline(s_sno || s_pno || s_projectno || s_quantity || s_shipdate || s_arriveddate);

ENDLOOP;

END;