oracle SQL - 比解码更简单的函数

SQL - A simpler function than decode

提问by Hal

I am working with a pl/sql procedure. I have an initialized variable myvar and I want to check its value : if it does not contain 'Z', I want it to contain 'P'.

我正在使用 pl/sql 过程。我有一个初始化变量 myvar，我想检查它的值：如果它不包含“Z”，我希望它包含“P”。

I am currently doing it this way:

我目前正在这样做：

myvar := decode(myvar,'Z','Z','P');

I was just wondering if there was a simplier way to do this. I mean, decode is already simple, but I feel it's weird to specify the content of the variable while it is already in it !

我只是想知道是否有更简单的方法来做到这一点。我的意思是，decode 已经很简单了，但是我觉得在变量已经存在的情况下指定它的内容很奇怪！

If such a function would exist, it would look like this:

如果存在这样的函数，它将如下所示：

Function myfunction(a In Varchar2, b In Varchar2, c In Varchar2) 
Return Varchar2 
Is               
Begin
      if a <> b
      then
           return c;
      end if;
      return a;
End myfunction; 

Any help would be appreciated !

任何帮助，将不胜感激 ！

回答by Tony Andrews

There is no built-in function that does exactly what you want.

没有内置函数可以完全满足您的要求。

You could use CASE rather than DECODE:

您可以使用 CASE 而不是 DECODE：

CASE myvar WHEN 'Z' THEN 'Z' ELSE 'P' END

It doesn't make it any shorter though!

但这并没有让它变得更短！

回答by Jokke Heikkil?

Put that function of yours to the program's declaration section and use it!

把你的那个函数放到程序的声明部分并使用它！

回答by FerranB

I agree the best option is to use CASEexpression:

我同意最好的选择是使用CASE表达式：

CASE myvar WHEN 'Z' THEN 'Z' ELSE 'P' END

Another approach if you feel happy with DECODEis to run this query:

如果您对DECODE感到满意，另一种方法是运行以下查询：

SELECT decode(myvar,'Z','Z','P') 
  INTO myvar
  FROM DUAL;

回答by TrojanName

To answer your original question of whether there is a simpler way, there is also this:

要回答您的原始问题是否有更简单的方法，还有这个：

if myvar <> 'Z' then 
  myvar := 'P'
end if;