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

 1CREATE OR REPLACE
 2PROCEDURE a03 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    HAVING AVG(balance)>200;   
14
15BEGIN
16
17  DBMS_OUTPUT.PUT_LINE('The Average Account Balance at Each Branch shre the');
18  DBMS_OUTPUT.PUT_LINE('Average Account Balance is more than $200');
19  DBMS_OUTPUT.PUT_LINE('--------------------');
20  DBMS_OUTPUT.PUT_LINE('BranchName   '||'Average');
21  DBMS_OUTPUT.PUT_LINE('-------------'||'-------');
22   
23  OPEN c1;
24
25  LOOP
26    FETCH c1 INTO bN, aV;
27
28    IF c1%ROWCOUNT = 0 THEN
29      RAISE no_selection;
30    END IF;
31
32    IF c1%FOUND THEN
33      DBMS_OUTPUT.PUT_LINE(RPAD(bN,13,' ')||
34	                   RPAD(aV,9,' '));
35    ELSE
36      EXIT;
37    END IF;
38  END LOOP;
39    
40  CLOSE c1;
41
42EXCEPTION
43  WHEN no_selection
44  THEN
45    DBMS_OUTPUT.PUT_LINE('Not a single tuple met the selection criteria.');
46
47END;
48/