The COUNT method returns the number of elements in a collection, ignoring deleted elements (even if DELETE operations have left placeholders for them).
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only MySQL-compatible mode.
Usage with a varray
For a varray, COUNT is always equal to LAST. If you use the EXTEND or TRIM method to change the size of a varray, the value of COUNT changes.
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, initializes it with four elements, and then executes EXTEND(3) and TRIM(5). The COUNT and LAST values of the varray are then output.
Usage with a nested table
For a nested table, COUNT is equal to LAST, except when elements are deleted from the middle of the nested table, in which case COUNT is less than LAST.
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 to 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 it with five elements, adds two NULL elements to the end, and deletes the third element. The COUNT and LAST values of the nested table are then output. Finally, the status of elements 1 through 8 in the example is output.