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/