Which cursor to use to execute a DML ?

[eeriehunk]

HI All,
I know that there are two types of cursors. Implicit and Explicit cursors. But in some places I read there were more than 2, actually 5. So what are they. And this interview question has been a mystery and where this search has all started:

Delete from emp wher e emp_id >1000; 

Which one of the following types of cursors do you use to execute this DML statement in the above sample code?
Explicit, Implicit, Static, Dynamic and Referenced

As per my knowledge a cursor is a temp buffer to load data into other variables or loop through this buffer and work on the data. But what is a cursor got to do with a DML statement?
Thanks in advance,
Aj

[madankarmukta]

@eeriehunk
DMLs like INSERT,UPDATE,DELETE,SELECT INTO have implicit cursor associated with them by default.

Update and delete operation have the Implicit cursor associted with them to point the set of Rows getting affected by the opeartion and Insert opeartion need the implicit cursor in order to hold the data for insertion.

All these opration make use of implicit cursor by default unless the Explicit Cursor are defined for them.

I hope , I cleared you doubt.

Thanks!

[amitpatel66]

Explicit Cursor - used for the select statements

Referenced Cursor - Used when you need to send the set of records as an output to the calling side. This cursor used as out parameter in procedures

Static/Dynamic Cursor - Used mainly for SELECT statements

Implicit Cursor - Created implicitly for any DML operaition within a procedure. They are created Implicitly and you need not seperately create a cursor for DML statements

[eeriehunk]

Hi Guys,
Thanks for your replies, and it’s clear that DML statements use Implicit Cursors implicitly. But what’s still confusing is, why does the question says.. "What cursors do you use to execute this DML statement ". If it’s implicit, we don’t have to USE anything to execute it? Unless this a wrongly phrased question. If it is, kindly ignore it and I appreciate your replies.
Regards,
Aj

[sonomaRIK]

@eeriehunk
Your interpretation is correct, in that one could/may read too much into the question.

The simpler way to have worded that, as the above have already answer 'what cursors to you use' is when using a DML statement, indicate which type of cursor is used. That is, implicit and other indicators mentioned. The inclussion of the word 'use' makes one believe they are having to EXPLICITLY indicate the cursor, which as you have been shown, is not necessary. It is a 'trick' question if one want to go that route.

r