Oracle PL/SQL - Comparing Nested Tables with SQL Multiset Conditions

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Data Type
  4. Nested Tables

Introduction

You can compare nested table variables, and test some of their properties, with SQL multiset conditions.

The following code uses the SQL multiset conditions and two SQL functions that take nested table variable arguments, CARDINALITY and SET.

Demo

SQL>
SQL> DECLARE--   w w w .j  av a2  s  .co  m
  2    TYPE NumberTable IS TABLE OF NUMBER;
  3    nt1 NumberTable := NumberTable(1,2,3);
  4    nt2 NumberTable := NumberTable(3,2,1);
  5    nt3 NumberTable := NumberTable(2,3,1,3);
  6    nt4 NumberTable := NumberTable(1,2,4);
  7
  8    PROCEDURE testify (
  9      truth BOOLEAN := NULL,
 10      quantity NUMBER := NULL
 11    ) IS
 12    BEGIN
 13      IF truth IS NOT NULL THEN
 14        DBMS_OUTPUT.PUT_LINE (
 15          CASE truth
 16             WHEN TRUE THEN 'True'
 17             WHEN FALSE THEN 'False'
 18          END
 19        );
 20      END IF;
 21      IF quantity IS NOT NULL THEN
 22          DBMS_OUTPUT.PUT_LINE(quantity);
 23      END IF;
 24    END;
 25  BEGIN
 26    testify(truth => (nt1 IN (nt2,nt3,nt4)));        -- condition
 27
 28    testify(truth => (nt1 SUBMULTISET OF nt3));      -- condition
 29    testify(truth => (nt1 NOT SUBMULTISET OF nt4));  -- condition
 30    testify(truth => (4 MEMBER OF nt1));             -- condition
 31    testify(truth => (nt3 IS A SET));                -- condition
 32    testify(truth => (nt3 IS NOT A SET));            -- condition
 33    testify(truth => (nt1 IS EMPTY));                -- condition
 34    testify(quantity => (CARDINALITY(nt3)));         -- function
 35    testify(quantity => (CARDINALITY(SET(nt3))));    -- 2 functions
 36  END;
 37  /
True
True
True
False
False
True
False
4
3

PL/SQL procedure successfully completed.

SQL>

Related Topic