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

 1/*
 2REM
 3REM q02.sp
 4REM
 5REM Get projects supplied by suppliers 
 6REM having status greater than 'Houston' city suppliers
 7REM
 8*/
 9
10DECLARE
11
12  jJN Projects.JN%TYPE;
13
14  no_selection EXCEPTION;
15
16  CURSOR c1 IS
17    SELECT DISTINCT P.jn
18    FROM Suppliers S, Projects P, Shipments SPJ
19    WHERE S.sn = SPJ.sn
20      AND P.jn = SPJ.jn
21      AND status > (SELECT status
22                    FROM Suppliers
23                    WHERE city = 'Houston');
24
25BEGIN
26
27  DBMS_OUTPUT.PUT_LINE('Projects supplied by suppliers having status');
28  DBMS_OUTPUT.PUT_LINE('greater than Houston city suppliers');
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/