Pages

Thursday, June 7, 2012

SELECT INTO Techinques

One thing that bothers a lot of PL/SQL developers is when they have to select some data from a table into local variables. The problem is that the data may or may not exist (i.e. return 0..1 rows). They're several ways to write this code, some taking longer than others which is the pain point for developers.

I've listed out various ways to do this below and hopefully it'll help you find some easier ways to solve this issue.

The first example is the classic case of handling the 0..1 rows issue in PL/SQL. They're a few issues (not problems) that I have with this. The first is that it adds some additional lines of code (highlighted) which can make the code harder to follow. The other issue is that instead of using "WHEN no_data_found" some developers tend to use "WHEN OTHERS" which is guaranteed to give you some false positives in the long run.

Another way to get the value from the table is to use the aggregate function MAX. Since you're expecting 0..1 rows this will always return 1 row and eliminates the need to have the "BEGIN ..." block of code which saves 5 lines of code. You can handle what to do when no value is found by using the NVL function.

The only issue with the above example is when you can have 0..n rows but should only get 0..1. Ideally you should have table constraints in order to guarantee 0..1 rows but it's not always the case in some system. The following example shows how you can use the same technique as above but it will trigger an exception if n rows are found (which is what you want to do):

In my humble opinion you left out the most important one:Explicitly declare a cursor.All issues solved, except the fact that you must write a couple of extra lines of code. But a person who doesn't like writing code and still doesn't have a tool with templates for common constructs (like ultraedit, plsql developer etc.) to do it for you, should, I think, get out of the business.

Once the cursor is declared, open fetch and close. Then "IF c_abc%NOTFOUND THEN ..."If you expect the possibility of more than one row, do a bulk fetch and check the number of rows in the collection that you fetched into.

Simple, readable, always the same constructs, easy. Just a couple (and no more than that) of lines extra.