​A selection of topics on IT and its application to finance. 
​Send me your comments, questions ​or suggestions by clicking


                                                  A general overview of PL/SQL tables     
PL/SQL tables are useful in that they can be used to store large quantities of bulk data and can be passed to functions and procedures as parameters and returned from functions. They can be based on many types of variables such as:- 

1) x number; 
   type pltab is table of x%type; /* Based on a simple variable */ 

2) type pltab is table of testtab.col1%type /* based on a table column */ 

3) type pltab is table of testtab%rowtype  /* Based on a table row */ 

4) type plrec is record (x number,y date, z varchar2(10)); 
   type pltab is table of plrec            /* Based on a record */ 

We can return a PL/SQL table from a function 
5) function pl_func(x IN number) return pltab as ... 

To reference an element of a single type PL/SQL table use an index number 

6) for i in 1..100 
  pltab(i) := salary *1.1 
   end loop; 

For a table of records use 
7) pltab(i).x := salary + bonus; 

For functions that return PL/SQL tables 
8) declare 
       type pltab is table of testtab%rowtype index by binary_integer; 
       function plfunc(max_num IN integer) return pltab as 
tt pltab; 
return tt; 

if plfunc(90)(3).y = 'ABC' then ... end if; 

Some ways to get data into PL/SQL tables are:- 

9)  select emp_name into pltab(1) from emptab where emp_no = 999; 

10) cursor c1 is select * from testtab; 
    open c1; 
        i := i + 1; 
        fetch c1 into testtab(i); 
    end loop; 

11) for c1_rec in (select * from testtab) 
        n := n + 1; 
        pltab(n).x = c1_rec.x; 
    end loop; 

In embedded C, PL/SQL tables have only one column and give array-like access 

12)     type pltab is tbale of number index by binary_integer; 
        mytab pltab; 
        pltab(8) = 9.99; 

PL/SQL tables have the following useful attributes associated with them 

EXISTS            - if pltab(i).exists then ... end if; 
COUNT            - if pltab.count > 0 then ... end if; 
FIRST/LAST   - if pltab.first = 1 then ... end if; (NULL if table is empty) 
PRIOR/NEXT  - if pltab.prior(2) = 2 then ... end if; 
DELETE          - pltab.delete(1,10) ( empty items 1 through 10 ) 
                          - pltab.delete(3)    ( empty item 3 only) 
                          - pltab.delete     ( empty whole table )