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

 1CREATE OR REPLACE
 2PROCEDURE o01 IS
 3
 4  cN Borrower.Customername%TYPE;
 5 
 6  no_selection EXCEPTION;
 7
 8  CURSOR c1 IS
 9    SELECT DISTINCT customername
10    FROM Borrower
11    WHERE branchname = 'Perryridge'
12    ORDER BY customername;
13
14BEGIN
15
16  DBMS_OUTPUT.PUT_LINE('All customers who have a loan at the Perryridge branch');
17  DBMS_OUTPUT.PUT_LINE('In alphabetical order');
18  DBMS_OUTPUT.PUT_LINE('---------------------');
19  DBMS_OUTPUT.PUT_LINE('CustomerName');
20  DBMS_OUTPUT.PUT_LINE('------------');
21   
22  OPEN c1;
23
24  LOOP
25    FETCH c1 INTO cN;
26
27    IF c1%ROWCOUNT = 0 THEN
28      RAISE no_selection;
29    END IF;
30
31    IF c1%FOUND THEN
32      DBMS_OUTPUT.PUT_LINE(RPAD(cN, 12,' '));
33    ELSE
34      EXIT;
35    END IF;
36  END LOOP;
37    
38  CLOSE c1;
39
40EXCEPTION
41  WHEN no_selection
42  THEN
43    DBMS_OUTPUT.PUT_LINE('There are no customers with a loan at the Perryridge Branch.');
44
45END;
46/