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

 1CREATE OR REPLACE
 2PROCEDURE q090 IS
 3
 4  cN Borrower.Customername%TYPE;
 5  cC Customer.Customercity%TYPE;
 6  
 7  no_selection EXCEPTION;
 8
 9  CURSOR c1 IS
10    SELECT Borrower.customername, customercity
11    FROM Borrower, Customer
12    WHERE Borrower.customername = Customer.customername;
13
14BEGIN
15
16  DBMS_OUTPUT.PUT_LINE('All customers who have a loan at the bank,');
17  DBMS_OUTPUT.PUT_LINE('and the cities in which they live.');
18  DBMS_OUTPUT.PUT_LINE('---------------------------');
19  DBMS_OUTPUT.PUT_LINE('CustomerName   '||'Customercity');
20  DBMS_OUTPUT.PUT_LINE('---------------------------');
21   
22  OPEN c1;
23
24  LOOP
25    FETCH c1 INTO cN, cC;
26
27    IF c1%ROWCOUNT = 0 THEN
28      RAISE no_selection;
29    END IF;
30
31    IF c1%FOUND THEN
32      DBMS_OUTPUT.PUT_LINE(RPAD(cN, 15,' ')||
33			   RPAD(cC, 15,' '));
34    ELSE
35      EXIT;
36    END IF;
37  END LOOP;
38    
39  CLOSE c1;
40
41EXCEPTION
42  WHEN no_selection
43  THEN
44    DBMS_OUTPUT.PUT_LINE('There are no customers who have a loan.');
45
46END;
47/