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
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).
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
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.
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.
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.
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.
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;
...
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.
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).
From a performance point of view, Implicit cursors are faster.
Let's compare the performance between an explicit and implicit cursor:
SQL> DECLARE
2 l_loops NUMBER := 100000;
3 l_dummy dual.dummy%TYPE;
4 l_start NUMBER;
5 -- explicit cursor declaration
6 CURSOR c_dual IS
7 SELECT dummy
8 FROM dual;
9 BEGIN
10 l_start := DBMS_UTILITY.get_time;
11 -- explicitly open, fetch and close the cursor
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 -- implicit cursor for loop
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. Implicit cursor is much faster than an explicit cursor.
More examples here.
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.
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.
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.
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;
...
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
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.
Implicit cursor returns only one record and are called automatically. However, explicit cursors are called manually and can return more than one record.
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.
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;
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.
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.
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.
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.
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
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.
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).
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.
1.CURSOR: When PLSQL issues sql statements it creates private work area to parse & execute the sql statement is called cursor.
2.IMPLICIT: When any PL/SQLexecutable block issues sql statement. PL/SQL creates implicit cursor and manages automatically means implcit open & close takes place. It used when sql statement return only one row.It has 4 attributes SQL%ROWCOUNT, SQL%FOUND, SQL%NOTFOUND, SQL%ISOPEN.
3.EXPLICIT: It is created & managed by the programmer. It needs every time explicit open,fetch & close. It is used when sql statement returns more than one row. It has also 4 attributes CUR_NAME%ROWCOUNT, CUR_NAME%FOUND, CUR_NAME%NOTFOUND, CUR_NAME%ISOPEN. It process several rows by using loop. The programmer can pass the parameter too to explicit cursor.
declare
cursor emp_cursor
is
select id,name,salary,dept_id
from employees;
v_id employees.id%type;
v_name employees.name%type;
v_salary employees.salary%type;
v_dept_id employees.dept_id%type;
begin
open emp_cursor;
loop
fetch emp_cursor into v_id,v_name,v_salary,v_dept_id;
exit when emp_cursor%notfound;
dbms_output.put_line(v_id||', '||v_name||', '||v_salary||','||v_dept_id);
end loop;
close emp_cursor;
end;
Implicit cursor returns only one record and are called automatically. However, explicit cursors are called manually and can return more than one record.
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.
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.
1.CURSOR: When PLSQL issues sql statements it creates private work area to parse & execute the sql statement is called cursor.
2.IMPLICIT: When any PL/SQLexecutable block issues sql statement. PL/SQL creates implicit cursor and manages automatically means implcit open & close takes place. It used when sql statement return only one row.It has 4 attributes SQL%ROWCOUNT, SQL%FOUND, SQL%NOTFOUND, SQL%ISOPEN.
3.EXPLICIT: It is created & managed by the programmer. It needs every time explicit open,fetch & close. It is used when sql statement returns more than one row. It has also 4 attributes CUR_NAME%ROWCOUNT, CUR_NAME%FOUND, CUR_NAME%NOTFOUND, CUR_NAME%ISOPEN. It process several rows by using loop. The programmer can pass the parameter too to explicit cursor.
declare
cursor emp_cursor
is
select id,name,salary,dept_id
from employees;
v_id employees.id%type;
v_name employees.name%type;
v_salary employees.salary%type;
v_dept_id employees.dept_id%type;
begin
open emp_cursor;
loop
fetch emp_cursor into v_id,v_name,v_salary,v_dept_id;
exit when emp_cursor%notfound;
dbms_output.put_line(v_id||', '||v_name||', '||v_salary||','||v_dept_id);
end loop;
close emp_cursor;
end;
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;
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.
From a performance point of view, Implicit cursors are faster.
Let's compare the performance between an explicit and implicit cursor:
SQL> DECLARE
2 l_loops NUMBER := 100000;
3 l_dummy dual.dummy%TYPE;
4 l_start NUMBER;
5 -- explicit cursor declaration
6 CURSOR c_dual IS
7 SELECT dummy
8 FROM dual;
9 BEGIN
10 l_start := DBMS_UTILITY.get_time;
11 -- explicitly open, fetch and close the cursor
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 -- implicit cursor for loop
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. Implicit cursor is much faster than an explicit cursor.
More examples here.
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.
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.
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.
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;
...
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;
...
Source: Stackoverflow.com