What is PL/SQL tables.  When and why they are used.

PL/Sql table is a virtual array(single array) table defined during runtime.These tables help u in storing,manipulating in a blocks thus removing the need to connect to the database for information except for the first time and later can be updated into the database.

eg
Assume I have a table emp

emp
------
empno
empname
empsal
saldate
empcomm

Now I would like to give the employees an increment/commission and will create a procedure with some logic.

Create procedure empinc
as
type v_empsal  is table of emp%rowtype;
cursor as my_empsal is select * from emp
where empsal.empno=emp.empno;
begin
open my_emp
fetch my_emp into v_empsal;
exit when my_emp%notfound;

for i in v_empsal.first..v_empsal.last
loop
 if v_empsal is not null then
  v_empsal(i).empcomm:=v_empsal(i).empsal/100*10;
  v_empsal(i).empsal=  v_empsal(i).empsal
                                    +v_emp(i).empcomm;
 end if;
end loop;

 begin
 forall k in v_empsal.first..v_empsal.last
   insert into empsal vlaues v_empsal(k)
 end;

end;

In the above example as you see I didn't need to use any extra valrables nor define any data types. Both of these were as it was defined in the database and the array takes care of storing the values and insertion of the details in to the table was done by
the decalring the table type and the array with in it

Kiran Patil

Anyway you are using the cursor variable, so you can loop across the records and process one by one. The use of table type variable is not significant.

When you open the cursor, during first fetch use bulk collect and take the records into table variables declared. After this close the cursot. YOU NEED NOT LOOP across the cursor.

Table type variable is very useful when you need to select in the array.

Declare
type empno_list is table of emp.empno%;
empno_list_var empno_list ;
begin
select empno BULK COLLECT into empno_list_var from
scott.emp where empno > 100 ;

for v_empno in empno_list_var.first ..
empno_list_var.last
loop
...
;;;
---
end loop;

end;

Amit

Quick Links:
Do you have an Oracle Question?

Best regards,
Oracle Database, SQL, Application, Programming Tips