1CREATE OR REPLACE
2PROCEDURE a01 IS
3
4 bN Depositor.Branchname%TYPE;
5 aV NUMBER;
6
7 no_selection EXCEPTION;
8
9 CURSOR c1 IS
10 SELECT branchname, AVG(balance)
11 FROM Depositor
12 GROUP BY branchname;
13
14BEGIN
15
16 DBMS_OUTPUT.PUT_LINE('The Average Account Balance at Each Branch');
17 DBMS_OUTPUT.PUT_LINE('--------------------');
18 DBMS_OUTPUT.PUT_LINE('BranchName '||'Average');
19 DBMS_OUTPUT.PUT_LINE('-------------'||'-------');
20
21 OPEN c1;
22
23 LOOP
24 FETCH c1 INTO bN, aV;
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(aV,9,' '));
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/