CODEWITHSUNDEEP
oracle-databaseplsql
poddroid
poddroid

Reputation: 855

Pros/cons using cursor approach vs. begin-end-exception approach for single-row queries in PL/SQL

Whenever a query in PL/SQL is written where only one row is expected, there are two ways it is often done:

  1. Use an implicit SELECT INTO, and check for TOO_MANY_ROWS and NO_DATA_FOUND exceptions.
  2. Use an explicit cursor and just fetch the first row from the cursor.

Though both the approaches yield the same result, what are the pros and cons of these approaches?

Upvotes: 5

Views: 887

Answers (1)

Tony Andrews
Tony Andrews

Reputation: 132580

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.

Upvotes: 6

Related Questions