|
|
|
Re: compare 2 collections data [message #665773 is a reply to message #665763] |
Wed, 20 September 2017 06:36 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
You completely misunderstand BULK COLLECT, LIMIT and %NOTFOUND
LOOP
FETCH c1 BULK COLLECT INTO l_emp_tab LIMIT 5;
EXIT WHEN c1%notfound;
END LOOP;
Loop iteration 1 will fetch first 5 rows into l_emp_tab.
Loop iteration 2 will fetch next 5 rows into l_emp_tab thus overwriting first 5 rows.
Loop iteration 3 will fetch remaining 4 rows into l_emp_tab thus overwriting second set of 5 rows.
As a result l_emp_tab will have last 4 rows only. But imagine table EMP has 15 row. Then there would be iteration 4 and therefore l_emp_tab would end up empty after the loop.
Now about comparing in chunks. Then you'll end up selecting chunks of one of the tables as many times as there are chunks in another. It is simpler and more efficient (unless either of two collections is to large to keep in memory) to chunk largest table only. Something like:
DECLARE
TYPE t_emp_tab IS
TABLE OF emp%rowtype;
l_emp_tab t_emp_tab;
TYPE t_dept_tab IS
TABLE OF dept%rowtype;
l_dept_tab t_dept_tab;
CURSOR c1 IS
SELECT
*
FROM
emp;
CURSOR c2 IS
SELECT
*
FROM
dept;
BEGIN
OPEN c2;
FETCH c2 BULK COLLECT INTO l_dept_tab;
CLOSE c2;
OPEN c1;
LOOP
FETCH c1 BULK COLLECT INTO l_emp_tab LIMIT 5;
EXIT WHEN l_emp_tab.count = 0;
FOR i IN 1..l_emp_tab.count LOOP
FOR j IN 1..l_dept_tab.count LOOP
IF l_dept_tab(j).deptno = l_emp_tab(i).deptno
THEN
dbms_output.put_line(l_emp_tab(i).ename);
END IF;
END LOOP;
END LOOP;
END LOOP;
CLOSE c1;
END;
/
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
PL/SQL procedure successfully completed.
SQL>
SY.
|
|
|
|
Re: compare 2 collections data [message #665775 is a reply to message #665774] |
Wed, 20 September 2017 07:27 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
It is possible but you'll have to read one of the tables multiple times:
DECLARE
TYPE t_emp_tab IS
TABLE OF emp%rowtype;
l_emp_tab t_emp_tab;
TYPE t_dept_tab IS
TABLE OF dept%rowtype;
l_dept_tab t_dept_tab;
CURSOR c1 IS
SELECT
*
FROM
emp;
CURSOR c2 IS
SELECT
*
FROM
dept;
BEGIN
OPEN c1;
LOOP
FETCH c1 BULK COLLECT INTO l_emp_tab LIMIT 5;
EXIT WHEN l_emp_tab.count = 0;
FOR i IN 1..l_emp_tab.count LOOP
OPEN c2;
LOOP
FETCH c2 BULK COLLECT INTO l_dept_tab LIMIT 3;
EXIT WHEN l_dept_tab.count = 0;
FOR j IN 1..l_dept_tab.count LOOP
IF l_dept_tab(j).deptno = l_emp_tab(i).deptno
THEN
dbms_output.put_line(l_emp_tab(i).ename);
END IF;
END LOOP;
END LOOP;
CLOSE c2;
END LOOP;
END LOOP;
CLOSE c1;
END;
/
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
PL/SQL procedure successfully completed.
SQL>
SY.
|
|
|