Yurttas/PL/DBL/oracle/F/02/Bank/q01.sp
Jump to navigation
Jump to search
1CREATE OR REPLACE
2PROCEDURE q01 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 branchname='Perryridge';
15
16BEGIN
17
18 DBMS_OUTPUT.PUT_LINE('The tuples of the Borrower relation where Branch=PerryRidge.');
19 DBMS_OUTPUT.PUT_LINE('--------------------------------------------------');
20 DBMS_OUTPUT.PUT_LINE('BranchName '||'LoanNumber '||'CustomerName '||'Amount ');
21 DBMS_OUTPUT.PUT_LINE('--------------------------------------------------');
22
23 OPEN c1;
24
25 LOOP
26 FETCH c1 INTO bN, lN, cN, aM;
27
28 IF c1%ROWCOUNT = 0 THEN
29 RAISE no_selection;
30 END IF;
31
32 IF c1%FOUND THEN
33 DBMS_OUTPUT.PUT_LINE(RPAD(bN, 13,' ')||
34 RPAD(lN, 13,' ')||
35 RPAD(cN, 15,' ')||
36 RPAD(aM, 10,' '));
37 ELSE
38 EXIT;
39 END IF;
40 END LOOP;
41
42 CLOSE c1;
43
44EXCEPTION
45 WHEN no_selection
46 THEN
47 DBMS_OUTPUT.PUT_LINE('There are no customers in the Borrower Table.');
48
49END;
50/