Yurttas/PL/DBL/oracle/F/02/Bank/a04.sp
Jump to navigation
Jump to search
1CREATE OR REPLACE
2PROCEDURE a04 IS
3
4 aV NUMBER;
5
6 no_selection EXCEPTION;
7
8 CURSOR c1 IS
9 SELECT AVG(balance)
10 FROM Depositor, Customer
11 WHERE Depositor.customername = Customer.customername
12 AND Customer.customercity = 'Harrison'
13 GROUP BY Depositor.customername
14 HAVING COUNT(accountnumber) >= 3;
15
16BEGIN
17
18 DBMS_OUTPUT.PUT_LINE('The Average Balance of all Depositors who live in');
19 DBMS_OUTPUT.PUT_LINE('Harrison and have at least 3 accounts.');
20 DBMS_OUTPUT.PUT_LINE('--------------------');
21 DBMS_OUTPUT.PUT_LINE('Average');
22 DBMS_OUTPUT.PUT_LINE('-------');
23
24 OPEN c1;
25
26 LOOP
27 FETCH c1 INTO aV;
28
29 IF c1%ROWCOUNT = 0 THEN
30 RAISE no_selection;
31 END IF;
32
33 IF c1%FOUND THEN
34 DBMS_OUTPUT.PUT_LINE(aV);
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('No Depositors in Harrison have at least 3 accounts.');
46
47END;
48/