I have procedure which have such part in query:
and (i_id is null or ID <> i_id)
The porpouse of that is to use parameter "i_id" in query as an additional condition only if parameter i_id was given i.e. it wasnt null.
Is this good practice to write so, or it slows down indexes or something?
Because one can also write code with if-else like this:
if i_id is null then --don't use this parameter in query
select count(*)
into v_count
from V_PRD_TYPE_LANG
where BRANCH_ID = PKG_SESSION.BRANCH_ID --nimi peab olema unikaalne filiaali sees.
and CLOSED_TIME is null
--
and NAME = i_prd_type_name;
else--use i_id in query
select count(*)
into v_count
from V_PRD_TYPE_LANG
where BRANCH_ID = PKG_SESSION.BRANCH_ID --nimi peab olema unikaalne filiaali sees.
and CLOSED_TIME is null
and ( ID <> i_id)--kui i_id on antud, siis välista see kirje
and NAME = i_prd_type_name;
end if;
So, how is wise to do?
/**
* Leiab kas tootegrupi nimetus on unikaalne.
* i_id - Tootegrupi ID millele soovitakse panna nime [i_prd_type_name]. Inserdi puhul NULL.
*/
function IS_PRD_TYPE_NAME_UNIQUE(
i_id in PRD_TYPE.ID%type,
i_prd_type_name in LANGUAGE.TRANSLATION%type
)
return boolean
is
v_count NUMBER;
begin
--
select count(*)
into v_count
from V_PRD_TYPE_LANG
where BRANCH_ID = PKG_SESSION.BRANCH_ID --nimi peab olema unikaalne filiaali sees.
and CLOSED_TIME is null
and (i_id is null or ID <> i_id)--kui i_id on antud, siis välista see kirje
and NAME = i_prd_type_name;
if v_count > 0 then
return false;
end if;
return true;
end IS_PRD_TYPE_NAME_UNIQUE;