Nested tables use the set operator-single or multiple columns

Source: Internet
Author: User

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;

 

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.