Working with PLSQL composit datatypes

Composit datatypes can hold multiple values(unlike scaler datatypes). Composit datatypes are two types: 

·         PLSQL records

·         PLSQL Collections:

o   INDEX BY Tables or associated arrays

o   Nested Table

o   VARRY

PLSQL Records: Use PLSQL records when we want to store values of different data types but only one occurrence at a time.

a)      Must contain one or more components (called fields) of any scalar, RECORD, or INDEX BY table data type

b)      Are similar to structures in most 3GL languages (including C and C++)

c)       Are user defined and can be a subset of a row in a table

d)      Treat a collection of fields as a logical unit

e)      Are convenient for fetching a row of data from a table for processing 

Example:

 

PLSQL Collections: Use PLSQL collections when we want to store values of the same data type.

•       INDEX BY Table:

a)      Are PL/SQL structures with two columns

                                                                                 i.            Primary key of integer or string data type

                                                                               ii.            Column of scalar or record data type

b)      Are unconstrained in size.  The size depends on the values that the key data type can hold.

Example:

  

 

•       Nested Table:  A nested table is like a one-dimensional array with an arbitrary number of elements. However, a nested table differs from an array in the following aspects:

a)      An array has a declared number of elements, but a nested table does not. The size of a nested table can increase dynamically.

b)      A nested array is dense initially, but it can become sparse when elements are deleted from it.

Example: