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/