If the collection has at least one element, the FIRST and LAST methods return the indices of the first and last elements, respectively, ignoring deleted elements (even if DELETE leaves placeholders for deleted elements).
If the collection has only one element, the FIRST and LAST methods return the same index. If the collection is empty, the FIRST and LAST methods return NULL.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only MySQL mode.
Use in 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 indices, 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 FIRST and LAST values for an associative array with a string index type. It then deletes the first and last elements and shows the FIRST and LAST values again.
Use in varrays
For a non-empty varray, FIRST always returns 1. For any varray, LAST always equals 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
---
Use in nested tables
For a nested table, LAST equals 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 members.
print_name('Team Members:');
name := players('Charles', 'Carl', 'James', 'Gary','Brian'); -- Members are added.
print_name('Team Members:');
name.DELETE(3,4); -- Members 3 and 4 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 nested tables can be sparse, the FOR LOOP statement outputs team(i) only when team.EXISTS(i) is TRUE.