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