oracle 动态PL/SQL查询，如何忽略空参数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4932276/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Dynamic PL/SQL query, how to ignore null parameters?
提问by eagerMoose
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
我有一个带有多个参数的 PL/SQL 过程。当一个webapp调用该过程时，如果它没有使用某个参数，它传递的是null，即
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
我想进行 SQL 查询，其中仅当参数不为空时，才在 WHERE 子句中包含给定的参数。除了在字符串中构建 SQL 查询然后打开该字符串的游标之外，有没有办法以优雅的方式实现这一点，就像这样
vSQL := 'SELECT * from TABLE WHERE something = something_else';
if param1 IS NOT NULL
vSQL := vSQL || 'AND some_param = ' || param1;
采纳答案by René Nyffenegger
I have answered a similar question on dba.stackexchange.com
我在 dba.stackexchange.com 上回答了一个类似的问题
回答by RichardTheKiwi
You can code it all into a single select
您可以将其全部编码为一个选择
SELECT * from TABLE WHERE something = something_else
AND ((param1 IS NOT NULL AND some_param = param1) OR 1)
回答by tbone
Create a test table:
创建一个测试表：
create table testtab
(
name_first varchar2(50),
name_last varchar2(50),
name_middle varchar2(50)
);
insert into testtab values ('Joe', 'Jones', 'A');
insert into testtab values ('Joe', 'Smith', 'A');
insert into testtab values ('Steve', 'Jones', 'B');
insert into testtab values ('Axl', 'Rose', 'C');
insert into testtab values ('Phil', 'McCracken', 'D');
commit;
Create your procedure:
创建您的程序：
CREATE OR REPLACE procedure ECDATA.get_testtab_rows
(i_name_first in varchar2 default null,
i_name_last in varchar2 default null,
i_name_middle in varchar2 default null,
o_cursor out sys_refcursor
) as
v_result_cur sys_refcursor;
begin
open v_result_cur for
select * from testtab
where name_first like nvl(i_name_first, '%')
and name_last like nvl(i_name_last, '%')
and name_middle like nvl(i_name_middle, '%')
;
o_cursor := v_result_cur;
end;
/
Then call it like this:
然后像这样调用它：
declare
v_cur sys_refcursor;
testtab_rec testtab%rowtype;
begin
get_testtab_rows(i_name_last=>'Jones', o_cursor=>v_cur);
loop
fetch v_cur into testtab_rec;
exit when v_cur%notfound;
dbms_output.put_line(testtab_rec.name_first || ' ' || testtab_rec.name_middle || ' ' || testtab_rec.name_last);
end loop;
exception
when others then raise;
end;
