Yurttas/PL/DBL/oracle/F/02/SPJ/q04.sp
Jump to navigation
Jump to search
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/