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/