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