SELECT 1 FROM DUAL

用PLSQL执行数据查询的时候，FROM子句是必须的，当不想查具体的表时，可以使用虚拟表dual。

SELECT 1+1 FROM dual;
SELECT 1 FROM dual WHERE NULL IS NULL;
SELECT 'SUCCESS' AS RESULT FROM dual WHERE 50 < 60;
SELECT SYSDATE FROM dual;

CASE语句

CASE表达式有两种形式：

CASE AAC004  
WHEN '1' THEN 'MALE'  
WHEN '2' THEN 'FEMALE'  
ELSE NULL END;

--CASE搜索函数  
CASE
WHEN AAC004 = '1' THEN 'MALE'  
WHEN AAC004 = '2' THEN 'FEMALE'  
ELSE NULL END;

• SELECT CASE WHEN 用法

  SELECT  AAC012, COUNT (CASE AAC004
                         WHEN '1' THEN 1
                         ELSE NULL
                         END) AS MALE,
                  COUNT (CASE AAC004
                         WHEN '2' THEN 1
                         ELSE NULL
                         END) AS FEMALE
      FROM AC01 WHERE AAC002 LIKE '%19940911%' GROUP BY AAC012;
  
  SELECT (CASE 
          WHEN 表达式1 THEN 'RESULT1'
          WHEN 表达式2 THEN 'RESULT2'
          ELSE 'RESULT3'
          END) AS RESULT FROM DUAL;
  

• WHERE CASE WHEN 用法

  SELECT T2.*, T1.*
     FROM T1, T2
    WHERE (CASE 
           WHEN T2.COMPARE_TYPE = 'A' AND T1.SOME_TYPE LIKE 'NOTHING%' THEN 1
           WHEN T2.COMPARE_TYPE != 'A' AND T1.SOME_TYPE NOT LIKE 'NOTHING%' THEN 1
             ELSE 0
             END) = 1;
  

• GROUP BY CASE WHEN 用法

  SELECT CASE AAC004
      WHEN '1' THEN 'MALE'
      WHEN '2' THEN 'FEMALE'
      ELSE NULL
      END SEX,--别名
      CUONT(*)
      FROM AC01 WHERE AAC002 LIKE '%19940911%'
      GROUP BY CASE AAC004
          WHEN '1' THEN 'MALE'
          WHEN '2' THEN 'FEMALE'
          ELSE NULL END;
  

DECODE用法

• 等价于IF-THEN-ELSE

  DECODE(VALUE, IF1, THEN1, IF2, THEN2, IF3, THEN3, . . . ELSE );
  

• 举例

  --SALARY>8000,加薪15%；SALARY<8000,加薪20%;SALARY=8000,加薪15%
  SELECT DECODE(SIGN(SALARY - 8000),1,SALARY*1.15,-1,SALARY*1.2,SALARY*1.15) FROM EMPLOYEE；
  
  --统计AC01表中19940911出生的男女数
  SELECT COUNT(DECODE(AAC004,'1',1,NULL)) AS MALE,COUNT(DECODE(AAC004,'2',1,NULL)) AS FEMALE FROM AC01 WHERE AAC002 LIKE '%19940911%'；
  
  --另外一种实现方法
  SELECT DECODE(AAC004,'1','MALE','2','FEMALE',NULL) SEX,COUNT(*) FROM AC01 WHERE AAC002 LIKE '%19940911%' GROUP BY DECODE(AAC004,'1','MALE','2','FEMALE',NULL)；
  

参考文章

• Oracle CASE WHEN 用法介绍