Yurttas/PL/DBL/oracle/F/02/Bank/q03.sp
Jump to navigation
Jump to search
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/