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

 1CREATE OR REPLACE
 2PROCEDURE q12 IS
 3
 4  cN Depositor.Customername%TYPE;
 5  
 6  no_selection EXCEPTION;
 7
 8  CURSOR c1 IS
 9   SELECT &1
10   FROM Borrower
11   WHERE branchname = '&2';
12
13BEGIN
14
15  DBMS_OUTPUT.PUT_LINE('All customers with an account');
16  DBMS_OUTPUT.PUT_LINE('at all branches located in Brooklyn.');
17  DBMS_OUTPUT.PUT_LINE('------------');
18  DBMS_OUTPUT.PUT_LINE('CustomerName');
19  DBMS_OUTPUT.PUT_LINE('------------');
20   
21  OPEN c1;
22
23  LOOP
24    FETCH c1 INTO cN;
25
26    IF c1%ROWCOUNT = 0 THEN
27      RAISE no_selection;
28    END IF;
29
30    IF c1%FOUND THEN
31      DBMS_OUTPUT.PUT_LINE(RPAD(cN, 15,' '));
32    ELSE
33      EXIT;
34    END IF;
35  END LOOP;
36    
37  CLOSE c1;
38
39EXCEPTION
40  WHEN no_selection
41  THEN
42    DBMS_OUTPUT.PUT_LINE('There are no customers with an account at all branches in Brooklyn');
43
44END;
45/