CODEWITHSUNDEEP
sqldatabaseoracle-databaseplsql
Andrey Saleba
Andrey Saleba

Reputation: 2197

PL/SQL subtract collection

I have 2 collection variables var1 and var2 in my pl/sql function. For example, the data inside of them is:

var1           var2
X              X
X
Y

How can I subtract with pl/sql these collections so the result of it will be like:

var1 - var2:       var2 - var1
X                  empty
Y

My collection type definition is below:

create type MY_TYPE_OBJECT as (
id                  numeric(20,0),
string_val          varchar(4000),
time                timestamp)

create domain MY_TYPE_TABLE as MY_TYPE_OBJECT[];

my variables are of MY_TYPE_TABLE type.

Upvotes: 0

Views: 1702

Answers (1)

peter.hrasko.sk
peter.hrasko.sk

Reputation: 4141

Assuming your collections are of the same data type, in PL/SQL you can do it extremely easily as

var1 multiset except var2
var2 multiset except var1

Example:

declare
    var1                sys.ora_mining_varchar2_nt := sys.ora_mining_varchar2_nt('X','X','Y');
    var2                sys.ora_mining_varchar2_nt := sys.ora_mining_varchar2_nt('X');

    var1_minus_var2     sys.ora_mining_varchar2_nt;
    var2_minus_var1     sys.ora_mining_varchar2_nt;
begin
    dbms_output.put_line('var1 has '||var1.count()||' elements');
    for i in nvl(var1.first(),1)..nvl(var1.last(),0) loop
        dbms_output.put_line('    element '||i||' = '||var1(i));
    end loop;

    dbms_output.put_line('var2 has '||var2.count()||' elements');
    for i in nvl(var2.first(),1)..nvl(var2.last(),0) loop
        dbms_output.put_line('    element '||i||' = '||var2(i));
    end loop;

    var1_minus_var2 := var1 multiset except var2;

    dbms_output.put_line('var1_minus_var2 has '||var1_minus_var2.count()||' elements');
    for i in nvl(var1_minus_var2.first(),1)..nvl(var1_minus_var2.last(),0) loop
        dbms_output.put_line('    element '||i||' = '||var1_minus_var2(i));
    end loop;

    var2_minus_var1 := var2 multiset except var1;

    dbms_output.put_line('var2_minus_var1 has '||var2_minus_var1.count()||' elements');
    for i in nvl(var2_minus_var1.first(),1)..nvl(var2_minus_var1.last(),0) loop
        dbms_output.put_line('    element '||i||' = '||var2_minus_var1(i));
    end loop;
end;
/

... yields the output of ...

var1 has 3 elements
    element 1 = X
    element 2 = X
    element 3 = Y
var2 has 1 elements
    element 1 = X
var1_minus_var2 has 2 elements
    element 1 = X
    element 2 = Y
var2_minus_var1 has 0 elements

Enjoy!

Upvotes: 1

Related Questions