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

 1CREATE OR REPLACE
 2PROCEDURE q071 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    MINUS 
13    (SELECT customername
14     FROM Borrower
15     WHERE branchname = 'Perryridge');
16
17BEGIN
18
19  DBMS_OUTPUT.PUT_LINE('All customers of the Perryridge Branch who have an account by not a loan.');
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 of the Perryridge Branch');
46    DBMS_OUTPUT.PUT_LINE(' who have an account but not a loan.');
47
48END;
49/