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; /
DECLARE
ReplyDeletel_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;
/
Martin,
ReplyDeleteIn 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.
Oh boy - Tom is not going to like this :-)
ReplyDeleteHAVING count(1) <= 1;
Lars
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.
ReplyDeleteI did find the blog interesting though just to remind me how sql behaves with different operators like 'having'. Thanks
Martin,
ReplyDeleteIMHO, 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?