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

 1CREATE OR REPLACE
 2PROCEDURE q070 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 NOT IN (SELECT customername
13                               FROM Borrower
14                               WHERE branchname = 'Perryridge');
15
16BEGIN
17
18  DBMS_OUTPUT.PUT_LINE('All customers of the Perryridge Branch who have an account by not a loan.');
19  DBMS_OUTPUT.PUT_LINE('------------');
20  DBMS_OUTPUT.PUT_LINE('CustomerName');
21  DBMS_OUTPUT.PUT_LINE('------------');
22   
23  OPEN c1;
24
25  LOOP
26    FETCH c1 INTO cN;
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    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 of the Perryridge Branch');
45    DBMS_OUTPUT.PUT_LINE(' who have an account but not a loan.');
46
47END;
48/