1CREATE OR REPLACE
2PROCEDURE q110 IS
3
4 cN Depositor.Customername%TYPE;
5
6 no_selection EXCEPTION;
7
8 CURSOR c1 IS
9 SELECT DISTINCT S.customername
10 FROM Depositor S
11 WHERE NOT EXISTS
12 (SELECT branchname
13 FROM Branch
14 WHERE branchcity = 'Brooklyn'
15 AND branchname NOT IN (SELECT T.branchname
16 FROM Depositor T
17 WHERE S.customername = T.customername));
18
19BEGIN
20
21 DBMS_OUTPUT.PUT_LINE('All customers with an account');
22 DBMS_OUTPUT.PUT_LINE('at all branches located in Brooklyn.');
23 DBMS_OUTPUT.PUT_LINE('------------');
24 DBMS_OUTPUT.PUT_LINE('CustomerName');
25 DBMS_OUTPUT.PUT_LINE('------------');
26
27 OPEN c1;
28
29 LOOP
30 FETCH c1 INTO cN;
31
32 IF c1%ROWCOUNT = 0 THEN
33 RAISE no_selection;
34 END IF;
35
36 IF c1%FOUND THEN
37 DBMS_OUTPUT.PUT_LINE(RPAD(cN, 15,' '));
38 ELSE
39 EXIT;
40 END IF;
41 END LOOP;
42
43 CLOSE c1;
44
45EXCEPTION
46 WHEN no_selection THEN
47 DBMS_OUTPUT.PUT_LINE('There are no customers with an account at all branches in Brooklyn');
48
49END;
50/