PL/SQL provides two composite datatypes: TABLE and RECORD.
Objects of the type table are called PL/SQL tables, which are modeled as database tables.
PL/SQL tables use a primary key for array-like access to rows. These tables can have one column and a primary key. Neither the column nor the primary key can be user defined.
The size of a PL/SQL table is unconstrained; that is, the table size grows dynamically as new rows are added.
Using PL/SQL tables
Declaration is done in two steps. First, define a table type, then declare PL/SQL tables of that type.
Table types can be declared within the declarative part of any block, subprogram, or package.
To reference the rows within a PL/SQL table, a primary key value must be specified using the array-like syntax, as shown in this diagram:
PL/SQL Syntax: TYPE type_name IS TABLE OF
The following graphic shows an example of declaring a PL/SQL table:
PL SQL Table Example: Using the type specifier
A PL/SQL table method is a built-in procedure or function that you can use with any PL/SQL table.
The method is called using the dot notation:
table_name.method_name [(parameters)]
The SlideShow below describes PL/SQL table methods.
The EXISTS(n) method returns TRUE if the nth element within the PL/SQL table exists.
The COUNT method returns the number of elements that the PL/SQL table currently contains.
The FIRST LAST method returns the first and last (smallest and largest) index numbers within the PL/SQL table.
This method returns NULL if the PL/SQL table is empty.
Example of the FIRST LAST method continued.
The PRIOR(n) method returns the index number that precedes index n within the PL/SQL table.
The NEXT method returns the index number that succeeds index n within a PL/SQL table
The EXTEND (n,i) method increases the size of the PL/SQL table.
The TRIM method removes one element from the end of the PL/SQL table.
The DELETE method removes all the elements from the PL/SQL table.
PL/SQL Table Methods
In the next lesson, you will learn how to create a PL/SQL record.