What is implicit cursor and how is it used by Oracle. Are there multiple implicit cursors existed in Oracle ? Thank you in advance.
All Oracle statements use cursors to manipulate rows, so every SQL statement that doesn't have a named (explicit) cursor has a default (implicit) cursor.
UPDATE a set b = 1; DELETE a where b = 1; SELECT * from a where b = 1;
all use an implicit cursor to do their work.
The implicit cursor can be referenced by SQL%. There are some special attributes defined by Oracle that help you to work with implicit cursors, like SQL%ROWCOUNT, which tells you how many rows were affected by the last implicit cursor:
UPDATE a set b = 1; dbms_output (SQL%ROWCOUNT);
and you'd get a message with the number of rows the UPDATE affected.
See these links for good explanations:
Now, when you want to manage the cursor yourself, you have to explicitly set it up with a name of your choosing and specify the query to run. This link has a pretty good explanation:
Then there's the "cursor for loop" which is as shorthand way of setting up an explicit cursor and is rather neat...
Here's a simple one, where "cur" is my name for a cursor on a select from t1...:
-- there exist tables t1 and t2 with columns c1, c2 and c3 in each... for cur (select c1, c2, c3 from t1) loop -- defines implicit cursor 'cur' within for loop -- inside the 'for' loop, you have access to cur.c1, cur.c2 and cur.c3 select t2.c1, t2.c2, cur.c3 || t2.c3 from t2 where t2.c1 = cur.c1 and t2.c2 = cur.c2; end loop; -- implicit cursor desroyed outside scope of loop -- do other things...
I believe I got all the syntax right but I didn't actually test it.
There's another example, here: Example Cursor
An implicit cursor is created for every SQL statement that doesn't have an explicit cursor. They're the interface the database uses to retrieve data for every task it does, whether from the data dictionary, user tables or x$ tables (which may just be memory based).