PL/SQL how to check if exists and return error code

Question

Disregard if using MERGE INTO is better in this case or not. I just wonder if I can check if the row exists or not. If not, then set the return code to 1 and return immediately. If yes, then continue to execute the rest of code and set return code to 0 in the end. Below code is not working as it always executes to the end. How should I fix it?

  BEGIN

    -- check 
    SELECT CASE
      WHEN NOT EXISTS (SELECT 1 FROM s WHERE s.col1 = 1 AND s.col2 = 2)
      THEN 1
    END
    INTO ret FROM dual;

    -- update
    UPDATE s
    SET s.col3 = 3
    WHERE s.col1 = 1 AND s.col2 = 2;

    COMMIT;

    SELECT 0 INTO ret FROM dual;
    RETURN ret;

  END foo;

What if I want be able to distinguish if it's s.col1 = 1 not exist or s.col2 = 2 not exist. And have 2 different return codes for them. How should I do in this case?

OraNob · Accepted Answer · 2012-07-27 08:33:15Z

4

I'm wondering if thre is any point checking if the row exists in the first place and why you just don't issue the UPDATE straightaway?

Could you not do something like this?

BEGIN

    -- update
    UPDATE s
    SET s.col3 = 3
    WHERE s.col1 = 1 AND s.col2 = 2;

    COMMIT;

    IF SQL%ROWCOUNT = 0 THEN
      RETURN 0;
    ELSE
      RETURN 1;
    END IF;

END foo;

answered Jul 27, 2012 at 8:33

OraNob

6943 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Stan Over a year ago

thanks. I updated my question with a more complicated case. That's my original goal. Is there a clean way to do that?

wildplasser Over a year ago

This answer makes more sense than the question. The test is useless and only duplicates the where clause.

score 3 · Accepted Answer · 2012-07-27 09:15:51Z

3

You are not doing anything with the value stored ret.

There is no IF around the UPDATE statement that checks if ret is 1 or null (the other alternative). And because there is no IF the rest of the procedure is always executed.

Something like this is needed:

SELECT sum(case when col1 = 1 then 1 else 0 end) as col1_count, 
       sum(case when col2 = 2 then 1 else 0 end) as col2_count
    into ret1, ret2
FROM s 
WHERE s.col1 = 1
   or s.col2 = 2;

if ret1 > 0 and ret2 > 0 then
   update ...;
   commit;
   ret := 0;
elsif  (ret1 > 0 and ret2 = 0) then 
   ret := 1;
elsif (ret1 = 0 and ret2 > 0) then 
   ret := 2;
end if;

return ret;

A much more efficient approach is to simply do the update and check if any rows where modified. Running the select before doing the update simply doubles the work if the row exists. Running only the update when the row does not exist is the same work as doing the select.

edited Jul 27, 2012 at 9:15

answered Jul 27, 2012 at 8:30

user330315

2 Comments

Stan Over a year ago

thanks. I updated my question with a more complicated case. That's my original goal. Is there a clean way to do that?

user330315 Over a year ago

@Stan: changing the scope of a question in "mid-flight" is not really nice. You should have added that right from the start. See my edit for possible solution.

OraNob · Accepted Answer · 2012-07-27 10:13:27Z

What if I want be able to distinguish if it's s.col1 = 1 not exist or s.col2 = 2 not exist. And have 2 different return codes for them. How should I do in this case?

I've probably over-complicated it with a BULK COLLECT - especially if the update is for only one row at any one time - but you can of course modify the code accordingly - the principle remains the same. The code aims to return 1,2 or 3 depending on which UPDATE condition is met regarding col1 and col2.

DECLARE

    TYPE test_rec is record ( .... );

    TYPE result_tab IS TABLE OF test_rt%ROWTYPE;

    lt_results result_tab;

    lv_ret NUMBER(1) := 0;

BEGIN

    SELECT x.* FROM (
    SELECT s.*, 1 as ret
    FROM s
    WHERE s.col1 = 1 and s.col2 != 2
    UNION ALL
    (SELECT s.*, 2
    FROM s
    WHERE s.col1 =! 1 and s.col2 = 2)
    UNION ALL
    (SELECT s.*, 3
    FROM s
    WHERE s.col1 = 1 and s.col2 = 2))
    BULK COLLECT INTO lt_results;

    FOR i in lt_results.first .. lt_results.last LOOP
        <<DO YOUR UPDATE>>
        lv_ret := lt_results(i).ret;
    END LOOP;

    COMMIT;

    RETURN lv_ret;

END;

Collectives™ on Stack Overflow

PL/SQL how to check if exists and return error code

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related