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/