Yurttas/PL/DBL/oracle/F/02/SPJ/q04.sp

 1/*
 2REM
 3REM q04.sp
 4REM
 5REM Get the supplier of the minimum of average shipments
 6REM
 7*/
 8
 9DECLARE
10
11  CURSOR c1 IS
12    SELECT sn, AVG(quantity) AS qty
13    FROM Shipments
14    GROUP BY sn;
15
16  supp c1%ROWTYPE;
17  min_supp c1%ROWTYPE;
18
19BEGIN
20
21  DBMS_OUTPUT.PUT_LINE('Suppliers of the minimum of average shipments');
22  DBMS_OUTPUT.PUT_LINE('-------------------------------------------');
23  DBMS_OUTPUT.PUT_LINE('SN'||'  '||'AVG(quantity)');
24  DBMS_OUTPUT.PUT_LINE('--'||'  '||'-------------');
25
26/* initializing the minimum value */
27
28  OPEN c1;
29
30  FETCH c1 INTO supp;
31  min_supp.qty := supp.qty;
32  min_supp.sn  := supp.sn;
33  CLOSE c1;
34
35  FOR supp IN c1
36  LOOP
37    IF supp.qty<=min_supp.qty THEN
38      min_supp.qty := supp.qty;
39      min_supp.sn  := supp.sn;
40    END IF;
41  END LOOP;
42
43  DBMS_OUTPUT.PUT_LINE(min_supp.SN||'  '||min_supp.qty);
44
45END;
46/