Yurttas/PL/DBL/oracle/F/02/Bank/o02.sp
Jump to navigation
Jump to search
1CREATE OR REPLACE
2PROCEDURE o02 IS
3
4 bN Borrower.Branchname%TYPE;
5 lN Borrower.Loannumber%TYPE;
6 cN Borrower.Customername%TYPE;
7 aM Borrower.Amount%TYPE;
8
9 no_selection EXCEPTION;
10
11 CURSOR c1 IS
12 SELECT *
13 FROM Borrower
14 ORDER BY amount DESC, loannumber ASC;
15
16BEGIN
17
18 DBMS_OUTPUT.PUT_LINE('The entire borrower relation in descending order of amount,');
19 DBMS_OUTPUT.PUT_LINE('if several loans have the same amount, order them');
20 DBMS_OUTPUT.PUT_LINE('in ascending order by lonanumber.');
21 DBMS_OUTPUT.PUT_LINE('--------------------------------------------------');
22 DBMS_OUTPUT.PUT_LINE('BranchName '||'LoanNumber '||'CustomerName '||'Amount ');
23 DBMS_OUTPUT.PUT_LINE('--------------------------------------------------');
24
25 OPEN c1;
26
27 LOOP
28 FETCH c1 INTO bN, lN, cN, aM;
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(bN, 13,' ')||
36 RPAD(lN, 13,' ')||
37 RPAD(cN, 15,' ')||
38 RPAD(aM, 10,' '));
39 ELSE
40 EXIT;
41 END IF;
42 END LOOP;
43
44 CLOSE c1;
45
46EXCEPTION
47 WHEN no_selection
48 THEN
49 DBMS_OUTPUT.PUT_LINE('There are no customers in the Borrower Table.');
50
51END;
52/