OceanBase 流程控制函數(shù)

CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END
OR
CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END

obclient> select CASE 'b' when 'a' then 1 when 'b' then 2 END;
+----------------------------------------------+
| CASE 'b' when 'a' then 1 when 'b' then 2 END |
+----------------------------------------------+
|                                            2 |
+----------------------------------------------+
1 row in set (0.01 sec)

obclient> select CASE concat('a','b') when concat('ab','') then 'a' when 'b' then 'b' end;
+--------------------------------------------------------------------------+
| CASE concat('a','b') when concat('ab','') then 'a' when 'b' then 'b' end |
+--------------------------------------------------------------------------+
| a                                                                        |
+--------------------------------------------------------------------------+
1 row in set (0.01 sec)

obclient> select case when 1>0 then 'true' else 'false' end;
+--------------------------------------------+
| case when 1>0 then 'true' else 'false' end |
+--------------------------------------------+
| true                                       |
+--------------------------------------------+
1 row in set (0.00 sec)

obclient> select if(5>6, 'T','F'), if (5>6, 1, 0), if(null, 'True', 'False'), if(0, 'True', 'False')\G
*************************** 1. row ***************************
         if(5>6, 'T','F'): F
           if (5>6, 1, 0): 0
if(null, 'True', 'False'): False
   if(0, 'True', 'False'): False
1 row in set (0.01 sec)

obclient> SELECT IFNULL('abc', null), IFNULL(NULL+1, NULL+2), IFNULL(1/0, 0/1);
+---------------------+------------------------+------------------+
| IFNULL('abc', null) | IFNULL(NULL+1, NULL+2) | IFNULL(1/0, 0/1) |
+---------------------+------------------------+------------------+
| abc                 |                   NULL |           0.0000 |
+---------------------+------------------------+------------------+
1 row in set (0.01 sec)

obclient> SELECT NULLIF('ABC', 123), NULLIF('123',123), NULLIF(NULL, 'abc');
+--------------------+-------------------+---------------------+
| NULLIF('ABC', 123) | NULLIF('123',123) | NULLIF(NULL, 'abc') |
+--------------------+-------------------+---------------------+
| ABC                | NULL              | NULL                |
+--------------------+-------------------+---------------------+
1 row in set, 1 warning (0.01 sec)

IF 條件=值1
THEN RETURN(返回值1)
ELSIF 條件=值2
THEN RETURN(返回值2)
......
ELSIF 條件=值n
THEN RETURN(返回值n)
ELSE RETURN(缺省值)
END IF