要在比较中使用空参数时，该如何处理PL/SQL存储过程中的空参数? [英] How should I deal with null parameters in a PL/SQL stored procedure when I want to use them in comparisons?

问题描述

我有一个带有参数name的存储过程，我想在where子句中使用它来匹配列的值，即类似

I have a stored procedure with a parameter name which I want to use in a where clause to match the value of a column i.e. something like

where col1 = name

当然，由于null的工作方式，现在无法将null与null匹配.我需要做

Now of course this fails to match null to null because of the way null works. Do I need to do

where ((name is null and col1 is null) or col1 = name)

在这种情况下还是有一种更简洁的方法?

in situations like this or is there a more concise way of doing it?

推荐答案

您可以使用

You can use decode function in the following fashion:

where decode(col1, name, 0) is not null

从SQL参考中引用:

在DECODE函数中，Oracle认为 两个null相等.

In a DECODE function, Oracle considers two nulls to be equivalent.

这篇关于要在比较中使用空参数时，该如何处理PL/SQL存储过程中的空参数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！