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

 1DECLARE
 2
 3 bN Branch.Branchname%TYPE;
 4 aSE Branch.Assets%TYPE;
 5 bC Branch.Branchcity%TYPE;
 6 
 7 no_selection EXCEPTION;
 8
 9  CURSOR c1 IS
10    SELECT *
11    FROM Branch;
12
13BEGIN
14
15  DBMS_OUTPUT.PUT_LINE('The Branch Table');
16  DBMS_OUTPUT.PUT_LINE('--------------------------------');
17  DBMS_OUTPUT.PUT_LINE('BranchName   '||'Assets   '||'BranchCity');
18  DBMS_OUTPUT.PUT_LINE('-------------'||'---------'||'----------');
19   
20  OPEN c1;
21
22  LOOP
23    FETCH c1 INTO bN, aSE, bC;
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(aSE,9,' ')||
32			   RPAD(bC,10,' '));
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/