0

Instead of doing:

select count(*) into l_count from mergetest  where a = 1;

case l_count
  when 0 then
    insert into mergetest  (a,b) values (1,1);
  when 1 then
    update  mergetest  set b = b + 1 where a = 1;
  else
    NULL;
  end case;

I would like to get rid of the local variable l_count and instead do:

case select count(*) from mergetest where a = 1;
...

Is this possible?

Roland
  • 7,525
  • 13
  • 61
  • 124

3 Answers3

1

No, it's not possible. It's not related to PL/SQL's CASE control structure but rather to PL/SQL in general. You can't use it in an IF statement either.

So this code wouldn't compile either:

IF (SELECT COUNT(*) FROM MY_TABLE) > 0 THEN ...

Note that you can use subqueries if the CASE statement is an expression within a SELECT (or similar SQL) statement:

SEELCT
  p.name,
  CASE WHEN P.TYPE_ID = 1 THEN
      (SELECT COUNT(*) FROM child c WHERE c.parent_id = p.id)
    ELSE 0
  END cnt
FROM parent p;

But of course, within an expression you cannot use statements such as INSERT or UPDATE.

Codo
  • 75,595
  • 17
  • 168
  • 206
1

It's possible to get rid of local variable by using merge, not case:

merge into mergetest
  using (select 1 a from dual) ins
  on (mergetest.a = ins.a)
  when matched then 
    update set mergetest.b = mergetest.b + 1
      where (select count(1) from mergetest where mergetest.a = ins.a)  = 1
  when not matched then insert (mergetest.a, mergetest.b)
    values (ins.a, 1)

Line: 

      where (select count(1) from mergetest where mergetest.a = ins.a)  = 1

prevents updates if a already exists in table more than one time.

Ponder Stibbons
  • 14,723
  • 2
  • 21
  • 24
  • True, but the code becomes harder to understand. You answered my other question btw: http://stackoverflow.com/questions/28382332/merge-statement-but-update-only-if-exactly-one-row-matches – Roland Feb 09 '15 at 08:31
0

It seems like it is not possible using PL/SQL

from the doc

The value of the CASE operand and WHEN operands in a simple CASE statement can be any PL/SQL type other than BLOB, BFILE, an object type, a PL/SQL record, an index-by table, a varray, or a nested table.

http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/case_statement.htm

However, you can use scalar subquery

select case when dummy = 'X' then (select count(*) from all_users)
else (select count(*) from dual) 
end cnt from dual

Which would probably resolve your issue.

Jean-François Savard
  • 20,626
  • 7
  • 49
  • 76