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

 1CREATE OR REPLACE
 2PROCEDURE q03 IS
 3
 4  cN Client.Customername%TYPE;
 5  bN Client.Bankername%TYPE;
 6  
 7  no_selection EXCEPTION;
 8
 9  CURSOR c1 IS
10    SELECT *
11    FROM Client
12    WHERE customername=bankername;
13
14BEGIN
15
16  DBMS_OUTPUT.PUT_LINE('All customers who have the same name as their personal banker.');
17  DBMS_OUTPUT.PUT_LINE('-------------------------');
18  DBMS_OUTPUT.PUT_LINE('CustomerName   '||'Bankername');
19  DBMS_OUTPUT.PUT_LINE('-------------------------');
20   
21  OPEN c1;
22
23  LOOP
24    FETCH c1 INTO cN, bN;
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			   RPAD(bN, 13,' '));
33    ELSE
34      EXIT;
35    END IF;
36  END LOOP;
37    
38  CLOSE c1;
39
40EXCEPTION
41  WHEN no_selection
42  THEN
43    DBMS_OUTPUT.PUT_LINE('There are no customers with the same name as their personal banker.');
44
45END;
46/