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

 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/