Menu
Whenever a query in PL/SQL is written where only one row is expected, there are two ways it is often done:
SELECT INTO, and check for TOO_MANY_ROWS and NO_DATA_FOUND exceptions.Though both the approaches yield the same result, what are the pros and cons of these approaches?
287
Use of Cursor The major function of a cursor is to retrieve data, one row at a time, from a result set, unlike the SQL commands which operate on all the rows in the result set at one time. Cursors are used when the user needs to update records in a singleton fashion or in a row by row manner, in a database table.
Cursors are used by database programmers to process individual rows returned by database system queries. Cursors enable manipulation of whole result sets at once. In this scenario, a cursor enables the sequential processing of rows in a result set.
What is one of the advantages of using parameters with a cursor? You can use a cursor FOR loop. You can declare the cursor FOR UPDATE. You do not need to DECLARE the cursor at all.
Cursors could be used in some applications for serialized operations as shown in example above, but generally they should be avoided because they bring a negative impact on performance, especially when operating on a large sets of data.
When a select statement is expected to return exactly one row then a "SELECT INTO" is the better approach. Yes, many developers prefer to use a cursor and fetch only one row because it saves them the "bother" of dealing with NO_DATA_FOUND - i.e. they sweep the problem under the carpet and leave the user with a mysterious bug. I blogged about this bad practice* recently.
(* a bad practice that is often sadly enshrined in project PL/SQL standards!)
As for counting and then querying, that just doubles the work so is to be avoided too.
180
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
