Yurttas/PL/DBL/oracle/F/02/Bank/a03.sp
Jump to navigation
Jump to search
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/