Oracle PL/SQL/PL SQL/IS

If varray is empty

 
SQL> CREATE OR REPLACE TYPE list IS TABLE OF NUMBER;
  2  /
Type created.
SQL>
SQL> CREATE OR REPLACE FUNCTION format_list(set_in LIST) RETURN VARCHAR2 IS
  2    returnValue VARCHAR2(2000);
  3  BEGIN
  4    IF set_in IS EMPTY THEN
  5      dbms_output.put_line("Empty");
  6    END IF;
  7    RETURN returnValue;
  8  END format_list;
  9  /
Function created.
SQL>

IS A SET operator checks whether a variable is a VARRAY or NESTED TABLE collection variable

 
SQL>
SQL>  DECLARE
  2     TYPE list IS TABLE OF INTEGER;
  3     a LIST := list();
  4   BEGIN
  5     IF a IS A SET THEN
  6       dbms_output.put_line(""a" is a set.");
  7     END IF;
  8   END;
  9   /
"a" is a set.
PL/SQL procedure successfully completed.

IS A SET, table collection

 

SQL> CREATE OR REPLACE PROCEDURE is_a_set_example AS
  2    TYPE charTable IS TABLE OF VARCHAR2(10);
  3    emp1 charTable;
  4    result BOOLEAN;
  5  BEGIN
  6    emp1 := charTable("A", "B", "C", "D");
  7    result := emp1 IS A SET;
  8    IF result THEN
  9      DBMS_OUTPUT.PUT_LINE("Elements are all unique");
 10    ELSE
 11      DBMS_OUTPUT.PUT_LINE("Elements contain duplicates");
 12    END IF;
 13  END is_a_set_example;
 14  /
Procedure created.

IS EMPTY

 

SQL> CREATE OR REPLACE PROCEDURE is_empty_example AS
  2    TYPE charTable IS TABLE OF VARCHAR2(10);
  3    emp1 charTable;
  4    result BOOLEAN;
  5  BEGIN
  6    emp1 := charTable("A", "B", "C");
  7    result := emp1 IS EMPTY;
  8    IF result THEN
  9      DBMS_OUTPUT.PUT_LINE("Nested table is empty");
 10    ELSE
 11      DBMS_OUTPUT.PUT_LINE("Nested table contains elements");
 12    END IF;
 13  END is_empty_example;
 14  /
Procedure created.
SQL>
SQL>

IS EMPTY operator checks whether a VARRAY or NESTED TABLE collection variable is empty

 
SQL>
SQL>  DECLARE
  2     TYPE list IS TABLE OF INTEGER;
  3     a LIST := list();
  4   BEGIN
  5     IF a IS EMPTY THEN
  6       dbms_output.put_line(""a" is empty.");
  7     END IF;
  8   END;
  9   /
"a" is empty.
PL/SQL procedure successfully completed.
SQL>

IS NULL operator checks whether a variable value is null

 
SQL>
SQL>  DECLARE
  2     var BOOLEAN;
  3    BEGIN
  4     IF var IS NULL THEN
  5      dbms_output.put_line("It is null.");
  6     END IF;
  7    END;
  8    /
It is null.
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>