Yurttas/PL/DBL/oracle/F/02/Bank/q091.sp
Jump to navigation
Jump to search
1CREATE OR REPLACE
2PROCEDURE q091 IS
3
4 cN Borrower.Customername%TYPE;
5 cC Customer.Customercity%TYPE;
6
7 no_selection EXCEPTION;
8
9 CURSOR c1 IS
10 SELECT B.customername, C.customercity
11 FROM Borrower B, Customer C
12 WHERE B.customername = C.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/