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.
DECLARE
  l_sal emp.sal%TYPE;
  p_empno emp.empno%TYPE := 123; -- Invalid empno number
BEGIN

  BEGIN
    SELECT sal
    INTO l_sal
    FROM emp
    WHERE empno = p_empno;
  EXCEPTION
    WHEN no_data_found THEN
      l_sal := 0;
  END;
  
END;
/
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.
DECLARE
  l_sal emp.sal%TYPE;
  p_empno emp.empno%TYPE := 123; -- Invalid empno number
BEGIN

  SELECT NVL(MAX(sal), 0)
  INTO l_sal
  FROM emp
  WHERE empno = p_empno;
  
END;
/
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):
DECLARE
  l_sal emp.sal%TYPE;
  p_empno emp.empno%TYPE := 123; -- Invalid empno number
BEGIN

  SELECT NVL(MAX(sal), 0)
  INTO l_sal
  FROM emp
  WHERE empno = p_empno
  HAVING count(1) <= 1;
  
END;
/

5 comments:

  1. DECLARE
    l_sal emp.sal%TYPE;
    p_empno emp.empno%TYPE := 123; -- Invalid empno number
    BEGIN
    SELECT NVL( ( SELECT sal
    FROM emp
    WHERE empno = p_empno
    ), 0 )
    INTO l_sal
    FROM DUAL;
    END;
    /

    ReplyDelete
  2. Martin,

    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.

    ReplyDelete
  3. Oh boy - Tom is not going to like this :-)

    HAVING count(1) <= 1;

    Lars

    ReplyDelete
  4. Technique 2 could be useful when, as you say, you know we get no more than 1 row back. However you're workaround in 3 goes back to square 1 catching the no_data_found exception.

    I did find the blog interesting though just to remind me how sql behaves with different operators like 'having'. Thanks

    ReplyDelete
  5. Martin,

    IMHO, Technique 2 will confuse people looking at the code later on and it does not offer any percieved benefit. Why even use this when the first technique is simple and looks clean?

    ReplyDelete