Yurttas/PL/DBL/oracle/F/02/Bank/o01.sp
Jump to navigation
Jump to search
1CREATE OR REPLACE
2PROCEDURE o01 IS
3
4 cN Borrower.Customername%TYPE;
5
6 no_selection EXCEPTION;
7
8 CURSOR c1 IS
9 SELECT DISTINCT customername
10 FROM Borrower
11 WHERE branchname = 'Perryridge'
12 ORDER BY customername;
13
14BEGIN
15
16 DBMS_OUTPUT.PUT_LINE('All customers who have a loan at the Perryridge branch');
17 DBMS_OUTPUT.PUT_LINE('In alphabetical order');
18 DBMS_OUTPUT.PUT_LINE('---------------------');
19 DBMS_OUTPUT.PUT_LINE('CustomerName');
20 DBMS_OUTPUT.PUT_LINE('------------');
21
22 OPEN c1;
23
24 LOOP
25 FETCH c1 INTO cN;
26
27 IF c1%ROWCOUNT = 0 THEN
28 RAISE no_selection;
29 END IF;
30
31 IF c1%FOUND THEN
32 DBMS_OUTPUT.PUT_LINE(RPAD(cN, 12,' '));
33 ELSE
34 EXIT;
35 END IF;
36 END LOOP;
37
38 CLOSE c1;
39
40EXCEPTION
41 WHEN no_selection
42 THEN
43 DBMS_OUTPUT.PUT_LINE('There are no customers with a loan at the Perryridge Branch.');
44
45END;
46/