PL-SQL Tables- Learn the implementation difference between arrays and PL-SQL Tables with syntax and examples. Also Learn about some predefined Collection Methods used in collections with Examples.

PL-SQL Tables

This are similar to arrays, only difference is implementation.

This PL-SQL table is similar to a database table that contains 2 fields, one is primary key and other is to store the corresponding value.

Primary key is a binary integer or varchar2.

Tables in oracle have no limit on the number of rows.

The elements in the table are not ordered. they do not have to be sequential.

A table is defined as a TYPE field in the Declare section using the syntax

Syntax

TYPE type_name IS TABLE OF data_type [NOT NULL] INDEX BY Data_type;
table_name type_name;

Note

That the data type has to be a scalar or record datatype and the index has to be binary_integer or varchar2

Example program to Print ID and Name using PL-SQL Tables

DECLARE
TYPE t_employee
IS
  TABLE OF VARCHAR2(12) INDEX BY BINARY_INTEGER;
  v_employee_list t_employee;
  name VARCHAR2(20);
BEGIN
  v_employee_list(5)  := 'scott';
  v_employee_list(2)  := 'locket';
  v_employee_list(12) := 'jeasd';
  v_employee_list(6)  := 'drift';
  name                :=v_employee_list.first;
  WHILE name          IS NOT NULL
  LOOP
    dbms_output.put_line('The employee id is: '||name||
          ' employee name is '||v_employee_list(name));
    name :=v_employee_list.next(name);
  END LOOP;
END;

Output

The employee id is: 2 employee name is locket
The employee id is: 5 employee name is scott
The employee id is: 6 employee name is drift
The employee id is: 12 employee name is jeasd

Collection Methods and Description

Method NameMethod Description
DELETE(m,n)Removes all elements in the range m..n from an associative array or nested table.If m is larger than n or if m or n is null, DELETE(m,n) does nothing.
DELETE(n)Removes the nth element from an associative array with a numeric key or a nested table. If the associative array has a string key, the element
corresponding to the key value is deleted. If n is null, DELETE(n) does nothing.
DELETERemoves all elements from a collection, setting COUNT to 0.
TRIM(n)Removes n elements from the end of a collection.
TRIMRemoves one element from the end of a collection.
EXTEND(n,i)Appends n copies of the ith element to a collection.
EXTEND(n)Appends n null elements to a collection.
EXTENDAppends one null element to a collection.
NEXT(n)Returns the index number that succeeds index n.
COUNTwill return the number of rows in the table
DELETEwill delete rows from a table
EXISTS(n)will return a boolean true if the entry requested exists in the table and a boolean false if it does not exist
FIRSTwill return the binary_integer index of the first row
LASTwill return the binary_integer index of the last row
NEXT(n)will return the binary_integer index of the next row in the table – the one after the specified row
PRIOR(n)will return the binary_integer index of the previous row in the table – the one before the specified row

Example Program to show usage of Collection Method

DECLARE
TYPE t_employee
IS
  TABLE OF VARCHAR2(12) INDEX BY BINARY_INTEGER;
  v_employee_list t_employee;
  v_count_result NUMBER;
  v_index_first BINARY_INTEGER;
  v_index_next BINARY_INTEGER;
  v_index_prior BINARY_INTEGER;
  v_index_last BINARY_INTEGER;
BEGIN
  v_employee_list(5)  := 'scott';
  v_employee_list(2)  := 'locket';
  v_employee_list(12) := 'jeasd';
  v_employee_list(6)  := 'drift';
  v_count_result      :=v_employee_list.count;
  dbms_output.put_line('The count is: '||v_count_result);
  v_index_first := v_employee_list.FIRST;
  dbms_output.put_line('The first record is: '||v_index_first);
  v_index_next := v_employee_list.NEXT(v_index_first);
  dbms_output.put_line('The next record is: '||v_index_next);
  v_index_prior := v_employee_list.PRIOR(v_index_next);
  dbms_output.put_line('The prior record is: '||v_index_prior);
  v_index_last := v_employee_list.last;
  dbms_output.put_line('The last record is: '||v_index_last);
  v_index_prior := v_employee_list.PRIOR(v_index_last);
  dbms_output.put_line('The prior record is: '||v_index_prior);
  v_index_first := v_employee_list.FIRST;
  dbms_output.put_line('The first record is: '||v_index_first);
END;

Output
The count is: 4
The first record is: 2
The next record is: 5
The prior record is: 2
The last record is: 12
The prior record is: 6
The first record is: 2

Example Program Index by Table With %ROWTYPE

DECLARE
  CURSOR e_employees
  IS
    SELECT name FROM employee_tbl1;
TYPE e_list
IS
  TABLE OF employee_tbl1.Name%type INDEX BY binary_integer;
  name_list e_list;
  counter INTEGER :=0;
BEGIN
  FOR n IN e_employees
  LOOP
    counter            := counter +1;
    name_list(counter) := n.name;
    dbms_output.put_line('employee('||counter||'):'||name_list(counter));
  END LOOP;
END;

Output

employee(1):Tom
employee(2):Pam
employee(3):John
employee(4):Sam
employee(5):Todd
employee(6):Ben
employee(7):Sara
employee(8):Valarie
employee(9):James
employee(10):Russell

Related Posts