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

user_11817972 0
Viewed 209

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.

database-cursor plsql oracle sql
2 Answers

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

user_58413060

If you explicitly declare a variable for cursor then this cursor is explicit, e.g.:

declare
  cursor c1 is
    select 1 as n from dual;
begin
  for r in c1 loop
    dbms_output.put_line(r.n);
  end loop;
end;

If you inline sql queries in your code (in FOR LOOP or SELECT INTO), PLSQL generates implicit (internal/undeclared) cursors, e.g.:

begin
  for r in (select 1 as n from dual) loop
    dbms_output.put_line(r.n);
  end loop;
end;

what is the difference between foreach cursor explicit and foreach cursor implicit? Is the code similar ? Which one is better to use?

The difference is that you need to declare a variable for an explicit cursor. You also need to open, close, and fetch an explicit cursor. For this reason, implicit cursors often result in much neater code.

Still, there are use cases for explicit cursors (see Practical life examples of oracle explicit cursor use):

  • You need to reuse the same cursor multiple times in the same PLSQL program. With an explicit cursor, you can reuse a variable without having to duplicate a query.
  • You want to use bulk collection with the LIMIT clause. With an explicit cursor, you can set the limit directly.

I've tried to reproduce the code but with using foreach cursor implicit, but didn't manage and gave up.

You can directly translate your code using implicit cursors:

declare
  v_ort varchar(45);
begin
  for v_rec in (SELECT LNR, LFNDNR, DATUM, STUECK, ANR FROM lagerbuchung) loop
    SELECT ORT into v_ort FROM LAGER WHERE lager.LNR = v_rec.LNR;
    DBMS_OUTPUT.PUT_LINE(': ' || v_rec.LNR || ' : ' || v_rec.LFNDNR ||
                         ' : ' || v_rec.DATUM || ' : ' || v_rec.STUECK ||
                         ' : ' || v_rec.ANR || ' : ' || v_ort);
  end loop;
end;

But @Barbaros Özhan already posted a more efficient version using a single query.

user_4515180
Related