Oracle 当输入参数允许为空时

场景：

有一个存储过程p_test 带有多个输入参数code、name、number

p_test(code IN VARCHAR2,nameIN VARCHAR2,number IN VARCHAR2,resultList OUT sys_refcursor)

但是业务上要求这三个参数都允许为空，也就是都不是必填项

 

原本sql语句是写成这样的：

select * from temptbl a where a.code=code and a.name=name and a.number=number

 

设想：

一开始想到的是分别对每个参数进行空值判断，然后拼接sql，不为空的参数就写到where条件里去。但是对于简单的sql好说，对于复杂的sql不太可行，这样做代码会很乱。

 

结论：

 

百度后找到一个很有用的函数叫COALESCE(表达式1,表达式2,.....,表达式n)

它的作用是返回多个表达式中第一个非null的值，从左到右找寻，找到后即停止寻找并返回该非null值。

例子：coalesce(null,2,3)  返回值为：2  

当然参数中的2和3也可写成表示式，如3+3、4+5之类的。或者其他数据类型。

回到刚刚的问题，此问题可以结合coalesce函数和like完成(或许还有更好的办法不过我现在只想到这个)

sql语句可以改为：

select * from temptbl a where a.code like coalesce(code,'%%') and a.name like coalesce(name,'%%') and a.number like coalesce(number,'%%')

那么当输入参数code为null时条件就变成a.code like '%%'，即不限制a.code的值(不过a.code为null的值会过滤掉，如果实际业务要展示null值，需另行判断)；当输入参数code不为null时条件就变成a.code like code，由于未加通配符，此查询等同于a.code=code。其他参数依次类推。