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

 1/*
 2REM
 3REM q07.sp
 4REM
 5REM Count the number of shipments by each supplier to 'Austin'
 6REM city projects
 7REM
 8*/
 9
10
11DECLARE
12
13  sSN Suppliers.SN%TYPE;
14
15  cnt_ship NUMBER;
16
17  no_selection EXCEPTION;
18
19  CURSOR c1 IS
20    SELECT SPJ.sn, COUNT(*)
21    FROM Suppliers S, Projects P, Shipments SPJ
22    WHERE S.sn = SPJ.sn
23      AND P.jn = SPJ.jn
24      AND P.city = 'Austin'
25    GROUP BY SPJ.sn;
26
27BEGIN
28
29  DBMS_OUTPUT.PUT_LINE('No. of shipments by each supplier to Austin');
30  DBMS_OUTPUT.PUT_LINE('city projects');
31  DBMS_OUTPUT.PUT_LINE('-------------------------------------------');
32  DBMS_OUTPUT.PUT_LINE('SN'||'  '||'Count(Shipments)');
33  DBMS_OUTPUT.PUT_LINE('--'||'  '||'----------------');
34
35  OPEN c1;
36
37  LOOP
38    FETCH c1 INTO sSN, cnt_ship;
39
40    IF c1%ROWCOUNT =0 THEN
41      RAISE no_selection;
42    END IF;
43
44    IF c1%FOUND THEN
45      DBMS_OUTPUT.PUT_LINE(sSN||'  '||cnt_ship);
46    ELSE
47      EXIT;
48    END IF;
49  END LOOP;
50
51  CLOSE c1;
52
53EXCEPTION
54  WHEN no_selection
55  THEN
56    DBMS_OUTPUT.PUT_LINE('Not a single tuple met the selection criteria');
57
58END;
59/