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

 1CREATE OR REPLACE
 2PROCEDURE q100 IS
 3
 4  cN Depositor.Customername%TYPE;
 5  
 6  no_selection EXCEPTION;
 7
 8  CURSOR c1 IS
 9    SELECT customername
10    FROM Depositor
11    WHERE branchname = 'Perryridge'
12      AND customername IN (SELECT customername
13                           FROM Borrower 
14                           WHERE branchname = 'Perryridge');
15
16BEGIN
17
18  DBMS_OUTPUT.PUT_LINE('All customers with both a loan and an account');
19  DBMS_OUTPUT.PUT_LINE('at the Perryridge branch.');
20  DBMS_OUTPUT.PUT_LINE('------------');
21  DBMS_OUTPUT.PUT_LINE('CustomerName');
22  DBMS_OUTPUT.PUT_LINE('------------');
23   
24  OPEN c1;
25
26  LOOP
27    FETCH c1 INTO cN;
28
29    IF c1%ROWCOUNT = 0 THEN
30      RAISE no_selection;
31    END IF;
32
33    IF c1%FOUND THEN
34      DBMS_OUTPUT.PUT_LINE(RPAD(cN, 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 customers with a loan and an account');
46    DBMS_OUTPUT.PUT_LINE('at the Perryridge branch.');
47
48END;
49/