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

 1CREATE OR REPLACE
 2PROCEDURE q05 IS
 3
 4 cN Borrower.Customername%TYPE;
 5                           
 6 no_selection EXCEPTION;
 7
 8 CURSOR c1 IS
 9   SELECT customername
10   FROM Client
11   WHERE customername=bankername;
12
13BEGIN
14
15  DBMS_OUTPUT.PUT_LINE('All customers who have the same name as their personal banker.');
16  DBMS_OUTPUT.PUT_LINE('------------');
17  DBMS_OUTPUT.PUT_LINE('CustomerName');
18  DBMS_OUTPUT.PUT_LINE('------------');
19   
20  OPEN c1;
21
22  LOOP
23    FETCH c1 INTO cN;
24
25    IF c1%ROWCOUNT = 0 THEN
26      RAISE no_selection;
27    END IF;
28
29    IF c1%FOUND THEN
30      DBMS_OUTPUT.PUT_LINE(RPAD(cN, 15,' '));
31    ELSE
32      EXIT;
33    END IF;
34  END LOOP;
35    
36  CLOSE c1;
37
38EXCEPTION
39  WHEN no_selection
40  THEN
41    DBMS_OUTPUT.PUT_LINE('There are no customers with the same name as their personal banker.');
42
43END;
44/