Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

adding condition to query if parameter is not null

WestDraytonJan 26 2010 — edited Jan 26 2010
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;  
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 23 2010
Added on Jan 26 2010
5 comments
3,430 views