Thread: Compare utility against two table

Hi Anand,
The following is a sample query which will compare the values from 2 tables.
I have used 2 cursors (cursor for loop) so that its simple & easily understandable. I have also used dbms output messages so that the process flow & the result can be viewed during execution.

SAMPLE CODE
===========
Create or replace procedure comp as
l_empno number(5);
l_new_empno number(5);
cursor l_emp_cur is
select empno from emp where sal > 3000 and deptno = 10;
cursor l_emp_new_cur is
select empno from emp_new where sal > 3000 and deptno = 10;
begin
for l_emp_rec in l_emp_cur
loop
dbms_output.put_line('after notfound if l_empno '||to_char(l_emp_rec.empno));
for l_emp_new_rec in l_emp_new_cur
loop
dbms_output.put_line('after notfound if l_new_empno '||to_char(l_emp_new_rec.empno));
if l_emp_rec.empno = l_emp_new_rec.empno then
dbms_output.put_line('same empno'||l_emp_rec.empno||' & '||l_emp_new_rec.empno);
else
dbms_output.put_line('different empno'||l_emp_rec.empno||' & '||l_emp_new_rec.empno);
end if;
if l_emp_new_cur%notfound then
exit;
end if;
end loop;
if l_emp_cur%notfound then
exit;
end if;
end loop;
end;
/

Using the above sample code, u can generate ur code with more enhancements whichever u require.
Hopefully its clear & understandable :-)

*** Mangai Varma ***