I try to compare to result of sets from SYS_REFCURSOR:
declare
v_RC sys_refcursor;
v_RC_union sys_refcursor;
v_REC userA.table1%rowtype;
v_REC_union userB.table2%rowtype;
i number := 0;
j number := 0;
z number := 0;
begin
open v_RC for select * from userA.table1;
open v_RC_union for select * from userB.table2;
loop fetch v_RC into v_REC;
exit when v_RC%notfound;
i := i+1;
loop fetch v_RC_union into v_REC_union;--
exit when v_RC_union%notfound;
j := j+1;
If v_REC_union.id= v_REC.id then
z :=z+1;
End if;
end loop;
end loop;
close v_RC;
close v_RC_union;
dbms_output.put_line(z);--too small
dbms_output.put_line('v_RC: '||i||', v_REC_union: '||j);
end;
I know that both cursor get correct number of rows (i & j are ok), but result of equal rows (z) is wrong (too small).
You are consuming all of the rows from v_RC_union during the first iteration of the outer loop. You seem to be expecting that you will see all of those rows every time you loop, but the second ref cursor is not reset to the start (and can't be).
If you add debugs to the code you can see that happening; I've created two small dummy tables with three matching IDs and one non-matching ID in each:
create table table1 (id, dummy) as select level + 1, 'x' from dual connect by level <= 4;
create table table2 (id, dummy) as select level, 'x' from dual connect by level <= 4;
declare
...
begin
open v_RC for select * from table1;
open v_RC_union for select * from table2;
loop
fetch v_RC into v_REC;
exit when v_RC%notfound;
i := i+1;
dbms_output.put_line('i: ' || i || ' table1 id ' || v_REC.id);
loop
fetch v_RC_union into v_REC_union;--
exit when v_RC_union%notfound;
j := j+1;
dbms_output.put_line('i: ' || i || ' j: ' || j || ' table1 id ' || v_REC_union.id);
If v_REC_union.id= v_REC.id then
z :=z+1;
end if;
end loop;
end loop;
close v_RC;
close v_RC_union;
dbms_output.put_line('z: ' || z);--too small
dbms_output.put_line('v_RC: '||i||', v_REC_union: '||j);
end;
/
The output from that is:
i: 1 table1 id 2
i: 1 j: 1 table1 id 1
i: 1 j: 2 table1 id 2
i: 1 j: 3 table1 id 3
i: 1 j: 4 table1 id 4
i: 2 table1 id 3
i: 3 table1 id 4
i: 4 table1 id 5
z: 1
v_RC: 4, v_REC_union: 4
On the first iteration, when i is 1, you do the inner loop and fetch all of the rows from v_RC_union, only stopping when notfound. Assuming one of those does match the first v_RC row's ID then z is incremented, so it's either 1 or 0 after that first outer loop.
On the second iteration, when i is 2, the inner loop exits immediately after the first fetch because you have already exhausted the v_RC_union result set. You fetched all of its rows in the first iteration, so there is nothing left to fetch. So, no that second v_RC row's ID is never compared with anything, and z isn't touched, as it doesn't get that far.
And so on for all the other iterations of the outer loop. The inner loop always exits immediately and doesn't do anything useful.
You could requery table2 for the specific ID each time as you show in your answer, though if you're only counting them and not using any of the column values you don't really need the open/loop/fetch and could just change that query to a count into a scalar variable. That doesn't seem very efficient though, even for row-by-row processing.
If you want to stick with the whole-table queries you could use collections:
declare
type t_table1 is table of table1%rowtype;
type t_table2 is table of table2%rowtype;
v_table1 t_table1;
v_table2 t_table2;
i number := 0;
j number := 0;
z number := 0;
begin
select * bulk collect into v_table1 from table1;
select * bulk collect into v_table2 from table2;
for r1 in v_table1.first..v_table1.last loop
i := i+1;
dbms_output.put_line('i: ' || i || ' table1 id ' || v_table1(r1).id);
j := 0;
for r2 in v_table2.first..v_table2.last loop
j := j+1;
dbms_output.put_line('i: ' || i || ' j: ' || j || ' table2 id ' || v_table2(r2).id);
if v_table2(r2).id = v_table1(r1).id then
z := z+1;
end if;
end loop;
end loop;
dbms_output.put_line('z: ' || z);
dbms_output.put_line('v_RC: '||i||', v_REC_union: '||j);
end;
/
By looping over the two collections each i iteration sees every j row and can compare the IDs.
i: 1 table1 id 2
i: 1 j: 1 table2 id 1
i: 1 j: 2 table2 id 2
i: 1 j: 3 table2 id 3
i: 1 j: 4 table2 id 4
i: 2 table1 id 3
i: 2 j: 1 table2 id 1
i: 2 j: 2 table2 id 2
i: 2 j: 3 table2 id 3
i: 2 j: 4 table2 id 4
i: 3 table1 id 4
i: 3 j: 1 table2 id 1
i: 3 j: 2 table2 id 2
i: 3 j: 3 table2 id 3
i: 3 j: 4 table2 id 4
i: 4 table1 id 5
i: 4 j: 1 table2 id 1
i: 4 j: 2 table2 id 2
i: 4 j: 3 table2 id 3
i: 4 j: 4 table2 id 4
z: 3
v_RC: 4, v_REC_union: 4
This still seems like a lot of work when you can use set operators to count and compare directly in plain SQL, e.g.:
select
(select count(*) from table1) as i,
(select count(*) from table2) as j,
(select count(*) from (select id from table1 intersect select id from table2)) as z
from dual;
I J Z
---------- ---------- ----------
4 4 3
There doesn't really seem to be a lot of benefit in using PL/SQL here; the SQL version possibly does more work as it's querying both tables twice (though is likely to be hitting caches), but you could avoid that with CTEs anyway and it's likely to be faster overall than using PL/SQL.