Yurttas/PL/DBL/oracle/F/02/Bank/a02.sp

 1CREATE OR REPLACE
 2PROCEDURE a02 IS
 3
 4  bN Depositor.Branchname%TYPE;
 5  cT NUMBER;
 6 
 7  no_selection EXCEPTION;
 8
 9  CURSOR c1 IS
10    SELECT branchname, COUNT(DISTINCT customername)
11    FROM Depositor
12    GROUP BY branchname;   
13
14BEGIN
15
16  DBMS_OUTPUT.PUT_LINE('The Number of Depositors for Each Branch');
17  DBMS_OUTPUT.PUT_LINE('------------------');
18  DBMS_OUTPUT.PUT_LINE('BranchName   '||'Count');
19  DBMS_OUTPUT.PUT_LINE('-------------'||'-----');
20   
21  OPEN c1;
22
23  LOOP
24    FETCH c1 INTO bN, cT;
25
26    IF c1%ROWCOUNT = 0 THEN
27      RAISE no_selection;
28    END IF;
29
30    IF c1%FOUND THEN
31      DBMS_OUTPUT.PUT_LINE(RPAD(bN,13,' ')||
32	                   RPAD(cT,8,' '));
33    ELSE
34      EXIT;
35    END IF;
36  END LOOP;
37    
38  CLOSE c1;
39
40EXCEPTION
41  WHEN no_selection
42  THEN
43    DBMS_OUTPUT.PUT_LINE('Not a single tuple met the selection criteria.');
44
45END;
46/