1/*
2REM
3REM q06.sp
4REM
5REM Get projects supplied either by 'Houston' city suppliers
6REM or projects located in 'Houston' city.
7REM
8REM
9*/
10
11
12DECLARE
13
14 jJN Projects.jn%TYPE;
15
16 no_selection EXCEPTION;
17
18 CURSOR c1 IS
19 SELECT DISTINCT P.jn
20 FROM Suppliers S, Projects P, Shipments SPJ
21 WHERE S.sn = SPJ.sn
22 AND P.jn = SPJ.jn
23 AND (S.city = 'Houston' OR P.City = 'Houston');
24
25BEGIN
26
27 DBMS_OUTPUT.PUT_LINE('Projects supplied either by Houston city suppliers');
28 DBMS_OUTPUT.PUT_LINE('or projects located in Houston city');
29 DBMS_OUTPUT.PUT_LINE('-------------------------------------------');
30 DBMS_OUTPUT.PUT_LINE('JN');
31 DBMS_OUTPUT.PUT_LINE('--');
32
33 OPEN c1;
34
35 LOOP
36 FETCH c1 INTO jJN;
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(jJN);
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/