PL/SQL Tables
 

PL/SQL table is used while writing like a batch process inside the PL/SQL.

When a stored procedure expects a customer number you can declare a variable of type number and send that as an argument when you are calling the procedure, you are good to go.

Say you want to pass set of cutomer numbers to a procedure then how we will do that? Using PL/SQL table we can pass multiple values as set of rows as one parameter.

Declaring a variable of type PL/SQL Table

TYPE var_name is TABLE OF data_type index by binary_integer;

TYPE var_name is TABLE OF data_type NOT NULL index by binary_integer;

TYPE cust_tab_typ IS TABLE OF customer%ROWTYPE index by BINARY_INTEGER;

TYPE cust_rec_typ IS RECORD ( cust_no number := 0, cust_name varchar(10) ) ;
TYPE customer_tab_typ IS TABLE OF cust_rec_typ INDEX BY BINARY_INTEGER;
 

Some functions used in pl/sql tables
pl_sql_table.Exists(i) - Checks whether we have a value for that index in the pl/sql tables.
pl_sql_table.Count -- gives the number of rows
pl_sql_table.Delete(3) --deletes the 3rd element
pl_sql_table.Delete(3, 10) --deletes element from 3 to 10
pl_sql_table.Delete -- deletes entire pl/sql table.
pl_sql_table.first -- gives 1 if there are any item exits else its null
pl_sql_table.last -- gives the last number of item in the pl/sql table

Tips
A PL/SQL table is nothing but an unbounded array. Each item has an index number associated.
It is not a database object, it just stays in the memory.
You cannot use sql against a pl/sql table.

Example
declare
  TYPE number_tab is TABLE OF number index by binary_integer;
  a number_tab;
  b number_tab;
begin
  a(1) := 10;
  a(2) := 20;
  a(3) := 30;
  dbms_output.put_line(a.last);
  dbms_output.put_line('a.first: ' || a(1));
  dbms_output.put_line('a.last: ' || a(2));
  dbms_output.put_line(nvl(b.first,0));
end;

Make a free website with Yola