RelationalDBDesign RelationalDBDesign

Composite Datatypes   «Prev  Next»
Lesson 2PL/SQL table
ObjectiveDescribe the structure of a PL/SQL table.

PL/SQL Table Structure: 1) TABLE, 2) RECORD

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.

  1. The EXISTS(n) method returns TRUE if the nth element within the PL/SQL table exists.
  2. The COUNT method returns the number of elements that the PL/SQL table currently contains.
  3. 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.
  4. Example of the FIRST LAST method continued.
  5. The PRIOR(n) method returns the index number that precedes index n within the PL/SQL table.
  6. The NEXT method returns the index number that succeeds index n within a PL/SQL table
  7. The EXTEND (n,i) method increases the size of the PL/SQL table.
  8. The TRIM method removes one element from the end of the PL/SQL table.
  9. 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.