compare if the cursor value exists in another cursor - oracle plsql

Question

I have two cursors I am comparing. I would compare cursor1 if exists in all rows of cursor2. I tried below but it seems stopping at 1st match.

  CURSOR cursor1 IS
  SELECT col1, col2 FROM table1;
  CURSOR cursor2 IS
  SELECT col1, col3 FROM table2;
  c1_rec cursor1%ROWTYPE;
  c2_rec cursor2%ROWTYPE;
BEGIN
  OPEN cursor1;
  OPEN cursor2;
  LOOP
    FETCH cursor1 INTO c1_rec;
    FETCH cursor2 INTO c2_rec;
    EXIT WHEN cursor1%NOTFOUND OR cursor2%NOTFOUND; 

    IF c1_rec.col1 = c2_rec.col1 OR c1_rec.col2 = c2_rec.col3 THEN
      DBMS_OUTPUT.PUT_LINE('Match in row: ' || SQL%ROWCOUNT);
    END IF;
  END LOOP;
  CLOSE cursor1;
  CLOSE cursor2;
END;
/

Answer 1

I think your logic might be incorrect. Your loop won't compare cursor1 if exists in all rows of cursor2. It is only comparing to one row of cursor2, then moving to the next row of both cursors. I think you want a loop within a loop.

This would do what you have described:

  CURSOR cursor1 IS
  SELECT col1, col2 FROM table1;
  CURSOR cursor2 IS
  SELECT col1, col3 FROM table2;
  c1_rec cursor1%ROWTYPE;
  c2_rec cursor2%ROWTYPE;
BEGIN
  OPEN cursor1;
  LOOP
    FETCH cursor1 INTO c1_rec;
    OPEN cursor2;
    LOOP   
       FETCH cursor2 INTO c2_rec;
       EXIT WHEN cursor1%NOTFOUND OR cursor2%NOTFOUND; 

       IF c1_rec.col1 = c2_rec.col1 OR c1_rec.col2 = c2_rec.col3 THEN
         DBMS_OUTPUT.PUT_LINE('Match in row: ' || SQL%ROWCOUNT);
       END IF;
    END LOOP
    CLOSE cursor2;
  END LOOP;
  CLOSE cursor1;
END;
/

However, I must warn you that this will give terrible performance for data larger than 1000 rows. You would get much better performance by doing a JOIN.

Example:

SELECT t1.col1, t1.col2, t2.col1, t2.col3
FROM table1, table2
WHERE t1.col1 = t2.col1
   OR t1.col2 = t2.col3;

Databases will complete a query like this in milliseconds, even for millions of rows.