sqloracle-databaseif-statementplsqlselect-into

Alternative for IF statement in plsql select Into


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


Solution

  • 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.