Use EMPTY Operator
DECLARE
TYPE customers_list_t IS TABLE OF VARCHAR2 (30);
l_customers customers_list_t
:= customers_list_t ('Customer 1', 'Customer 3');
BEGIN
IF l_customers IS NOT EMPTY
THEN
DBMS_OUTPUT.put_line ('We have customers!');
END IF;
END; We have customers!
Use Good Old COUNT
DECLARE
TYPE customers_list_t IS TABLE OF VARCHAR2 (30);
l_customers customers_list_t
:= customers_list_t ('Customer 1', 'Customer 3');
BEGIN
IF l_customers.COUNT > 0
THEN
DBMS_OUTPUT.put_line ('We have customers!');
END IF;
END; We have customers!
Use CARDINALITY
DECLARE
TYPE customers_list_t IS TABLE OF VARCHAR2 (30);
l_customers customers_list_t
:= customers_list_t ('Customer 1', 'Customer 3');
BEGIN
IF CARDINALITY (l_customers) > 0
THEN
DBMS_OUTPUT.put_line ('We have customers!');
END IF;
END; We have customers!
Compare Performance of Different Approaches
DECLARE
l_number NUMBER;
TYPE customers_list_t IS TABLE OF VARCHAR2 (30);
l_customers customers_list_t
:= customers_list_t ('Customer 1', 'Customer 3');
l_start TIMESTAMP;
PROCEDURE mark_start
IS
BEGIN
l_start := SYSTIMESTAMP;
END mark_start;
PROCEDURE show_elapsed (NAME_IN IN VARCHAR2)
IS
BEGIN
DBMS_OUTPUT.put_line (
'"'
|| NAME_IN
|| '": '
|| REGEXP_SUBSTR (SYSTIMESTAMP - l_start,
'([1-9][0-9:]*|0)\.\d{3}')
|| ' seconds');
mark_start;
END show_elapsed;
BEGIN
mark_start;
FOR indx IN 1 .. 100000000
LOOP
IF l_customers IS EMPTY
THEN
l_number := indx;
END IF;
END LOOP;
show_elapsed ('IS EMPTY');
FOR indx IN 1 .. 100000000
LOOP
IF l_customers.COUNT = 0
THEN
l_number := indx;
END IF;
END LOOP;
show_elapsed ('COUNT');
FOR indx IN 1 .. 100000000
LOOP
IF CARDINALITY (l_customers) = 0
THEN
l_number := indx;
END IF;
END LOOP;
show_elapsed ('CARDINALITY');
END; "IS EMPTY": 6:59:58.890 seconds
"COUNT": 6:59:58.365 seconds
"CARDINALITY": 6:59:58.384 seconds