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

 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/