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

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