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