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

 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/