Nested tables use the set operator-single or multiple columns

Nested tables use Multiset union, Multiset except T, Multiset intersect, and other set operators. Generally, only one column is compared, but multiple columns are actually allowed:

The following describes how to use the Multiset transform t operator.

I. Use the set operator when there is only one column

The following 1,), 2), 3), 4) indicates that when only one column is used, the set operator can be directly assigned or assigned in the form of an SQL statement, but 2) A compilation error occurs because the variable type must be defined at the schma level, that is, a variable must be defined externally as 3) and 4, or define the packet header directly in the package.

1) Execution successful

 

Declare
Type typ_id_table1 is table of number;
Tab_fids1 typ_id_table1: = typ_id_table1 ();
Tab_fids2 typ_id_table1: = typ_id_table1 ();
V_result typ_id_table1;
Begin
V_result: = tab_fids1 Multiset into T tab_fids2;
End;

 

2) Execution prompt: the local collection type cannot be used in SQL statements.

Declare
Type typ_id_table1 is table of number;
Tab_fids1 typ_id_table1: = typ_id_table1 ();
Tab_fids2 typ_id_table1: = typ_id_table1 ();
V_result typ_id_table1;
Begin
Select tab_fids1 Multiset partition t tab_fids2 into v_result from dual;
End;

 

3) Compilation successful

Create or replace type typ_id_table3 as table of number (10); -- variables are defined externally
Declare
Tab_fids1 typ_id_table3: = typ_id_table3 ();
Tab_fids2 typ_id_table3: = typ_id_table3 ();
V_result typ_id_table3;
Begin
V_result: = tab_fids1 Multiset into T tab_fids2;
End;

 

4) Compilation successful

 

Create or replace type typ_id_table3 as table of number (10); -- define variables externally
Declare
Tab_fids1 typ_id_table3: = typ_id_table3 ();
Tab_fids2 typ_id_table3: = typ_id_table3 ();
V_result typ_id_table3;
Begin
Select tab_fids1 Multiset partition t tab_fids2 into v_result from dual;
End;

 

 

Ii. Use the set operator for multiple columns

The set operator cannot be used in case of multiple columns. Thanks again to the answers provided by the itpub newkid moderator.

That is, when comparing multiple columns, you cannot directly compare them. Instead, you must change them to the SQL statement format for comparison, as shown below:

 

Create or replace type typ_id_object as object (GID number (10 ),
Gno number (5 ),
CO number (5 ));

Create or replace type typ_id_table as table of typ_id_object;

 

 

1) Compilation successful

Declare
V1 typ_id_table: = typ_id_table ();
V2 typ_id_table: = typ_id_table ();
V3 typ_id_table;
Begin
Select V1 Multiset into T V2 into V3 from dual; -- This SQL statement must be used if multiple columns exist.
End;

2) declare
V1 typ_id_table: = typ_id_table ();
V2 typ_id_table: = typ_id_table ();
V3 typ_id_table;
Begin
V3: = V1 Multiset except V2; -- if this form is used, the prompt: PLS-00306: the number of parameters or type error when calling 'Et Et _ except_all'

End;