Yurttas/PL/DBL/oracle/F/02/Bank/Borrower-q-all.sp

From ZCubes Wiki
Jump to navigation Jump to search
 1DECLARE
 2
 3 bN Borrower.Branchname%TYPE;
 4 lN Borrower.Loannumber%TYPE;
 5 cN Borrower.Customername%TYPE;
 6 aM Borrower.Amount%TYPE;
 7
 8 no_selection EXCEPTION;
 9
10  CURSOR c1 IS
11    SELECT *
12    FROM Borrower;
13
14BEGIN
15
16  DBMS_OUTPUT.PUT_LINE('The Borrower Table');
17  DBMS_OUTPUT.PUT_LINE('-----------------------------------------------');
18  DBMS_OUTPUT.PUT_LINE('BranchName   '||'LoanNumber   '||'CustomerName   '||'Amount');
19  DBMS_OUTPUT.PUT_LINE('-------------'||'-------------'||'---------------'||'------');
20   
21  OPEN c1;
22
23  LOOP
24    FETCH c1 INTO bN, lN, cN, aM;
25
26    IF c1%ROWCOUNT = 0 THEN
27      RAISE no_selection;
28    END IF;
29
30    IF c1%FOUND THEN
31      DBMS_OUTPUT.PUT_LINE(RPAD(bN,13,' ')||
32	                   RPAD(lN,13,' ')||
33			   RPAD(cN,15,' ')||
34			   RPAD(aM,6,' '));
35    ELSE
36      EXIT;
37    END IF;
38  END LOOP;
39    
40  CLOSE c1;
41
42EXCEPTION
43  WHEN no_selection THEN
44  DBMS_OUTPUT.PUT_LINE('Not a single tuple met the selection criteria.');
45
46END;
47/