What is the difference between explicit and implicit cursors in Oracle?

Question

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

Answer 1

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).

Answer 2

An explicit cursor is defined as such in a declaration block:

DECLARE 
CURSOR cur IS 
  SELECT columns FROM table WHERE condition;
BEGIN
...

an implicit cursor is implented directly in a code block:

...
BEGIN
   SELECT columns INTO variables FROM table where condition;
END;
...

Answer 3

With explicit cursors, you have complete control over how to access information in the database. You decide when to OPEN the cursor, when to FETCH records from the cursor (and therefore from the table or tables in the SELECT statement of the cursor) how many records to fetch, and when to CLOSE the cursor. Information about the current state of your cursor is available through examination of the cursor attributes.

See http://www.unix.com.ua/orelly/oracle/prog2/ch06_03.htm for details.

Answer 4

An implicit cursor is one created "automatically" for you by Oracle when you execute a query. It is simpler to code, but suffers from

• inefficiency (the ANSI standard specifies that it must fetch twice to check if there is more than one record)
• vulnerability to data errors (if you ever get two rows, it raises a TOO_MANY_ROWS exception)

Example

SELECT col INTO var FROM table WHERE something;

An explicit cursor is one you create yourself. It takes more code, but gives more control - for example, you can just open-fetch-close if you only want the first record and don't care if there are others.

Example

DECLARE   
  CURSOR cur IS SELECT col FROM table WHERE something; 
BEGIN
  OPEN cur;
  FETCH cur INTO var;
  CLOSE cur;
END;

Answer 5

Every SQL statement executed by the Oracle database has a cursor associated with it, which is a private work area to store processing information. Implicit cursors are implicitly created by the Oracle server for all DML and SELECT statements.

You can declare and use Explicit cursors to name the private work area, and access its stored information in your program block.

Answer 6

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 7

These days implicit cursors are more efficient than explicit cursors.

http://www.oracle.com/technology/oramag/oracle/04-sep/o54plsql.html

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1205168148688

Answer 8

Google is your friend: http://docstore.mik.ua/orelly/oracle/prog2/ch06_03.htm

PL/SQL issues an implicit cursor whenever you execute a SQL statement directly in your code, as long as that code does not employ an explicit cursor. It is called an "implicit" cursor because you, the developer, do not explicitly declare a cursor for the SQL statement.

An explicit cursor is a SELECT statement that is explicitly defined in the declaration section of your code and, in the process, assigned a name. There is no such thing as an explicit cursor for UPDATE, DELETE, and INSERT statements.

Answer 9

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 10

A cursor is a SELECTed window on an Oracle table, this means a group of records present in an Oracle table, and satisfying certain conditions. A cursor can SELECT all the content of a table, too. With a cursor you can manipulate Oracle columns, aliasing them in the result. An example of implicit cursor is the following:

BEGIN
   DECLARE
      CURSOR C1
      IS
         SELECT DROPPED_CALLS FROM ALARM_UMTS;

      C1_REC   C1%ROWTYPE;
   BEGIN
      FOR C1_REC IN C1
      LOOP
         DBMS_OUTPUT.PUT_LINE ('DROPPED CALLS: ' || C1_REC.DROPPED_CALLS);
      END LOOP;
   END;
END;
/

With FOR ... LOOP... END LOOP you open and close the cursor authomatically, when the records of the cursor have been all analyzed.

An example of explicit cursor is the following:

BEGIN
   DECLARE
      CURSOR C1
      IS
         SELECT DROPPED_CALLS FROM ALARM_UMTS;

      C1_REC   C1%ROWTYPE;
   BEGIN
      OPEN c1;

      LOOP
         FETCH c1 INTO c1_rec;

         EXIT WHEN c1%NOTFOUND;

         DBMS_OUTPUT.PUT_LINE ('DROPPED CALLS: ' || C1_REC.DROPPED_CALLS);
      END LOOP;

      CLOSE c1;
   END;
END;
/

In the explicit cursor you open and close the cursor in an explicit way, checking the presence of records and stating an exit condition.

Answer 11

implicit cursor are return only one record,no need to call it but explicit cursors are call manually, we can retrive more than one record depending upon the user requirement