x

What is implicit cursor and how is it used by Oracle ?

What is implicit cursor and how is it used by Oracle. Are there multiple implicit cursors existed in Oracle ? Thank you in advance.

more ▼

asked Jan 07, 2010 at 12:10 PM in Default

OracleApprender gravatar image

OracleApprender
771 69 73 75

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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:

Cursors

More Cursors

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:

Explicit Cursors

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

more ▼

answered Jan 10, 2010 at 09:45 PM

KillerDBA gravatar image

KillerDBA
1.5k 8 9 10

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Jan 07, 2010 at 02:40 PM

Andrew Mobbs gravatar image

Andrew Mobbs
1.5k 1 3

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x713
x378
x9

asked: Jan 07, 2010 at 12:10 PM

Seen: 2078 times

Last Updated: Jan 13, 2010 at 05:10 PM