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

 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/