| Cursors [message #258495] |
Sun, 12 August 2007 14:48  |
swapnajojo
Messages: 40
Registered: June 2007
Location: India
|
Member |
|
|
Hi Team,
I had faced the following question in an interview recently.
whats the best option for this question
Q)Suppose i have a to select a field from a table ,
which is the best to use
1)Select into or 2)Explicit cursor and the reason for the same
Thanks In Advance
Binu
|
|
|
|
|
|
|
|
| Re: Cursors [message #258516 is a reply to message #258495] |
Sun, 12 August 2007 22:55  |
TimJF
Messages: 11
Registered: August 2007
|
Junior Member |
|
|
I'm no expert, but I reckon it depends whether or not you are going to fetch more than one row from the database.
If you are certain that you will only return the value from one row, than a 'SELECT INTO' would be fine.
Otherwise, given the choices you have listed, I would use an explicit cursor.
Hope this helps.
|
|
|
|
|
|
|
|
| Re: Cursors [message #258597 is a reply to message #258548] |
Mon, 13 August 2007 02:48 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Since they bothered asking it in an interview, the answer they were most likely searching for was Explicit Cursor, because SELECT INTO performs an extra fetch to ensure that it returns only one row.
For PL/SQL, this is a myth. Oracle does not perform the extra fetch; SELECT INTO is just as fast as an explicit cursor.
For the pre-compilers, it could be a factor though. Depending on one of the compiler options, SELECT INTO may perform an extra fetch in Pro*C, Pro*COBOL, etc. This may also be the case with OCI - I'm not sure.
Assuming you are using PL/SQL, the only considerations are:
- whether you want to protect against TOO_MANY_ROWS exception
- whether you want to share the cursor and open/fetch it in many places.
If you want to protect against TOO_MANY_ROWS, you can either select on a unique key or add AND ROWNUM = 1 to your SELECT INTO query. Both of these are probably tidier that using an explicit cursor.
If you want to share the SQL, you must use an explicit cursor.
So, to sum up: You could tell the interviewer that explicit cursors should only be used when SQL needs to be shared, and that it is a common misconception that Explicit Cursors out-perform Implicit Cursors. That might start an argument - depends how convincing you can sound.
Ross Leishman
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
