You can use the COUNT method to return the number of elements in a collection, where deleted elements are ignored even if the DELETE method retains placeholders for them.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only MySQL mode.
Usage in varrays
For a variable-size array (or varray), COUNT is always equivalent to LAST. If the EXTEND or TRIM method is used to increase or decrease the size of a varray, the value of COUNT changes accordingly.
obclient> DECLARE
TYPE oblist IS VARRAY(10) OF INTEGER;
t oblist := OBLIST(2,4,6,8,10);
PROCEDURE print_count_and_last IS
BEGIN
DBMS_OUTPUT.PUT('t.COUNT = ' || t.COUNT || ', ');
DBMS_OUTPUT.PUT_LINE('t.LAST = ' || t.LAST);
END print_count_and_last;
BEGIN
print_count_and_last;
t.EXTEND(2);
print_count_and_last;
t.TRIM(4);
print_count_and_last;
END;
/
Query OK, 0 rows affected
t.COUNT = 5, t.LAST = 5
t.COUNT = 7, t.LAST = 7
t.COUNT = 3, t.LAST = 3
The preceding example declares a varray variable and initializes and assigns values to four elements. After EXTEND(3) and TRIM(5) are executed, the COUNT and LAST values of the varray are output.
Usage in nested tables
For a nested table, COUNT is equivalent to LAST. The value of COUNT is smaller than that of LAST unless intermediate elements are deleted from the nested table.
obclient> DECLARE
TYPE oblist IS TABLE OF INTEGER;
t oblist := OBLIST(2,4,6,8,10);
PROCEDURE print_count_and_last IS
BEGIN
DBMS_OUTPUT.PUT('t.COUNT = ' || t.COUNT || ', ');
DBMS_OUTPUT.PUT_LINE('t.LAST = ' || t.LAST);
END print_count_and_last;
BEGIN
print_count_and_last;
t.EXTEND(2); -- Add two null elements at the end.
print_count_and_last;
t.DELETE(3); -- Delete the third element.
print_count_and_last;
FOR i IN 1..8 LOOP
IF t.EXISTS(i) THEN
IF t(i) IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE('t(' || i || ') = ' || t(i));
ELSE
DBMS_OUTPUT.PUT_LINE('t(' || i || ') = NULL');
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('t(' || i || ') does not exist');
END IF;
END LOOP;
END;
/
Query OK, 0 rows affected
t.COUNT = 5, t.LAST = 5
t.COUNT = 7, t.LAST = 7
t.COUNT = 6, t.LAST = 7
t(1) = 2
t(2) = 4
t(3) does not exist
t(4) = 8
t(5) = 10
t(6) = NULL
t(7) = NULL
t(8) does not exist
The preceding example declares a nested table variable, initializes and assigns values to five elements, adds two null elements at the end, deletes the third element, and returns the COUNT and LAST values of the nested table. Finally, the states of elements 1 to 8 are output.