Yurttas/PL/DBL/oracle/F/02/SPJ/q01.sp

 1/*
 2REM
 3REM q01.sp
 4REM
 5REM Get Suppliers who do not supply 'Houston' city projects
 6REM
 7*/
 8
 9DECLARE
10
11  sSN Suppliers.sn%TYPE;
12
13  no_selection EXCEPTION;
14
15  CURSOR c1 IS
16    SELECT sn
17    FROM Suppliers
18    WHERE sn NOT IN (SELECT DISTINCT S.sn
19                     FROM Suppliers S, Projects P, Shipments SPJ
20                     WHERE S.sn = SPJ.sn
21                       AND P.jn = SPJ.jn
22                       AND P.city = 'Houston');
23
24BEGIN
25
26  DBMS_OUTPUT.PUT_LINE('Suppliers who do not supply Houston');
27  DBMS_OUTPUT.PUT_LINE('city projects');
28  DBMS_OUTPUT.PUT_LINE('-------------------------------------------');
29  DBMS_OUTPUT.PUT_LINE('SN');
30  DBMS_OUTPUT.PUT_LINE('--');
31   
32  OPEN c1;
33
34  LOOP
35    FETCH c1 INTO sSN;
36
37    IF c1%ROWCOUNT = 0 THEN
38      RAISE no_selection;
39    END IF;
40
41    IF c1%FOUND THEN
42      DBMS_OUTPUT.PUT_LINE(sSN);
43    ELSE
44      EXIT;
45    END IF;
46  END LOOP;
47
48  CLOSE c1;
49
50EXCEPTION
51  WHEN no_selection
52  THEN
53    DBMS_OUTPUT.PUT_LINE('Not a single tuple met the selection criteria');
54
55END;
56/