java2db

PL/SQL Arrays and nested tables ( Collections )



PL/SQL Arrays and nested tables ( Collections ) are  single-dimensional arrays of data all with the same data type and are accessed by an index. PL/SQL Collections are three types.

 

PL/SQL Arrays and nested tables ( Collections ) Associative arrays (known as index-by tables or PL/SQL tables) :

These are single dimension and unbounded collections of  homogeneous elements,set of key-value pairs. Available only in PL/SQL but not in the Oracle database. Associative arrays are initially sparse.they have non consecutive subscripts.

 

PL/SQL Arrays and nested tables ( Collections )  Like database table , It is empty (but not null) until you populate it.
PL/SQL Arrays and nested tables ( Collections )  It can possible to hold unspecified number of elements ,  and possible to access without knowing their position
PL/SQL Arrays and nested tables ( Collections )  Cannot be manipulate with DML statements.
PL/SQL Arrays and nested tables ( Collections )  Cannot declare an associate array type at schema level.
PL/SQL Arrays and nested tables ( Collections )  Do not use TO_CHAR(SYSDATE) as an index.

 

Syntax :

TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY {BINARY_INTEGER | VARCHAR2 (size_limit)};

There are two types of associative arrays :

i) INDEX BY BINARY_INTEGER or PLS_INTEGER : which allows you to associate a value with a BINARY_INTEGER.

ii) INDEX BY VARCHAR2 : which allows you to associate a value with a text string.

 

 

PL/SQL Arrays and nested tables ( Collections ) Nested tables :

These are single dimension and unbounded collection of homogeneous elements available in both PL/SQL  and  the Oracle database as a column of a table.Nested tables initially have consecutive subscripts. but they can become sparse through deletions.

Syntax :

[CREATE [OR REPLACE]] TYPE type_name { IS | AS } TABLE OF element_type [NOT NULL];

 

 

PL/SQL Arrays and nested tables ( Collections ) VARRAYs

Variable-size arrays. These are single dimension and bounded collections of homogeneous elements available in both PL/SQL and the Oracle database.VARRAYs are never sparse.

Syntax :

[CREATE [OR REPLACE ]] TYPE type_name {IS | AS } VARRAY | VARYING ARRAY (max_elements) OF element_type [NOT NULL];

PL/SQL Arrays and nested tables ( Collections ) Difference between Arrays and nested tables :

Arrays:

PL/SQL Arrays and nested tables ( Collections ) Bounded, and the size cannot increase dynamically.
PL/SQL Arrays and nested tables ( Collections ) Not possible to delete elements. never sparse.
Nested Tables:
PL/SQL Arrays and nested tables ( Collections ) Unbounded, and the size can increase dynamically.
PL/SQL Arrays and nested tables ( Collections ) Possible to delete elements ,so it become sparse.

Possible to model a multidimensional collection with a collection whose elements are collections.

 

PL/SQL Arrays and nested tables ( Collections ) Collection ( Arrays and nested tables )  methods :

Method Type Description
COUNT Function Returns the current number of elements in the collection.If a collection is atomically NULL, this method raises an exception
DELETE(i or (i,j)) Procedure i)DELETE  ,deletes all elements form the collection.
ii)DELETE(i) , deletes the element whose index is i .
iii)DELETE(i,j)  , deletes all elements indexes with in the range of i .. j .
EXISTS(i) Function Returns TRUE or FALSE to indicate whether element i exists . If the collection is an uninitialized nested table or VARRAY ,return FALSE.
EXTEND(n or (n,i)) Procedure i)EXTEND, appends one null element to the collection .
ii)EXTEND(n) , appends n null elements to the collection  .
iii)EXTEND(n,i) appends ,  n copies of i the element to the collection . Both n and i are optional. By default n is 1 .
FIRST Function Returns the lowest index in use, Returns NULL when applied to empty initialized collections.
LAST Function Returns the greatest index in use, Returns NULL when applied to empty initialized collections.
LIMIT Function Returns the maximum number of allowed elements in a VARRAY. Returns NULL for Associative Arrays and nested tables .
PRIOR(i) Function Returns the index immediately before element i . Returns NULL if i is less than of equal to FIRST.
NEXT(i) Function Returns the index immediately after element i. Returns NULL if i is greater than or equal to COUNT.
TRIM(n) Procedure i)TRIM , removes one element from the end of the collection , if the collection has at least one element.
ii)TRIM(n) ,removes n elements from the end of the collection, if there are at least n elements at the end. Otherwise it raises SUBSCRIPT_BEYOND_COUNT exception.



Related Posts :
IF THEN ELSE and CASE in PL/SQL
PL/SQL FOR and WHILE loop controls
PL/SQL Operators
PL/SQL Procedures
PL/SQL Triggers
PL/SQL Functions
PL/SQL Variables
addAll method in java Collections
   [will not be published]



^ <