Oracle PL/SQL Tables

PL/SQL tables

PL/SQL has two composite datatypes: TABLE and RECORD.

Objects of type TABLE are known as PL/SQL tables.

PL/SQL tables use a primary key to give you array-like access to rows. The number of rows in a PL/SQL table can increase dynamically.

The PL/SQL tables grows as new rows are added. PL/SQL tables can have one column and a primary key, neither of which can be named. The column can belong to any scalar type, but the primary key must belong to type BINARY_INTEGER.

A PL/SQL tables can consist of one simple datatype or be defined as a type of record and is sometimes referred to as an Index by table.

Rows in a PL/SQL table do not have to be contiguous.

Tables with simple datatypes can be populated as:

<variable>(<integer>) := <value>;

Tables with complex datatypes will need the columns populated individually as:

<variable>(<integer>).<column_name> := <value>;

Or from a cursor:

fetch <cursor_name> into <variable>(<integer>);

Example 1 of PL/SQL Table

Type my_first_table is table of varchar2(10)
Index by binary_integer;
Var_of_table my_first_table;
Var_of_table(1) := ‘hello world’;
Var_of_table(2) := ‘bye’;

Example 2 of PL/SQL Table

Type my_emp_table is table of emp%rowtype
Index by binary_integer:
Var_of_emp my_emp_table;
Var1_of_emp my_emp_table;
Var_of_emp(1).ename := ‘sachin’;
Var_of_emp(1).empno := 20;
Var_of_emp(1).sal := 3000;

To delete individual records from PL/SQL tables:

Var_of_emp .delete(1);

To remove all entries from a PL/SQL table:

Var_of_emp.delete;

Or

Var_of_emp := var1_of_emp

Where var1_of_emp does not contain any value, it is empty.

COUNT method can be used to return number of records in a PL/SQL Table.

Var_of_table.count

First, Next and Last methods of PL/SQL Tables.

First is for first index in the PL/SQL Tables.

Last is for last index in the PL/SQL Tables.

Next is for next index in the PL/SQL Tables.

Example showing First and Next method of PL/SQL tables

SQL> set serveroutput on

SQL> Declare
2
3 Type my_dept_table is table of varchar2(20)
4 Index by binary_integer;
5
6 Var_of_dept my_dept_table;
7 Index_value number;
8
9 Begin
10
11 For dept_rec in (select * from dept) loop
12 Var_of_dept(dept_rec.deptno) := dept_rec.dname;
13 End loop;
14
15 Index_value := var_of_dept.first;
16 Loop
17 Exit when index_value is null;
18 Dbms_output.put_line (index_value || ' ' ||var_of_dept(index_value));
19 Index_value := var_of_dept.next(index_value);
20 End loop;
21 End;
22 /

10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS

PL/SQL procedure successfully completed.


Another example of PL/SQL Table

DECLARE
 TYPE last_name_type IS TABLE OF student.last_name%TYPE
  INDEX BY BINARY_INTEGER;
 last_name_tab last_name_type;



last_name_type TYPE is declared based on the column LAST_NAME
 of the STUDENT table. 
last_name_tab is the actual index-by table declared as 
LAST_NAME_TYPE.


DECLARE 
 CURSOR name_cur IS
  SELECT last_name
  FROM student
  WHERE rownum <=10;
 
   TYPE last_name_type IS TABLE OF student.last_name%TYPE 
      INDEX BY BINARY_INTEGER;
   last_name_tab last_name_type;

v_counter INTEGER := 0;
BEGIN
     For name_rec IN name_cur LOOP
 v_counter := v_counter + 1;
 last_name_tab(v_counter) := name_rec.last_name;
 DBMS_OUTPUT.PUT_LINE('last_name('||v_counter||'): '|| 
 last_name_tab(v_counter));
     END LOOP;
END;


OUTPUT will be like:
last_name(1): Roy
last_name(2): Brit
....
....