动态PL / SQL查询，如何忽略空参数？ [英] Dynamic PL/SQL query, how to ignore null parameters?

问题描述

我有一个具有多个参数的PL / SQL过程。当webapp调用该过程时，如果没有使用某个参数，那么它将被传递为空，即

  procedure test（param1在varchar2中，varchar2中的param2，varchar2中的param3，cursor out sys_refcursor）
 ... 
结束过程测试; 
  

我想做一个SQL查询，其中我将WHERE子句中的给定参数包含在参数中不为空有没有办法以优雅的方式实现这一点，除了在字符串中构建SQL查询，然后打开该字符串的游标，像这样

  vSQL：='SELECT * from TABLE WHERE something = something_else'; 
如果param1 IS NOT NULL 
 vSQL：= vSQL || 'AND some_param ='||参数1; 
  

解决方案

我已经回答了一个类似的问题 dba.stackexchange.com

I have a PL/SQL procedure with multiple parameters. When a webapp calls the procedure, if it's not using a certain parameter, it passes is as null, ie

procedure test (param1 in varchar2, param2 in varchar2, param3 in varchar2, cursor out sys_refcursor)
...
end procedure test;

I want to make an SQL query where I include the given parameters in the WHERE clause only if the parameter is not null. Is there a way to achieve this in an elegant way, other than building the SQL query in a string and then opening the cursor for that string, like this

vSQL := 'SELECT * from TABLE WHERE something = something_else';
if param1 IS NOT NULL
    vSQL := vSQL || 'AND some_param = ' || param1;

解决方案

I have answered a similar question on dba.stackexchange.com

这篇关于动态PL / SQL查询，如何忽略空参数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！