If the collection has at least one element, FIRST and LAST return the indexes of the first and last elements, respectively, ignoring deleted elements, even if DELETE has reserved placeholders for the deleted elements.
If the collection has only one element, FIRST and LAST return the same index. If the collection is empty, FIRST and LAST return NULL.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only MySQL mode.
Usage with associative arrays
For an associative array with an index type of PLS_INTEGER, the first and last elements are the elements with the smallest and largest indexes, respectively. For an associative array with a string index type, the first and last elements are the elements with the lowest and highest key values, respectively.
Here is an example:
obclient> DECLARE
TYPE numlist IS TABLE OF NUMBER INDEX BY VARCHAR2(5);
nl NUMLIST;
PROCEDURE print_first_and_last IS
BEGIN
DBMS_OUTPUT.PUT_LINE('FIRST = ' || nl.FIRST);
DBMS_OUTPUT.PUT_LINE('LAST = ' || nl.LAST);
END print_first_and_last;
BEGIN
nl('A') := 2;
nl('C') := 4;
nl('D') := 6;
nl('E') := 8;
nl('B') := 10;
DBMS_OUTPUT.PUT_LINE('Before deleting:');
print_first_and_last;
nl.DELETE('A');
nl.DELETE('E');
DBMS_OUTPUT.PUT_LINE('After deleting:');
print_first_and_last;
END;
/
Query OK, 0 rows affected
Before deleting:
FIRST = A
LAST = E
After deleting:
FIRST = B
LAST = D
The preceding example shows the values of FIRST and LAST for an associative array with a string index type. It then deletes the first and last elements and displays the values of FIRST and LAST again.
Usage with varrays
For a non-empty varray, FIRST always returns 1. For any varray, LAST is always equal to COUNT. Here is an example:
obclient> DECLARE
TYPE players IS VARRAY(5) OF VARCHAR2(20);
name PLAYERS;
PROCEDURE print_name (heading VARCHAR2) IS
BEGIN
DBMS_OUTPUT.PUT_LINE(heading);
IF name IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Does not exist');
ELSIF name.FIRST IS NULL THEN
DBMS_OUTPUT.PUT_LINE('No members');
ELSE
FOR i IN name.FIRST..name.LAST LOOP
DBMS_OUTPUT.PUT_LINE(i || '. ' || name(i));
END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('---');
END;
BEGIN
print_name('Team Members:');
name := PLAYERS(); -- The team has no members.
print_name('Team Members:');
name := players('Andrew', 'Barton');
print_name('Team Members:');
name := players('Charles', 'Carl', 'James', 'Gary','Brian');
print_name('New Team Members:');
END;
/
Query OK, 0 rows affected
Team Members:
Does not exist
---
Team Members:
No members
---
Team Members:
1. Andrew
2. Barton
---
New Team Members:
1. Charles
2. Carl
3. James
4. Gary
5. Brian
---
Usage with nested tables
For a nested table, LAST is equal to COUNT, except when elements are deleted from the middle, which makes LAST greater than COUNT.
Here is an example:
obclient> DECLARE
TYPE players IS TABLE OF VARCHAR2(20);
name PLAYERS;
PROCEDURE print_name (heading VARCHAR2) IS
BEGIN
DBMS_OUTPUT.PUT_LINE(heading);
IF name IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Does not exist');
ELSIF name.FIRST IS NULL THEN
DBMS_OUTPUT.PUT_LINE('No members');
ELSE
FOR i IN name.FIRST..name.LAST LOOP
DBMS_OUTPUT.PUT_LINE(i || '.');
IF name.EXISTS(i) THEN
DBMS_OUTPUT.PUT_LINE(name(i));
ELSE
DBMS_OUTPUT.PUT_LINE('TBD');
END IF;
END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('---');
END;
BEGIN
print_name('Team Members:');
name := players(); -- The team exists but has no players.
print_name('Team Members:');
name := players('Charles', 'Carl', 'James', 'Gary','Brian'); -- Players are added.
print_name('Team Members:');
name.DELETE(3,4); -- The third and fourth players are deleted.
print_name('Current Team Members:');
END;
/
Query OK, 0 rows affected
Team Members:
Does not exist
---
Team Members:
No members
---
Team Members:
1.
Charles
2.
Carl
3.
James
4.
Gary
5.
Brian
---
Current Team Members:
1.
Charles
2.
Carl
3.
TBD
4.
TBD
5.
Brian
---
The preceding example uses a FOR LOOP statement with team.FIRST and team.LAST to output the nested table name. Because a nested table can be sparse, the FOR LOOP statement outputs team(i) only when team.EXISTS(i) is TRUE.
The example uses a `FOR LOOP` statement with `team.FIRST` and `team.LAST` to output the nested table `name`. Because a nested table can be sparse, the `FOR LOOP` statement outputs `team(i)` only when `team.EXISTS(i)` is `TRUE`.