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 following series of images 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.
FIRST and LAST Methods
Use the FIRST and LAST methods with nested tables, associative arrays, and VARRAYs to return, respectively, the lowest and highest index values defined in the collection. For string-indexed associative arrays, these methods return strings; “lowest” and “highest” are determined by the ordering of the character set in use in that session. For all other collection types, these methods return integers. The specifications for these functions follow.
FUNCTION FIRST RETURN PLS_INTEGER | VARCHAR2;
FUNCTION LAST RETURN PLS_INTEGER | VARCHAR2;
For example, the following code scans from the start to the end of my collection:
FOR indx IN holidays.FIRST .. holidays.LAST
LOOP
send_everyone_home (indx);
END LOOP;
This kind of loop will only work if the collection is densely populated. In the next example, I use COUNT to concisely specify that I want to append a row to the end of an associative array. I use a cursor FOR loop to transfer data from the database to an associative array of records. When the first record is fetched, the companies collection is empty, so the COUNT operator will return 0.
FOR company_rec IN company_cur
LOOP
companies ((companies.COUNT) + 1).company_id
company_rec.company_id;
END LOOP;
Boundary considerations:
FIRST and LAST return NULL when they are applied to initialized collections that have
no elements. For VARRAYs, which have at least one element, FIRST is always 1, and
LAST is always equal to COUNT.
Exceptions possible:
If FIRST and LAST are applied to an uninitialized nested table or a VARRAY, they raise
the COLLECTION_ IS_NULL predefined exception.
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
DECLARE
TYPE PetNameTabTyp IS TABLE OF CHAR(10) INDEX
BY BINARY_INTEGER;
PName_tab PetNameTabTyp;
BEGIN
PName_tab(1) := 'DOG';
PName_tab(3) := 'CAT';
PName_tab(5) := 'FISH';
-- delete element 3
PName_tab.DELETE(3);
-- PL/SQL keeps a placeholder for element 3.
-- So, the next statement appends element 4.
PName_tab.EXTEND;
PName_tab(4) := 'RABBIT';
END;
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.
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.
In the next lesson, you will learn how to create a PL/SQL record.
