1/*
2REM
3REM q07.sp
4REM
5REM Find the manufacturer(s) of the computer(PC or laptop)
6REM with the highest available speed.
7REM
8*/
9
10CREATE OR REPLACE
11VIEW ProdSpeed(model, speed)
12 AS (SELECT model, speed
13 FROM PC
14 WHERE speed IN (SELECT MAX(speed)
15 FROM PC))
16 UNION
17 (SELECT model, speed
18 FROM Laptop
19 WHERE speed IN (SELECT MAX(speed)
20 FROM Laptop));
21
22CREATE OR REPLACE
23PROCEDURE q07 IS
24
25 CURSOR c1 IS
26 SELECT P.maker, S.speed
27 FROM Product P, ProdSpeed S
28 WHERE P.model=S.model
29 AND S.speed IN (SELECT MAX(speed)
30 FROM ProdSpeed);
31
32 no_selection EXCEPTION;
33
34 PCM c1%ROWTYPE;
35
36BEGIN
37
38 DBMS_OUTPUT.PUT_LINE('Manufacturer of the computer with');
39 DBMS_OUTPUT.PUT_LINE('the highest available speed.');
40 DBMS_OUTPUT.PUT_LINE('-------------------------------------------');
41 DBMS_OUTPUT.PUT_LINE('Mfgr Speed');
42 DBMS_OUTPUT.PUT_LINE('---- -----');
43
44 OPEN c1;
45
46 LOOP
47 FETCH c1 INTO PCM;
48
49 IF c1%ROWCOUNT = 0 THEN
50 RAISE no_selection;
51 END IF;
52
53 IF c1%FOUND THEN
54 DBMS_OUTPUT.PUT_LINE(PCM.maker||' '||PCM.speed);
55 ELSE
56 EXIT;
57 END IF;
58 END LOOP;
59
60 CLOSE c1;
61
62EXCEPTION
63 WHEN no_selection
64 THEN
65 DBMS_OUTPUT.PUT_LINE('Not a single tuple met the selection criteria');
66
67END q07;
68/