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

 1CREATE OR REPLACE
 2PROCEDURE q04 IS
 3
 4  bN Borrower.Branchname%TYPE;
 5  cN Borrower.Customername%TYPE;
 6  
 7  no_selection EXCEPTION;
 8
 9  CURSOR c1 IS
10    SELECT branchname, customername
11    FROM Borrower;
12
13BEGIN
14
15  DBMS_OUTPUT.PUT_LINE('Customers and the branches from which they borrow.');
16  DBMS_OUTPUT.PUT_LINE('-------------------------');
17  DBMS_OUTPUT.PUT_LINE('BranchName   '||'CustomerName');
18  DBMS_OUTPUT.PUT_LINE('-------------------------');
19   
20  OPEN c1;
21
22  LOOP
23    FETCH c1 INTO bN, cN;
24
25    IF c1%ROWCOUNT = 0 THEN
26      RAISE no_selection;
27    END IF;
28
29    IF c1%FOUND THEN
30      DBMS_OUTPUT.PUT_LINE(RPAD(bN, 13,' ')||
31			   RPAD(cN, 15,' '));
32    ELSE
33      EXIT;
34    END IF;
35  END LOOP;
36    
37  CLOSE c1;
38
39EXCEPTION
40  WHEN no_selection
41  THEN
42    DBMS_OUTPUT.PUT_LINE('There are no customers in the Borrower Table.');
43
44END;
45/