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

 1/*
 2REM
 3REM q05.sp
 4REM
 5REM Find those manufacturers that sell Laptops, but not PC's.
 6REM
 7*/
 8
 9CREATE OR REPLACE
10PROCEDURE q05 IS
11
12  pMAKER Product.maker%TYPE;
13
14  no_selection EXCEPTION;
15
16  CURSOR c1 IS
17    (SELECT P.maker
18     FROM Product P, Laptop L
19     WHERE P.model=L.model)
20    MINUS
21    (SELECT P.maker
22     FROM Product P, PC
23     WHERE P.model=PC.model);
24
25BEGIN
26
27  DBMS_OUTPUT.PUT_LINE('Manufacturers that sell laptops, but not PCs.');
28  DBMS_OUTPUT.PUT_LINE('Laptop manufactuers');
29  DBMS_OUTPUT.PUT_LINE('-------------------------------------------');
30  DBMS_OUTPUT.PUT_LINE('Manufacturer');
31  DBMS_OUTPUT.PUT_LINE('------------');
32   
33  OPEN c1;
34
35  LOOP
36    FETCH c1 INTO pMAKER;
37
38    IF c1%ROWCOUNT = 0 THEN
39      RAISE no_selection;
40    END IF;
41
42    IF c1%FOUND THEN
43      DBMS_OUTPUT.PUT_LINE(pMAKER);
44    ELSE
45      EXIT;
46    END IF;
47  END LOOP;
48
49  CLOSE c1;
50
51EXCEPTION
52  WHEN no_selection
53  THEN
54    DBMS_OUTPUT.PUT_LINE('Not a single tuple met the selection criteria');
55
56END;
57/