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

 1DECLARE
 2
 3 cN Customer.Customername%TYPE;
 4 sT Customer.Street%TYPE;
 5 cU Customer.Customercity%TYPE;
 6
 7 no_selection EXCEPTION;
 8
 9  CURSOR c1 IS
10    SELECT *
11    FROM Customer;
12
13BEGIN
14
15  DBMS_OUTPUT.PUT_LINE('The Customer Table');
16  DBMS_OUTPUT.PUT_LINE('---------------------------------------');
17  DBMS_OUTPUT.PUT_LINE('CustomerName   '||'Street      '||'CustomerCity');
18  DBMS_OUTPUT.PUT_LINE('---------------'||'------------'||'------------');
19   
20  OPEN c1;
21
22  LOOP
23    FETCH c1 INTO cN, sT, cU;
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(cN,15,' ')||
31	                   RPAD(sT,12,' ')||
32			   RPAD(cU,15,' '));
33    ELSE
34      EXIT;
35    END IF;
36  END LOOP;
37    
38  CLOSE c1;
39
40EXCEPTION
41  WHEN no_selection THEN
42  DBMS_OUTPUT.PUT_LINE('Not a single tuple met the selection criteria.');
43
44END;
45/