Home » Oracle » How to use Oracle PLSQL Tables (Associative array or index-by table)

How to use Oracle PLSQL Tables (Associative array or index-by table)

Oracle PLSQL Tables

  • PLSQL tables are composite datatypes.
  • These behave in the same way as arrays except that have no upper bounds, allowing them to constantly extend.
  • They are also called index by table
  • PLSQL Table contains two elements

(1) A primary key of BINARY_INTEGER datatypes that index the table, which do not need to be consecutive. The collection is extended by assigning values to an element using an index value that does not currently exist.
we are able to index by a string value also

(2) A column of scalar or record datatype which stores the index by table elements

How to define and Declare Table type

To create PL/SQL tables, you take two steps. First, you define a TABLE type, then declare PL/SQL tables of that type. You can define TABLE types in the declarative part of any block, subprogram, or package using the syntax

Syntax 
 TYPE table_type_name IS TABLE OF datatype [NOT NULL] INDEX BY BINARY_INTEGER;

If the element type is a record type, every field in the record must have a scalar datatype such as CHAR, DATE, or NUMBER.

To specify the element type, you can use %TYPE to provide the data type of a variable or database column

DECLARE 
 TYPE DEPTTabTyp IS TABLE OF dept.dept_name%TYPE INDEX BY BINARY_INTEGER;

You can add the NOT NULL constraint to a TABLE type definition and so prevent the storing of nulls in PL/SQL tables of that type:

DECLARE 
 TYPE DEPTTabTyp IS TABLE OF dept.location%TYPE NOT NULL INDEX BY BINARY_INTEGER;

You can also use %ROWTYPE to specify the element type.

DECLARE 
 TYPE deptTabTyp IS TABLE OF dept%ROWTYPE INDEX BY BINARY_INTEGER;

you use a User define RECORD type to specify the element type:

DECLARE 
 TYPE emp_phonetyp IS RECORD
 (
 Stdcode      PLS_INTEGER,
 phn_number   PLS_INTEGER,
 extension    PLS_INTEGER
 );
 TYPE emp_contact IS TABLE OF emp_phonetyp INDEX BY BINARY_INTEGER;

After you define a TABLE type, you can declare PL/SQL tables of that type

DECLARE   
 TYPE SalTabTyp IS TABLE OF emp.sal%TYPE 
 INDEX BY BINARY_INTEGER;
 TYPE EmpTabTyp IS TABLE OF emp%ROWTYPE
 INDEX BY BINARY_INTEGER;
 sal_tab SalTabTyp;  -- declare PL/SQL table
 emp_tab EmpTabTyp;  -- declare another PL/SQL table

PLSQL Table attributes or Method

A PL/SQL table has the attributes EXISTS, COUNT, FIRST, LAST, PRIOR, NEXT, and DELETE.

See also  Top 115 HFM Interview Questions Part-3

They make PL/SQL tables easier to use and your applications easier to maintain.

plsql_table_name.attribute_name

The attributes exists, PRIOR, NEXT, and DELETE take parameters. Each parameter must be an expression that yields a BINARY_INTEGER value or a value implicitly convertible to that datatype.

DELETE acts like a procedure, which is called as a statement. However, the other PL/SQL table attributes act like a function, which is called as part of an expression.

Exists(n)Return true if nth element in the table exists
countReturn the number of elements in the table
First and lastFIRST and LAST return the first and last (smallest and largest) index numbers in a PL/SQL table
PRIOR(n)returns the index number that precedes index n in a PL/SQL table
NEXT(n)returns the index number that succeeds index n
DeleteDELETE removes all elements from a PL/SQL table. DELETE(n) removes the nth element. If n is null, DELETE(n) does nothing. DELETE(mn) removes all elements in the range m .. n. If m is larger than n or if m or n is null, DELETE(mn) does nothing

PL/SQL tables to move bulk data into and out of database tables or between client-side applications and stored subprograms.

How to populate the data in PLSQL Table

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>);
Type emptabletype  is table of varchar2(10)
Index by binary_integer;
emp_table emptabletyp;
emp_table (1) := ‘good’;
emp_table (2) := ‘bad’’;

You can retrieve Oracle data into a PL/SQL table in other three ways also

See also  How To Retrieve Instance Metadata from Server

a)SELECT INTO statement lets you select a single row of data

Using the SELECT INTO statement, you can select a column entry into a scalar element. Or, you can select an entire row into a record element. In the following example, you select a row from the database table dept into a record stored by the first element of the PL/SQL table dept_tab:

DECLARE
TYPE DeptTabTyp IS TABLE OF dept%ROWTYPE
INDEX BY BINARY_INTEGER;
dept_tab DeptTabTyp;
BEGIN
/* Select entire row into record stored by first element. */
SELECT * INTO dept_tab(1) FROM dept WHERE deptno = 10;
IF dept_tab(1).dname = 'ACCOUNTING' THEN ...
...
END;

b)  FETCH statement

With the FETCH statement, you can fetch an entire column of Oracle data into a PL/SQL table of scalars.

Or you can fetch an entire table of Oracle data into a PL/SQL table of records.

DECLARE
TYPE EmpTabTyp IS TABLE OF emp%ROWTYPE
INDEX BY BINARY_INTEGER;
emp_tab EmpTabTyp;
i BINARY_INTEGER := 0;
CURSOR c1 IS SELECT * FROM emp;
BEGIN
OPEN c1;
LOOP
i := i + 1;
/* Fetch entire row into record stored by ith element. */
FETCH c1 INTO emp_tab(i);
EXIT WHEN c1%NOTFOUND;
-- process data record
END LOOP;
CLOSE c1;
END;

c) cursor FOR loop lets you fetch multiple rows.

DECLARE
TYPE EnameTabTyp IS TABLE OF emp.ename%TYPE
INDEX BY BINARY_INTEGER;
TYPE SalTabTyp IS TABLE OF emp.sal%TYPE
INDEX BY BINARY_INTEGER;
ename_tab EnameTabTyp;
sal_tab SalTabTyp;
n BINARY_INTEGER := 0;
BEGIN
/* Fetch entire columns into PL/SQL tables. */
FOR emp_rec IN (SELECT ename, sal FROM emp) LOOP
n := n + 1;
ename_tab(n) := emp_rec.ename;
sal_tab(n) := emp_rec.sal;
END LOOP;
...
END;

You must read these  other PLSQL posts

See also  How to backup controlfile in RMAN in Oracle Database

Oracle PLSQL Block Structure and Types
Oracle PLSQL records
Oracle plsql interview questions
Quick Oracle sql and plsql tutorials

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top