In programming we don't just compare variables/integers with each other, we tend to compare arrays too. Collections aka arrays can be compared in different ways, lets see them in detail.
Traditional Method
Option 1
The traditional method to compare collection would be just to loop through them and compare one with another, like below
SQL> set timing on
SQL>
SQL> set serveroutput on
SQL> declare
2 type basket is table of number;
3 num_balls basket := basket(1,2,3);
4 num_color_balls basket := basket(3,2,1);
5 inter_match number:=0;
6 begin
7 if num_balls.count = num_color_balls.count then
8 for i in num_balls.first .. num_balls.last loop
9 for j in num_color_balls.first .. num_color_balls.last loop
10 if num_balls(i) = num_color_balls(j) then
11 inter_match := inter_match+1;
12 end if;
13 end loop;
14 end loop;
15 if inter_match = num_balls.count then
16 dbms_output.put_line('Arrays are equal');
17 end if;
18 else
19 dbms_output.put_line('Arrays are not equal');
20 end if;
21 end;
22 /
Arrays are equal
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.04
SQL>
Option 2
Using Table Cast with MINUS operator thus avoiding loops
SQL>
SQL> create type basket is table of number;
2 /
Type created.
Elapsed: 00:00:00.72
SQL>
SQL> declare
2 num_balls basket := basket(1,2,3);
3 num_color_balls basket := basket(3,2,1);
4 v_count number;
5
6 begin
7
8 select column_value into v_count from
9 (
10 (select column_value from table(cast(num_balls as basket)))
11 minus
12 (select column_value from table(cast(num_color_balls as basket)))
13 );
14
15 if v_count>0 then
16 dbms_output.put_line('Arrays are not equal');
17 end if;
18
19 exception
20 when no_data_found then
21 dbms_output.put_line('Arrays are equal');
22
23 end;
24 /
Arrays are equal
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQL>
You will have to go only with the above two options until Oracle 10g, From 10g we have got many options to compare collections, lets see them in detail.
Comparing collection is now as same as comparing variables/integers, example given below
Comparing collection is now as same as comparing variables/integers, example given below
SQL>
SQL> declare
2 num_balls basket := basket(1,2,3);
3 num_color_balls basket := basket(1,2,3);
4 begin
5 if num_balls = num_color_balls then
6 dbms_output.put_line('Arrays are equal');
7 else
8 dbms_output.put_line('Arrays are not equal');
9 end if;
10 end;
11 /
Arrays are equal
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQL>
Note : The order of elements in an array is irrelevant while comparing collections. It means that an array with data 1,2,3 is equal to 3,2,1 and that's what we have seen above.
Ok this is fine, can we do the same with Associative and Varrays
Varrays
SQL>
SQL> declare
2 --type basket is table of number;
3 type basket is varray(10) of number;
4 num_balls basket := basket(1,2,3);
5 num_color_balls basket := basket(1,2,3);
6 begin
7 if num_balls = num_color_balls then
8 dbms_output.put_line('Arrays are equal');
9 else
10 dbms_output.put_line('Arrays are not equal');
11 end if;
12 end;
13 /
if num_balls = num_color_balls then
*
ERROR at line 7:
ORA-06550: line 7, column 20:
PLS-00306: wrong number or types of arguments in call to '='
ORA-06550: line 7, column 7:
PL/SQL: Statement ignored
Elapsed: 00:00:00.01
SQL>
Means you cannot compare all elements at one shot, have to do it one by one
SQL>
SQL> declare
2 --type basket is table of number;
3 type basket is varray(10) of number;
4 num_balls basket := basket(1,2,3);
5 num_color_balls basket := basket(1,2,3);
6 begin
7 if num_balls(1) = num_color_balls(1) then
8 dbms_output.put_line('Arrays are equal');
9 else
10 dbms_output.put_line('Arrays are not equal');
11 end if;
12 end;
13 /
Arrays are equal
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SQL>
Associative Array - Cannot be compared at one shot, should be looped,
SQL> REM ASSOCIATIVE ARRAYS
SQL>
SQL> declare
2 type basket is table of number index by pls_integer;
3 num_balls basket;
4 num_color_balls basket;
5 begin
6 select level bulk collect into num_balls from dual connect by level < 10;
7 select level bulk collect into num_color_balls from dual connect by level < 10;
8 if num_balls = num_color_balls then
9 dbms_output.put_line('Arrays are equal');
10 else
11 dbms_output.put_line('Arrays are not equal');
12 end if;
13 end;
14 /
if num_balls = num_color_balls then
*
ERROR at line 8:
ORA-06550: line 8, column 20:
PLS-00306: wrong number or types of arguments in call to '='
ORA-06550: line 8, column 7:
PL/SQL: Statement ignored
Elapsed: 00:00:00.01
SQL>
SQL>
SQL> declare
2 type basket is table of number index by pls_integer;
3 num_balls basket;
4 num_color_balls basket;
5 begin
6 select level bulk collect into num_balls from dual connect by level < 10;
7 select level bulk collect into num_color_balls from dual connect by level < 10;
8 if num_balls(1) = num_color_balls(1) then
9 dbms_output.put_line('Arrays are equal');
10 else
11 dbms_output.put_line('Arrays are not equal');
12 end if;
13 end;
14 /
Arrays are equal
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQL>
In the traditional example what will be the result if num_color_balls is basket(4,3,2,1)?
ReplyDeleteStraight away not equal, Check the count of elements in the array, if not equal then why waste time in comparing them. If you are looking for subset of data will have to use Multiset Operators.
Delete