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

 1CREATE OR REPLACE
 2PROCEDURE q02 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    WHERE amount>1200;
15
16BEGIN
17
18  DBMS_OUTPUT.PUT_LINE('All tuples from the borrower relation where amount');
19  DBMS_OUTPUT.PUT_LINE('borrowed is more than $1200.');
20  DBMS_OUTPUT.PUT_LINE('--------------------------------------------------');
21  DBMS_OUTPUT.PUT_LINE('BranchName   '||'LoanNumber   '||'CustomerName   '||'Amount   ');
22  DBMS_OUTPUT.PUT_LINE('--------------------------------------------------');
23   
24  OPEN c1;
25
26  LOOP
27    FETCH c1 INTO bN, lN, cN, aM;
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(bN, 13,' ')||
35			   RPAD(lN, 13,' ')||
36			   RPAD(cN, 15,' ')||
37			   RPAD(aM, 10,' '));
38    ELSE
39      EXIT;
40    END IF;
41  END LOOP;
42    
43  CLOSE c1;
44
45EXCEPTION
46  WHEN no_selection
47  THEN
48    DBMS_OUTPUT.PUT_LINE('There are no customers in the Borrower Table with a loan > $1200');
49
50END;
51/