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

 1CREATE OR REPLACE
 2PROCEDURE q101 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    INTERSECT
13    (SELECT customername
14     FROM Borrower 
15     WHERE branchname = 'Perryridge');
16
17BEGIN
18
19  DBMS_OUTPUT.PUT_LINE('All customers with both a loan and an account');
20  DBMS_OUTPUT.PUT_LINE('at the Perryridge branch.');
21  DBMS_OUTPUT.PUT_LINE('------------');
22  DBMS_OUTPUT.PUT_LINE('CustomerName');
23  DBMS_OUTPUT.PUT_LINE('------------');
24   
25  OPEN c1;
26
27  LOOP
28    FETCH c1 INTO cN;
29
30    IF c1%ROWCOUNT = 0 THEN
31      RAISE no_selection;
32    END IF;
33
34    IF c1%FOUND THEN
35      DBMS_OUTPUT.PUT_LINE(RPAD(cN, 15,' '));
36    ELSE
37      EXIT;
38    END IF;
39  END LOOP;
40    
41  CLOSE c1;
42
43EXCEPTION
44  WHEN no_selection
45  THEN
46    DBMS_OUTPUT.PUT_LINE('There are no customers with a loan and an account');
47    DBMS_OUTPUT.PUT_LINE('at the Perryridge branch.');
48
49END;
50/