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

 1/*
 2REM
 3REM q10.sp
 4REM
 5REM Find the maker of the printer with the fastest processor
 6REM among all those PC's that have the smallest amount of RAM.
 7REM
 8*/
 9
10CREATE OR REPLACE
11VIEW SmallRam (model,ram)
12  AS (SELECT model, ram
13      FROM PC
14      WHERE ram IN (SELECT MIN (ram)
15                    FROM  PC));
16
17CREATE OR REPLACE
18PROCEDURE q10 IS
19
20  no_selection EXCEPTION;
21
22  CURSOR c2 IS 
23    SELECT P.maker
24    FROM Product P, PC C, SmallRam S
25    WHERE P.model = C.model
26      AND C.model = S.model
27      AND C.speed IN (SELECT MAX (speed)
28                      FROM PC, SmallRam S
29                      WHERE PC.model = S.model);
30
31  pMAKER Product.maker%TYPE;
32
33BEGIN
34
35  DBMS_OUTPUT.PUT_LINE('Find the maker of the color printer with the lowest price.');
36  DBMS_OUTPUT.PUT_LINE('-------------------------------------------');
37  DBMS_OUTPUT.PUT_LINE('MAKER');
38  DBMS_OUTPUT.PUT_LINE('-----');
39   
40  OPEN c2;
41
42  LOOP
43    FETCH c2 INTO pMAKER;
44
45    IF c2%ROWCOUNT = 0 THEN
46      RAISE no_selection;
47    END IF;
48
49    IF c2%FOUND THEN
50      DBMS_OUTPUT.PUT_LINE(pMAKER);
51    ELSE
52      EXIT;
53    END IF;
54  END LOOP;
55
56  CLOSE c2;
57
58EXCEPTION
59  WHEN no_selection
60  THEN
61    DBMS_OUTPUT.PUT_LINE('Not a single tuple met the selection criteria');
62
63END q10;
64/