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

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 2

Implicit cursors require anonymous buffer memory.

Explicit cursors can be executed again and again by using their name.They are stored in user defined memory space rather than being stored in an anonymous buffer memory and hence can be easily accessed afterwards.

Answer 3

In PL/SQL, A cursor is a pointer to this context area. It contains all the information needed for processing the statement.

Implicit Cursors: Implicit cursors are automatically created by Oracle whenever an SQL statement is executed, when there is no explicit cursor for the statement. Programmers cannot control the implicit cursors and the information in it.

Explicit Cursors: Explicit cursors are programmer-defined cursors for gaining more control over the context area. An explicit cursor should be defined in the declaration section of the PL/SQL Block. It is created on a SELECT Statement which returns more than one row.

The syntax for creating an explicit cursor is:

CURSOR cursor_name IS select_statement; 

Answer 4

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 5

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 6

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.