Yurttas/PL/DBL/oracle/F/02/Bank/q101.sp
Jump to navigation
Jump to search
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/