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

 1/*
 2REM
 3REM q06.sp
 4REM
 5REM Find those manufacturers of at least two different
 6REM computers (PC's or laptops) with speeds of at least 133.
 7REM
 8*/
 9
10CREATE OR REPLACE
11PROCEDURE q06 IS
12
13  pMAKER products.model%TYPE;
14
15  no_selection EXCEPTION;
16
17  CURSOR c1 IS
18    SELECT maker
19    FROM Product
20    WHERE model IN ((SELECT P.model
21                     FROM Product P, PC
22                     WHERE P.model=PC.model
23                       AND PC.speed>=133)
24                    UNION
25                    (SELECT P.model
26                     FROM Product P, Laptop L
27                     WHERE P.model=L.model
28                       AND L.speed>=133) )
29    GROUP BY maker
30    HAVING COUNT(model)>=2;
31
32BEGIN
33
34  DBMS_OUTPUT.PUT_LINE('Find those manufacturers of at least two different');
35  DBMS_OUTPUT.PUT_LINE('computers -PC''s or laptops- with speeds of at least 133.');
36  DBMS_OUTPUT.PUT_LINE('-------------------------------------------');
37  DBMS_OUTPUT.PUT_LINE('MAKER');
38  DBMS_OUTPUT.PUT_LINE('-----');
39   
40  OPEN c1;
41
42  LOOP
43    FETCH c1 INTO pMAKER;
44
45    IF c1%ROWCOUNT = 0 THEN
46      RAISE no_selection;
47    END IF;
48
49    IF c1%FOUND THEN
50      DBMS_OUTPUT.PUT_LINE(pMAKER);
51    ELSE
52      EXIT;
53    END IF;
54  END LOOP;
55
56  CLOSE c1;
57
58EXCEPTION
59  WHEN no_selection
60  THEN
61    DBMS_OUTPUT.PUT_LINE('Not a single tuple met the selection criteria');
62
63END q06;
64/