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

 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/