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