动态处理选择条件 [英] Handling select condition dynamically

问题描述

我有一个类似

Procedure P_GENDER_REP
 (
  l_province  IN  varchar,
  l_district  IN  varchar,
  l_village   IN  varchar,
  l_tribe     IN  varchar,
  l_clan      IN  varchar,
  l_refcursor out sys_refcursor
 )

我在这样的过程中有一个选择查询

And i have a select Query inside the procedure like this

select a.province,a.DISTRICT,a.VILLAGE,a.TRIBE,a.CLAN,b.cdr_data 
from itaukei_data_store b,itaukei_data_store_key a 
where a.reference_no = b.reference_no and a.record_no = b.record_no and a.province = l_province 
  and a.district = l_district and a.village = l_village and a.tribe= l_tribe 
    and a.clan = l_clan      
order by a.DISTRICT,a.VILLAGE,a.TRIBE,a.CLAN;

现在我需要一个选择查询来处理给定的输入.例如，如果仅给出l_province，我需要动态忽略所有其他条件.请帮我.

Now i need a select query handling the inputs given.For example if only l_province is given,i need to ignore all the other where conditions dynamically. Pls help me out.

推荐答案

通常，您可以通过在每个条件下添加OR l_variable IS NULL来做到这一点:

Usually you do this by adding OR l_variable IS NULL to every conditions:

select a.province,a.DISTRICT,a.VILLAGE,a.TRIBE,a.CLAN,b.cdr_data 
from itaukei_data_store b INNER JOIN itaukei_data_store_key a 
ON a.reference_no = b.reference_no 
  and a.record_no = b.record_no 
  and a.province = l_province 
WHERE l_district IS NULL OR a.district = l_district
  and l_village IS NULL OR a.village = l_village
  and l_tribe IS NULL OR a.tribe= l_tribe
  and l_clan IS NULL OR a.clan = l_clan
order by a.DISTRICT,a.VILLAGE,a.TRIBE,a.CLAN;

但是，这有时可能会影响性能.您最好对每种组合使用IF.另外，您可以尝试使用支持短路的CASE语句，如下所示:

However, this may sometimes affect performance. You will be better off to use IF for each of the combinations. Alternatively, you can try to use a CASE statement, that supports the short-circuiting, like this:

select a.province,a.DISTRICT,a.VILLAGE,a.TRIBE,a.CLAN,b.cdr_data 
from itaukei_data_store b INNER JOIN itaukei_data_store_key a 
ON a.reference_no = b.reference_no 
  and a.record_no = b.record_no 
  and a.province = l_province 
WHERE a.district = CASE WHEN l_district IS NULL THEN a.district ELSE l_district END
  and a.village = CASE WHEN l_village IS NULL THEN a.village ELSE l_village END
  and a.tribe = CASE WHEN l_tribe IS NULL THEN a.tribe ELSE l_tribe END
  and a.clan = CASE WHEN l_clan IS NULL THEN a.clan ELSE l_clan END
order by a.DISTRICT,a.VILLAGE,a.TRIBE,a.CLAN;

您还有第三个选择.您可以使用COALESCE代替CASE，如下所示:

You have a third option. You can use COALESCE in place of a CASE, like this:

select a.province,a.DISTRICT,a.VILLAGE,a.TRIBE,a.CLAN,b.cdr_data 
from itaukei_data_store b INNER JOIN itaukei_data_store_key a 
ON a.reference_no = b.reference_no 
  and a.record_no = b.record_no 
  and a.province = l_province 
WHERE a.district = COALESCE(l_district, a.district)
  and a.village = COALESCE(l_village, a.village)
  and a.tribe = COALESCE(l_tribe, a.tribe)
  and a.clan = COALESCE(l_clan, a.clan)
order by a.DISTRICT,a.VILLAGE,a.TRIBE,a.CLAN;

尝试每一个，看看哪个效果更好.顺便说一句，我也将您的FROM子句从逗号分隔的表列表更改为标准的INNER JOIN.请养成使用JOIN的习惯，因为它现在被认为是最佳选择".

Try each one and see which one performs better. BTW, I have also changed your FROM clause from comma delimited lists of table to a standard INNER JOIN. Please make a habit to use JOINs, as it is now considered a "best parctice"

这篇关于动态处理选择条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！