正在根据0检查Oracle SQL DECODE函数结果

Question

我正在查看一些旧的PL/SQL代码，我有十几个DECODE函数，它们是这样写的：

DECODE(value_1, value_2, 1, 0) = 0

现在，我知道这些DECODE在value_1和value_2之间进行比较，并且根据比较结果返回true或false。但是，出于对编码的热爱，有人能告诉我=0部分背后的逻辑是什么吗？为什么有必要对照0检查返回值？

Answer 1

• 如果是value_1 = value_2，则decode返回1
• 否则返回0

因此，整个表达式检查value_1是否与value_2不同，然后-我想-做一些的事情。

如果它是PL/SQL的一部分，您可以将其重写为

if value_1 <> value_2 then...

---

一些例子：

SQL> declare
2    value_1 number := 5;
3    value_2 number := 1;
4  begin
5    decode(value_1, value_2, 1, 0) = 0
6  end;
7  /
decode(value_1, value_2, 1, 0) = 0
*
ERROR at line 5:
ORA-06550: line 5, column 34:
PLS-00103: Encountered the symbol "=" when expecting one of the following:
:= . ( % ;

SQL> declare
2    value_1 number := 5;
3    value_2 number := 1;
4  begin
5    decode(value_1, value_2, 1, 0) = 0;
6  end;
7  /
decode(value_1, value_2, 1, 0) = 0;
*
ERROR at line 5:
ORA-06550: line 5, column 34:
PLS-00103: Encountered the symbol "=" when expecting one of the following:
:= . ( % ;
The symbol ":= was inserted before "=" to continue.

SQL> declare
2    value_1 number := 5;
3    value_2 number := 1;
4  begin
5    if decode(value_1, value_2, 1, 0) = 0 then
6       null;
7    end if;
8  end;
9  /
if decode(value_1, value_2, 1, 0) = 0 then
*
ERROR at line 5:
ORA-06550: line 5, column 6:
PLS-00204: function or pseudo-column 'DECODE' may be used inside a SQL statement only
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored

SQL> declare
2    value_1 number := 5;
3    value_2 number := 1;
4  begin
5    case when decode(value_1, value_2, 1, 0) = 0 then
6         null;
7    end case;
8  end;
9  /
case when decode(value_1, value_2, 1, 0) = 0 then
*
ERROR at line 5:
ORA-06550: line 5, column 13:
PLS-00204: function or pseudo-column 'DECODE' may be used inside a SQL statement only
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored

SQL> declare
2    value_1 number := 5;
3    value_2 number := 1;
4    l_decode_result number;
5  begin
6    select decode(value_1, value_2, 1, 0)
7      into l_decode_result
8      from dual;
9  end;
10  /
PL/SQL procedure successfully completed.
SQL>