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

 1CREATE OR REPLACE
 2PROCEDURE q111 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 ((SELECT branchname
12                      FROM Branch
13                      WHERE branchcity = 'Brooklyn')
14                     MINUS
15                     (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/