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

 1CREATE OR REPLACE
 2PROCEDURE q06 IS
 3
 4  cN Borrower.Customername%TYPE;
 5   
 6  no_selection EXCEPTION;
 7
 8  CURSOR c1 IS
 9    (SELECT customername
10     FROM Depositor
11     WHERE branchname = 'Perryridge')
12    UNION
13    (SELECT customername
14     FROM Borrower
15     WHERE branchname = 'Perryridge');
16
17BEGIN
18
19  DBMS_OUTPUT.PUT_LINE('Everyone that has an account or loan or both at the Perryridge Branch.');
20  DBMS_OUTPUT.PUT_LINE('------------');
21  DBMS_OUTPUT.PUT_LINE('CustomerName');
22  DBMS_OUTPUT.PUT_LINE('------------');
23   
24  OPEN c1;
25
26  LOOP
27    FETCH c1 INTO cN;
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(RPAD(cN, 15,' '));
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('There are no customers of the Perryridge Branch.');
46
47END;
48/