Yurttas/PL/DBL/oracle/F/02/Bank/q08.sp
Jump to navigation
Jump to search
1CREATE OR REPLACE
2PROCEDURE q08 IS
3
4 cN Customer.Customername%TYPE;
5 cC Customer.Customercity%TYPE;
6
7 no_selection EXCEPTION;
8
9 CURSOR c1 IS
10 SELECT Customer.customername, customercity
11 FROM Client, Customer
12 WHERE Client.customername = Customer.customername
13 AND bankername = 'Johnson';
14
15BEGIN
16
17 DBMS_OUTPUT.PUT_LINE('All clients of the banker Johnson, as well as');
18 DBMS_OUTPUT.PUT_LINE('the cities in which these clients live.');
19 DBMS_OUTPUT.PUT_LINE('---------------------------');
20 DBMS_OUTPUT.PUT_LINE('CustomerName '||'Customercity');
21 DBMS_OUTPUT.PUT_LINE('---------------------------');
22
23 OPEN c1;
24
25 LOOP
26 FETCH c1 INTO cN, cC;
27
28 IF c1%ROWCOUNT = 0 THEN
29 RAISE no_selection;
30 END IF;
31
32 IF c1%FOUND THEN
33 DBMS_OUTPUT.PUT_LINE(RPAD(cN, 15,' ')||
34 RPAD(cC, 15,' '));
35 ELSE
36 EXIT;
37 END IF;
38 END LOOP;
39
40 CLOSE c1;
41
42EXCEPTION
43 WHEN no_selection
44 THEN
45 DBMS_OUTPUT.PUT_LINE('There are no clients of the banker Johnson.');
46
47END;
48/