What is the difference between explicit and implic

Question

I am a bit rusty on my cursor lingo in PL/SQL. Anyone know this?

Answer 1

Implicit cursor returns only one record and are called automatically. However, explicit cursors are called manually and can return more than one record.

Answer 2

I know this is an old question, however, I think it would be good to add a practical example to show the difference between the two from a performance point of view.

From a performance point of view, Implicit cursors are faster.

Let's see the performance difference between the two:

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2    l_loops  NUMBER := 100000;
  3    l_dummy  dual.dummy%TYPE;
  4    l_start  NUMBER;
  5
  6    CURSOR c_dual IS
  7      SELECT dummy
  8      FROM   dual;
  9  BEGIN
 10    l_start := DBMS_UTILITY.get_time;
 11
 12    FOR i IN 1 .. l_loops LOOP
 13      OPEN  c_dual;
 14      FETCH c_dual
 15      INTO  l_dummy;
 16      CLOSE c_dual;
 17    END LOOP;
 18
 19    DBMS_OUTPUT.put_line('Explicit: ' ||
 20                         (DBMS_UTILITY.get_time - l_start) || ' hsecs');
 21
 22    l_start := DBMS_UTILITY.get_time;
 23
 24    FOR i IN 1 .. l_loops LOOP
 25      SELECT dummy
 26      INTO   l_dummy
 27      FROM   dual;
 28    END LOOP;
 29
 30    DBMS_OUTPUT.put_line('Implicit: ' ||
 31                         (DBMS_UTILITY.get_time - l_start) || ' hsecs');
 32  END;
 33  /
Explicit: 332 hsecs
Implicit: 176 hsecs

PL/SQL procedure successfully completed.

So, a significant difference is clearly visible.

More examples here.

Answer 3

Explicit...

cursor foo is select * from blah; begin open fetch exit when close cursor yada yada yada

don't use them, use implicit

cursor foo is select * from blah;

for n in foo loop x = n.some_column end loop

I think you can even do this

for n in (select * from blah) loop...

Stick to implicit, they close themselves, they are more readable, they make life easy.

Answer 4

In answer to the first question. Straight from the Oracle documentation

A cursor is a pointer to a private SQL area that stores information about processing a specific SELECT or DML statement.

Answer 5

As stated in other answers, implicit cursors are easier to use and less error-prone.

And Implicit vs. Explicit Cursors in Oracle PL/SQL shows that implicit cursors are up to two times faster than explicit ones too.

It's strange that no one had yet mentioned Implicit FOR LOOP Cursor:

begin
  for cur in (
    select t.id from parent_trx pt inner join trx t on pt.nested_id = t.id
    where t.started_at > sysdate - 31 and t.finished_at is null and t.extended_code is null
  )
  loop
    update trx set finished_at=sysdate, extended_code = -1 where id = cur.id;
    update parent_trx set result_code = -1 where nested_id = cur.id;
  end loop cur;
end;

Another example on SO: PL/SQL FOR LOOP IMPLICIT CURSOR.

It's way more shorter than explicit form.

This also provides a nice workaround for updating multiple tables from CTE.

Answer 6

An explicit cursor is one you declare, like:

CURSOR my_cursor IS
  SELECT table_name FROM USER_TABLES

An implicit cursor is one created to support any in-line SQL you write (either static or dynamic).