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/