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

 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/