Yurttas/PL/DBL/oracle/F/02/PC/q06.sp
Jump to navigation
Jump to search
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/