What is the difference between foreach cursor explicit and foreach cursor implicit

Question

Here I have a sample where I've used explicit cursors to solve a task.

set serveroutput on
DECLARE
  CURSOR c_1
  IS
    SELECT 
        LNR, LFNDNR, DATUM, STUECK, ANR
    FROM 
        lagerbuchung;
   CURSOR c_2(p_LNR INT)
   IS
    SELECT 
       ORT
    FROM 
        LAGER
    WHERE
      lager.LNR = p_LNR; 
    v_ort varchar(45);
BEGIN
  FOR v_rec IN c_1
  LOOP
    open c_2(v_rec.LNR);
    fetch c_2into v_ort;
    DBMS_OUTPUT.PUT_LINE(': ' || v_rec.LNR || ' : ' || v_rec.LFNDNR ||' : ' || v_rec.DATUM ||' : ' || v_rec.STUECK || ' : ' || v_rec.ANR || ' : ' || v_ort );
    close c_2;
  END LOOP;
END;

So my question is, what is the difference between explicit foreach cursor and implicit foreach cursor?
Is the code similar? Which one is better to use? I've tried to reproduce the code but with using implicit foreach cursors, but didn't manage and gave up.

Answer 1

An explicit cursor should explicitly be defined and declared pointing to a private SQL area, while implicit cursor is just a SQL statement which doesn't need to be opened by the anyone, as already been opened and closed by the database on its own.

For your case, using such code

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  v_ort varchar(45);
BEGIN
  FOR v_rec IN 
  (
    SELECT l.ort, lb.lnr, lb.lfndnr, lb.datum, lb.stueck, lb.anr
      FROM lager l
      JOIN lagerbuchung lb
     WHERE lb.lnr = l.lnr 
  )  
  LOOP
    DBMS_OUTPUT.PUT_LINE(': ' || v_rec.lnr || ' : ' || v_rec.lfndnr ||
                         ' : ' || v_rec.datum || ' : ' || v_rec.stueck ||
                         ' : ' || v_rec.anr || ' : ' || v_rec.ort);
  END LOOP;
END;

will be enough to convert the current one to an implicit cursor