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