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