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

 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/