Alternative for IF statement in plsql select Into

Question

I have a select statement like below using IF condition

If (v_block = 'apple')
then

select count(*) into v_apple_id
from table_fruits
where fruit_id = v_fruit_id;

elsif (v_block = 'mango')
then
select count(*) into v_mango_id
from table_fruits
where fruit_id = v_fruit_id;

end if;

How to Optimise this query by avoiding the IF statement.

The v_fruit_id will depend on the value in v_block. For eg. If v_block = 'Mango' then the v_fruit_id will be 1234 and if v_block = 'apple' then v_fruit_id will be 1244. and the number here like 1234 or 1244 are not the same always so the variable v_fruit_id should be given instead of hard coding the numbers

I dont want to use the IF Else statement here because there are more values for the v_block so to avoid big query with IF else i want to find an alternativ simple select statement for it.

I also need the into varibale to be according to the fruit. Incase of v_block = mango then it should be v_mango_id and in case of apple then into v_apple_id

score 1 · Answer 1 · answered Jun 23 '21 at 12:04

1

Seems you need a CASE ..WHEN expression such as

SELECT COUNT(*)
  INTO v_apple_id
  FROM table_fruits
 WHERE fruit_id = CASE WHEN v_block = 'Mango' THEN 1234
                       WHEN v_block = 'Apple' THEN 1244 END

while getting rid of v_fruit_id parameter

answered Jun 23 '21 at 12:04

Barbaros Özhan

59,113
10
31
55

Thanks, But I also need the into varibale to be according to the fruit. Incase of v_block = mango then it should be v_mango_id and in case of apple then into v_apple_id – Sangathamilan Ravichandran Jun 23 '21 at 12:07

score 1 · Accepted Answer · answered Jun 23 '21 at 12:41

In my opinion, you're trying to do it wrong.

You can't and shouldn't have separate variables for all fruits. OK, now you have only two - v_apple_id and v_mango_id. What when bananas come? Will you create yet another v_banana_id and modify the whole code of your procedure? Lucky you, you'll do it in a matter of an hour or two. And tomorrow, you'll get v_peach_id and v_pear_id. What then?

As I said - completely wrong approach.

I don't know what v_block represents (you never posted much information about the whole problem), so here's code as it should look like (again, in my opinion):

CREATE OR REPLACE PROCEDURE p_fruit (
   par_fruit_id  IN table_fruits.fruit_id%TYPE)
IS
   v_count  NUMBER;
BEGIN
   SELECT COUNT (*)
     INTO v_count
     FROM table_fruits
    WHERE fruit_id = par_fruit_id;
END;
/

In other words: create a procedure which will accept fruit ID as a parameter and use it later in its code. That's general approach. It might need to be changed, but - that depends on what you're really trying to do. Maybe it should just be converted to a function which returns number of e.g. apples ... can't tell at the moment.

Alternative for IF statement in plsql select Into

2 Answers2