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.

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 datatype 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.

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
count Return the number of elements in the table
First and last FIRST 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
Delete DELETE 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 must read these  other PLSQL posts

Oracle PLSQL Block Structure and Oracle PLSQL Variable

Everything about Oracle PLSQL records

Most commonly asked 25 oracle plsql interview questions

Oracle sql and plsql


Leave a Reply