Yurttas/PL/DBL/oracle/F/02/PC/q11.sp

 1/*
 2REM
 3REM q11.sp
 4REM
 5REM Find the average hard disk size of a PC for all those
 6REM manufacturers that make printers.
 7REM
 8*/
 9
10CREATE OR REPLACE
11PROCEDURE q11 IS
12
13  no_selection EXCEPTION;
14
15  CURSOR c1 IS
16    SELECT P.maker, AVG(hd) "avgs"
17    FROM Product P, PC
18    WHERE P.model = PC.model
19      AND P.maker IN (SELECT P.maker
20                      FROM Product P, Printer R
21                      WHERE P.model = R.model)
22    GROUP BY P.maker;
23
24  PCM c1%ROWTYPE;
25
26BEGIN
27
28  DBMS_OUTPUT.PUT_LINE('Average hard drive size of printer maker');
29  DBMS_OUTPUT.PUT_LINE('-------------------------------------------');
30  DBMS_OUTPUT.PUT_LINE('Mfgr  Size');
31  DBMS_OUTPUT.PUT_LINE('----  ----');
32
33  OPEN c1;
34
35  LOOP
36    FETCH c1 INTO PCM;
37
38    IF c1%ROWCOUNT = 0 THEN
39      RAISE no_selection;
40    END IF;
41
42    IF c1%FOUND THEN
43      DBMS_OUTPUT.PUT_LINE(PCM.maker||'     '||PCM."avgs");
44    ELSE
45      EXIT;
46    END IF;
47  END LOOP;
48
49  CLOSE c1;
50
51EXCEPTION
52  WHEN no_selection
53  THEN
54    DBMS_OUTPUT.PUT_LINE('Not a single tuple met the selection criteria');
55
56END q11;
57/