《卸甲筆記》-單行函式對比之三

宋躍傑發表於2016-06-22

41查詢出所有在每年2月份僱傭的僱員資訊
Oracle

SQL> select * from emp where TO_CHAR(hiredate,`MM`)=`02`;

     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7499 ALLEN        SALESMAN         7698 20-FEB-81
      1600      300          30

      7521 WARD         SALESMAN         7698 22-FEB-81
      1250      500          30

PPAS

scott=# select * from emp where TO_CHAR(hiredate,`MM`)=`02`;
 empno | ename |   job    | mgr  |      hiredate      |   sal   |  comm  | deptno 
-------+-------+----------+------+--------------------+---------+--------+--------
  7499 | ALLEN | SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600.00 | 300.00 |     30
  7521 | WARD  | SALESMAN | 7698 | 22-FEB-81 00:00:00 | 1250.00 | 500.00 |     30
(2 rows)

42直接判斷數字2
Oracle

SQL> select * from emp where TO_CHAR(hiredate,`MM`)= 2;

     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7499 ALLEN        SALESMAN         7698 20-FEB-81
      1600      300          30

      7521 WARD         SALESMAN         7698 22-FEB-81
      1250      500          30

PPAS

Oracle遷移PPAS(PostgreSQL)改造點
scott=# select * from emp where TO_CHAR(hiredate,`MM`)= `02`;
 empno | ename |   job    | mgr  |      hiredate      |   sal   |  comm  | deptno 
-------+-------+----------+------+--------------------+---------+--------+--------
  7499 | ALLEN | SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600.00 | 300.00 |     30
  7521 | WARD  | SALESMAN | 7698 | 22-FEB-81 00:00:00 | 1250.00 | 500.00 |     30
(2 rows)

43將每個僱員的僱傭日期進行格式化顯示，要求所有的僱傭日期可以按照“年-月-日”的形式顯示，也可以將僱傭的年、月、日拆開分別顯示
Oracle

SQL> select empno,ename,job,hiredate,
  2  TO_CHAR(hiredate,`YYYY-MM-DD`) 格式化僱傭日期,TO_CHAR(hiredate,`YYYY`) 年,
  3  TO_CHAR(hiredate,`MM`) 月,TO_CHAR(hiredate,`DD`) 日
  4  from emp;

     EMPNO ENAME        JOB           HIREDATE
---------- -------------------- ------------------ ------------
格式化僱傭日期         年       月   日
-------------------- -------- ---- ----
      7369 SMITH        CLERK           17-DEC-80
1980-12-17         1980     12   17

      7499 ALLEN        SALESMAN       20-FEB-81
1981-02-20         1981     02   20

      7521 WARD         SALESMAN       22-FEB-81
1981-02-22         1981     02   22


     EMPNO ENAME        JOB           HIREDATE
---------- -------------------- ------------------ ------------
格式化僱傭日期         年       月   日
-------------------- -------- ---- ----
      7566 JONES        MANAGER        02-APR-81
1981-04-02         1981     04   02

      7654 MARTIN        SALESMAN       28-SEP-81
1981-09-28         1981     09   28

      7698 BLAKE        MANAGER        01-MAY-81
1981-05-01         1981     05   01


     EMPNO ENAME        JOB           HIREDATE
---------- -------------------- ------------------ ------------
格式化僱傭日期         年       月   日
-------------------- -------- ---- ----
      7782 CLARK        MANAGER        09-JUN-81
1981-06-09         1981     06   09

      7788 SCOTT        ANALYST        19-APR-87
1987-04-19         1987     04   19

      7839 KING         PRESIDENT       17-NOV-81
1981-11-17         1981     11   17


     EMPNO ENAME        JOB           HIREDATE
---------- -------------------- ------------------ ------------
格式化僱傭日期         年       月   日
-------------------- -------- ---- ----
      7844 TURNER        SALESMAN       08-SEP-81
1981-09-08         1981     09   08

      7876 ADAMS        CLERK           23-MAY-87
1987-05-23         1987     05   23

      7900 JAMES        CLERK           03-DEC-81
1981-12-03         1981     12   03


     EMPNO ENAME        JOB           HIREDATE
---------- -------------------- ------------------ ------------
格式化僱傭日期         年       月   日
-------------------- -------- ---- ----
      7902 FORD         ANALYST        03-DEC-81
1981-12-03         1981     12   03

      7934 MILLER        CLERK           23-JAN-82
1982-01-23         1982     01   23


14 rows selected.

PPAS

scott=# select empno,ename,job,hiredate,
scott-# TO_CHAR(hiredate,`YYYY-MM-DD`) 格式化僱傭日期,TO_CHAR(hiredate,`YYYY`) 年, 
scott-# TO_CHAR(hiredate,`MM`) 月,TO_CHAR(hiredate,`DD`) 日
scott-# from emp;
 empno | ename  |    job    |      hiredate      | 格式化僱傭日期 |  年  | 月 | 日 
-------+--------+-----------+--------------------+----------------+------+----+----
  7369 | SMITH  | CLERK     | 17-DEC-80 00:00:00 | 1980-12-17     | 1980 | 12 | 17
  7499 | ALLEN  | SALESMAN  | 20-FEB-81 00:00:00 | 1981-02-20     | 1981 | 02 | 20
  7521 | WARD   | SALESMAN  | 22-FEB-81 00:00:00 | 1981-02-22     | 1981 | 02 | 22
  7566 | JONES  | MANAGER   | 02-APR-81 00:00:00 | 1981-04-02     | 1981 | 04 | 02
  7654 | MARTIN | SALESMAN  | 28-SEP-81 00:00:00 | 1981-09-28     | 1981 | 09 | 28
  7698 | BLAKE  | MANAGER   | 01-MAY-81 00:00:00 | 1981-05-01     | 1981 | 05 | 01
  7782 | CLARK  | MANAGER   | 09-JUN-81 00:00:00 | 1981-06-09     | 1981 | 06 | 09
  7788 | SCOTT  | ANALYST   | 19-APR-87 00:00:00 | 1987-04-19     | 1987 | 04 | 19
  7839 | KING   | PRESIDENT | 17-NOV-81 00:00:00 | 1981-11-17     | 1981 | 11 | 17
  7844 | TURNER | SALESMAN  | 08-SEP-81 00:00:00 | 1981-09-08     | 1981 | 09 | 08
  7876 | ADAMS  | CLERK     | 23-MAY-87 00:00:00 | 1987-05-23     | 1987 | 05 | 23
  7900 | JAMES  | CLERK     | 03-DEC-81 00:00:00 | 1981-12-03     | 1981 | 12 | 03
  7902 | FORD   | ANALYST   | 03-DEC-81 00:00:00 | 1981-12-03     | 1981 | 12 | 03
  7934 | MILLER | CLERK     | 23-JAN-82 00:00:00 | 1982-01-23     | 1982 | 01 | 23
(14 rows)

44使用英文的日期合適表示出每個僱員的僱傭日期
Oracle

SQL> select empno,ename,hiredate,TO_CHAR(hiredate,`YEAR-MONTH-DY`)
  2   from emp;

     EMPNO ENAME        HIREDATE
---------- -------------------- ------------
TO_CHAR(HIREDATE,`YEAR-MONTH-DY`)
--------------------------------------------------------------------------------
      7369 SMITH        17-DEC-80
NINETEEN EIGHTY-DECEMBER -WED

      7499 ALLEN        20-FEB-81
NINETEEN EIGHTY-ONE-FEBRUARY -FRI

      7521 WARD         22-FEB-81
NINETEEN EIGHTY-ONE-FEBRUARY -SUN


     EMPNO ENAME        HIREDATE
---------- -------------------- ------------
TO_CHAR(HIREDATE,`YEAR-MONTH-DY`)
--------------------------------------------------------------------------------
      7566 JONES        02-APR-81
NINETEEN EIGHTY-ONE-APRIL    -THU

      7654 MARTIN        28-SEP-81
NINETEEN EIGHTY-ONE-SEPTEMBER-MON

      7698 BLAKE        01-MAY-81
NINETEEN EIGHTY-ONE-MAY      -FRI


     EMPNO ENAME        HIREDATE
---------- -------------------- ------------
TO_CHAR(HIREDATE,`YEAR-MONTH-DY`)
--------------------------------------------------------------------------------
      7782 CLARK        09-JUN-81
NINETEEN EIGHTY-ONE-JUNE     -TUE

      7788 SCOTT        19-APR-87
NINETEEN EIGHTY-SEVEN-APRIL    -SUN

      7839 KING         17-NOV-81
NINETEEN EIGHTY-ONE-NOVEMBER -TUE


     EMPNO ENAME        HIREDATE
---------- -------------------- ------------
TO_CHAR(HIREDATE,`YEAR-MONTH-DY`)
--------------------------------------------------------------------------------
      7844 TURNER        08-SEP-81
NINETEEN EIGHTY-ONE-SEPTEMBER-TUE

      7876 ADAMS        23-MAY-87
NINETEEN EIGHTY-SEVEN-MAY      -SAT

      7900 JAMES        03-DEC-81
NINETEEN EIGHTY-ONE-DECEMBER -THU


     EMPNO ENAME        HIREDATE
---------- -------------------- ------------
TO_CHAR(HIREDATE,`YEAR-MONTH-DY`)
--------------------------------------------------------------------------------
      7902 FORD         03-DEC-81
NINETEEN EIGHTY-ONE-DECEMBER -THU

      7934 MILLER        23-JAN-82
NINETEEN EIGHTY-TWO-JANUARY  -SAT


14 rows selected.

PPAS

scott=# select empno,ename,hiredate,TO_CHAR(hiredate,`YEAR-MONTH-DY`)
scott-# from emp;
 empno | ename  |      hiredate      |               to_char               
-------+--------+--------------------+-------------------------------------
  7369 | SMITH  | 17-DEC-80 00:00:00 | NINETEEN EIGHTY-DECEMBER -WED
  7499 | ALLEN  | 20-FEB-81 00:00:00 | NINETEEN EIGHTY ONE-FEBRUARY -FRI
  7521 | WARD   | 22-FEB-81 00:00:00 | NINETEEN EIGHTY ONE-FEBRUARY -SUN
  7566 | JONES  | 02-APR-81 00:00:00 | NINETEEN EIGHTY ONE-APRIL    -THU
  7654 | MARTIN | 28-SEP-81 00:00:00 | NINETEEN EIGHTY ONE-SEPTEMBER-MON
  7698 | BLAKE  | 01-MAY-81 00:00:00 | NINETEEN EIGHTY ONE-MAY      -FRI
  7782 | CLARK  | 09-JUN-81 00:00:00 | NINETEEN EIGHTY ONE-JUNE     -TUE
  7788 | SCOTT  | 19-APR-87 00:00:00 | NINETEEN EIGHTY SEVEN-APRIL    -SUN
  7839 | KING   | 17-NOV-81 00:00:00 | NINETEEN EIGHTY ONE-NOVEMBER -TUE
  7844 | TURNER | 08-SEP-81 00:00:00 | NINETEEN EIGHTY ONE-SEPTEMBER-TUE
  7876 | ADAMS  | 23-MAY-87 00:00:00 | NINETEEN EIGHTY SEVEN-MAY      -SAT
  7900 | JAMES  | 03-DEC-81 00:00:00 | NINETEEN EIGHTY ONE-DECEMBER -THU
  7902 | FORD   | 03-DEC-81 00:00:00 | NINETEEN EIGHTY ONE-DECEMBER -THU
  7934 | MILLER | 23-JAN-82 00:00:00 | NINETEEN EIGHTY TWO-JANUARY  -SAT
(14 rows)

45格式化數字顯示
Oralce

SQL> select TO_CHAR(987654321.789,`999,999,999,999.99999`) 格式化數字,
  2  TO_CHAR(987654321.789,`000,000,000,000.00000`) 格式化數字
  3  from dual;

格式化數字
--------------------------------------------
格式化數字
--------------------------------------------
     987,654,321.78900
 000,987,654,321.78900

PPAS

scott=# select TO_CHAR(987654321.789,`999,999,999,999.99999`) 格式化數字,
scott-# TO_CHAR(987654321.789,`000,000,000,000.00000`) 格式化數字 
scott-# from dual;
       格式化數字       |       格式化數字       
------------------------+------------------------
      987,654,321.78900 |  000,987,654,321.78900
(1 row)

46格式化貨幣顯示
Oracle

SQL>  select TO_CHAR(987654321.789,`L999,999,999,999.99999`) 顯示貨幣, 
  2  TO_CHAR(987654321.789,`$999,999,999,999.99999`) 顯示美元
  3  from dual;

顯示貨幣
----------------------------------------------------------------
顯示美元
----------------------------------------------
          $987,654,321.78900
     $987,654,321.78900

PPAS

scott=# select TO_CHAR(987654321.789,`L999,999,999,999.99999`) 顯示貨幣,          
scott-# TO_CHAR(987654321.789,`$999,999,999,999.99999`) 顯示美元
scott-# from dual;
        顯示貨幣         |        顯示美元         
-------------------------+-------------------------
 $     987,654,321.78900 | $     987,654,321.78900
(1 row)

47使用TO_DATE()函式
Oracle

SQL> select TO_DATE(`2016-06-21`,`YYYY-MM-DD`)
  2  FROM dual;

TO_DATE(`201
------------
21-JUN-16

PPAS

scott=# select TO_DATE(`2016-06-21`,`YYYY-MM-DD`)
scott-# FROM dual;
      to_date       
--------------------
 21-JUN-16 00:00:00
(1 row)

48使用TO_TIMESTAMP()函式
Oracle

SQL> select TO_TIMESTAMP(`1981-09-27 18:07:10`,`YYYY-MM-DD HH24:MI:SS`) datetime
  2  from dual;

DATETIME
---------------------------------------------------------------------------
27-SEP-81 06.07.10.000000000 PM

PPAS

scott=# select TO_TIMESTAMP(`1981-09-27 18:07:10`,`YYYY-MM-DD HH24:MI:SS`) datetime
scott-# from dual;
         datetime          
---------------------------
 27-SEP-81 18:07:10 +08:00
(1 row)

49使用TO_NUMBER()函式將字串變為數字
Oracle

SQL> select TO_NUMBER(`09`) + TO_NUMBER(`19`) 加法計算,
  2  TO_NUMBER(`09`) * TO_NUMBER(`19`) 乘法計算
  3  from dual;

  加法計算   乘法計算
---------- ----------
    28      171

PPAS

scott=# select TO_NUMBER(`09`) + TO_NUMBER(`19`) 加法計算,
scott-# TO_NUMBER(`09`) * TO_NUMBER(`19`) 乘法計算
scott-# from dual;
 加法計算 | 乘法計算 
----------+----------
       28 |      171
(1 row)

50不利用TO_NUMBER()函式，字串也可以自動變為數字
Oracle

SQL>  select `09` + `19` 加法計算,`09` * `19` 乘法計算
  2   from dual;

  加法計算   乘法計算
---------- ----------
    28      171

PPAS

scott=# select `09` + `19` 加法計算,`09` * `19` 乘法計算
scott-# from dual;
ERROR:  operator is not unique: unknown + unknown
LINE 1: select `09` + `19` 加法計算,`09` * `19` 乘法計算
                    ^
HINT:  Could not choose a best candidate operator. You might need to add explicit type casts.

Oracle遷移PPAS(PostgreSQL)改造點
scott=# select 09 + 19 加法計算,09 * 19 乘法計算                    
scott-# from dual;
 加法計算 | 乘法計算 
----------+----------
       28 |      171
(1 row)
scott=# select 09 + 19 加法計算,09 * 19 乘法計算;           
 加法計算 | 乘法計算 
----------+----------
       28 |      171
(1 row)

51查詢出每個僱員的編號、姓名、職位、僱用日期、年薪
Oracle

SQL> select empno,ename,job,hiredate,(sal + comm)*12 年薪
  2  from emp;

     EMPNO ENAME        JOB           HIREDATE          年薪
---------- -------------------- ------------------ ------------ ----------
      7369 SMITH        CLERK           17-DEC-80
      7499 ALLEN        SALESMAN       20-FEB-81         22800
      7521 WARD         SALESMAN       22-FEB-81         21000
      7566 JONES        MANAGER        02-APR-81
      7654 MARTIN        SALESMAN       28-SEP-81         31800
      7698 BLAKE        MANAGER        01-MAY-81
      7782 CLARK        MANAGER        09-JUN-81
      7788 SCOTT        ANALYST        19-APR-87
      7839 KING         PRESIDENT       17-NOV-81
      7844 TURNER        SALESMAN       08-SEP-81         18000
      7876 ADAMS        CLERK           23-MAY-87

     EMPNO ENAME        JOB           HIREDATE          年薪
---------- -------------------- ------------------ ------------ ----------
      7900 JAMES        CLERK           03-DEC-81
      7902 FORD         ANALYST        03-DEC-81
      7934 MILLER        CLERK           23-JAN-82

14 rows selected.

PPAS

scott=# select empno,ename,job,hiredate,(sal + comm)*12 年薪
scott-# from emp;
 empno | ename  |    job    |      hiredate      |   年薪   
-------+--------+-----------+--------------------+----------
  7369 | SMITH  | CLERK     | 17-DEC-80 00:00:00 |         
  7499 | ALLEN  | SALESMAN  | 20-FEB-81 00:00:00 | 22800.00
  7521 | WARD   | SALESMAN  | 22-FEB-81 00:00:00 | 21000.00
  7566 | JONES  | MANAGER   | 02-APR-81 00:00:00 |         
  7654 | MARTIN | SALESMAN  | 28-SEP-81 00:00:00 | 31800.00
  7698 | BLAKE  | MANAGER   | 01-MAY-81 00:00:00 |         
  7782 | CLARK  | MANAGER   | 09-JUN-81 00:00:00 |         
  7788 | SCOTT  | ANALYST   | 19-APR-87 00:00:00 |         
  7839 | KING   | PRESIDENT | 17-NOV-81 00:00:00 |         
  7844 | TURNER | SALESMAN  | 08-SEP-81 00:00:00 | 18000.00
  7876 | ADAMS  | CLERK     | 23-MAY-87 00:00:00 |         
  7900 | JAMES  | CLERK     | 03-DEC-81 00:00:00 |         
  7902 | FORD   | ANALYST   | 03-DEC-81 00:00:00 |         
  7934 | MILLER | CLERK     | 23-JAN-82 00:00:00 |         
(14 rows)

52驗證NVL()函式，如果運算元為null，則自動替換成預設值0
Oracle

SQL>  select NVL(null,0),NVL(3,0) from dual;

NVL(NULL,0)   NVL(3,0)
-----------    ----------
      0             3

PPAS

scott=# select NVL(null,0),NVL(3,0) from dual;
 nvl | nvl 
-----+-----
   0 |   3
(1 row)

53使用NVL()函式解決年薪為null的情況
Oracle

SQL> select empno,ename,job,hiredate,(sal + NVL(comm,0))*12 年薪
  2  from emp;

     EMPNO ENAME        JOB           HIREDATE          年薪
---------- -------------------- ------------------ ------------ ----------
      7369 SMITH        CLERK           17-DEC-80          9600
      7499 ALLEN        SALESMAN       20-FEB-81         22800
      7521 WARD         SALESMAN       22-FEB-81         21000
      7566 JONES        MANAGER        02-APR-81         35700
      7654 MARTIN        SALESMAN       28-SEP-81         31800
      7698 BLAKE        MANAGER        01-MAY-81         34200
      7782 CLARK        MANAGER        09-JUN-81         29400
      7788 SCOTT        ANALYST        19-APR-87         36000
      7839 KING         PRESIDENT       17-NOV-81         60000
      7844 TURNER        SALESMAN       08-SEP-81         18000
      7876 ADAMS        CLERK           23-MAY-87         13200

     EMPNO ENAME        JOB           HIREDATE          年薪
---------- -------------------- ------------------ ------------ ----------
      7900 JAMES        CLERK           03-DEC-81         11400
      7902 FORD         ANALYST        03-DEC-81         36000
      7934 MILLER        CLERK           23-JAN-82         15600

14 rows selected.

PPAS

scott=# select empno,ename,job,hiredate,(sal + NVL(comm,0))*12 年薪
scott-# from emp;
 empno | ename  |    job    |      hiredate      |   年薪   
-------+--------+-----------+--------------------+----------
  7369 | SMITH  | CLERK     | 17-DEC-80 00:00:00 |  9600.00
  7499 | ALLEN  | SALESMAN  | 20-FEB-81 00:00:00 | 22800.00
  7521 | WARD   | SALESMAN  | 22-FEB-81 00:00:00 | 21000.00
  7566 | JONES  | MANAGER   | 02-APR-81 00:00:00 | 35700.00
  7654 | MARTIN | SALESMAN  | 28-SEP-81 00:00:00 | 31800.00
  7698 | BLAKE  | MANAGER   | 01-MAY-81 00:00:00 | 34200.00
  7782 | CLARK  | MANAGER   | 09-JUN-81 00:00:00 | 29400.00
  7788 | SCOTT  | ANALYST   | 19-APR-87 00:00:00 | 36000.00
  7839 | KING   | PRESIDENT | 17-NOV-81 00:00:00 | 60000.00
  7844 | TURNER | SALESMAN  | 08-SEP-81 00:00:00 | 18000.00
  7876 | ADAMS  | CLERK     | 23-MAY-87 00:00:00 | 13200.00
  7900 | JAMES  | CLERK     | 03-DEC-81 00:00:00 | 11400.00
  7902 | FORD   | ANALYST   | 03-DEC-81 00:00:00 | 36000.00
  7934 | MILLER | CLERK     | 23-JAN-82 00:00:00 | 15600.00
(14 rows)

54查詢每個僱員的編號，姓名，年薪（sal+comm）、基本工資、獎金
Oracle

SQL>  select empno,ename,NVL2(comm,sal+comm,sal),sal,comm
  2  from emp;

     EMPNO ENAME        NVL2(COMM,SAL+COMM,SAL)        SAL     COMM
---------- -------------------- ----------------------- ---------- ----------
      7369 SMITH                    800        800
      7499 ALLEN                   1900       1600      300
      7521 WARD                    1750       1250      500
      7566 JONES                   2975       2975
      7654 MARTIN                   2650       1250     1400
      7698 BLAKE                   2850       2850
      7782 CLARK                   2450       2450
      7788 SCOTT                   3000       3000
      7839 KING                    5000       5000
      7844 TURNER                   1500       1500        0
      7876 ADAMS                   1100       1100

     EMPNO ENAME        NVL2(COMM,SAL+COMM,SAL)        SAL     COMM
---------- -------------------- ----------------------- ---------- ----------
      7900 JAMES                    950        950
      7902 FORD                    3000       3000
      7934 MILLER                   1300       1300

14 rows selected.

PPAS

scott=# select empno,ename,NVL2(comm,sal+comm,sal),sal,comm 
scott-# from emp;
 empno | ename  |  nvl2   |   sal   |  comm   
-------+--------+---------+---------+---------
  7369 | SMITH  |  800.00 |  800.00 |        
  7499 | ALLEN  | 1900.00 | 1600.00 |  300.00
  7521 | WARD   | 1750.00 | 1250.00 |  500.00
  7566 | JONES  | 2975.00 | 2975.00 |        
  7654 | MARTIN | 2650.00 | 1250.00 | 1400.00
  7698 | BLAKE  | 2850.00 | 2850.00 |        
  7782 | CLARK  | 2450.00 | 2450.00 |        
  7788 | SCOTT  | 3000.00 | 3000.00 |        
  7839 | KING   | 5000.00 | 5000.00 |        
  7844 | TURNER | 1500.00 | 1500.00 |    0.00
  7876 | ADAMS  | 1100.00 | 1100.00 |        
  7900 | JAMES  |  950.00 |  950.00 |        
  7902 | FORD   | 3000.00 | 3000.00 |        
  7934 | MILLER | 1300.00 | 1300.00 |        
(14 rows)

55驗證NULLIF()函式
Oracle

SQL> select NULLIF(1,1),NULLIF(1,2) FROM dual;

NULLIF(1,1) NULLIF(1,2)
-----------   -----------
              1

PPAS

scott=# select NULLIF(1,1),NULLIF(1,2) FROM dual;
 nullif | nullif 
--------+--------
        |      1
(1 row)

56驗證NULLIF()函式
Oracle

SQL> select empno,ename,job,LENGTH(ename),LENGTH(job),NULLIF(LENGTH(ename),LENGTH(job)) 
  2  from emp;

     EMPNO ENAME        JOB           LENGTH(ENAME) LENGTH(JOB)
---------- -------------------- ------------------ ------------- -----------
NULLIF(LENGTH(ENAME),LENGTH(JOB))
---------------------------------
      7369 SMITH        CLERK                   5       5


      7499 ALLEN        SALESMAN               5       8
                5

      7521 WARD         SALESMAN               4       8
                4

     EMPNO ENAME        JOB           LENGTH(ENAME) LENGTH(JOB)
---------- -------------------- ------------------ ------------- -----------
NULLIF(LENGTH(ENAME),LENGTH(JOB))
---------------------------------
      7566 JONES        MANAGER                5       7
                5

      7654 MARTIN        SALESMAN               6       8
                6

      7698 BLAKE        MANAGER                5       7
                5
     EMPNO ENAME        JOB           LENGTH(ENAME) LENGTH(JOB)
---------- -------------------- ------------------ ------------- -----------
NULLIF(LENGTH(ENAME),LENGTH(JOB))
---------------------------------
      7782 CLARK        MANAGER                5       7
                5

      7788 SCOTT        ANALYST                5       7
                5

      7839 KING         PRESIDENT               4       9
                4

     EMPNO ENAME        JOB           LENGTH(ENAME) LENGTH(JOB)
---------- -------------------- ------------------ ------------- -----------
NULLIF(LENGTH(ENAME),LENGTH(JOB))
---------------------------------
      7844 TURNER        SALESMAN               6       8
                6

      7876 ADAMS        CLERK                   5       5


      7900 JAMES        CLERK                   5       5

     EMPNO ENAME        JOB           LENGTH(ENAME) LENGTH(JOB)
---------- -------------------- ------------------ ------------- -----------
NULLIF(LENGTH(ENAME),LENGTH(JOB))
---------------------------------
      7902 FORD         ANALYST                4       7
                4

      7934 MILLER        CLERK                   6       5
                6
14 rows selected.

PPAS

scott=# select empno,ename,job,LENGTH(ename),LENGTH(job),NULLIF(LENGTH(ename),LENGTH(job))                   
scott-# from emp;
 empno | ename  |    job    | length | length | nullif 
-------+--------+-----------+--------+--------+--------
  7369 | SMITH  | CLERK     |      5 |      5 |       
  7499 | ALLEN  | SALESMAN  |      5 |      8 |      5
  7521 | WARD   | SALESMAN  |      4 |      8 |      4
  7566 | JONES  | MANAGER   |      5 |      7 |      5
  7654 | MARTIN | SALESMAN  |      6 |      8 |      6
  7698 | BLAKE  | MANAGER   |      5 |      7 |      5
  7782 | CLARK  | MANAGER   |      5 |      7 |      5
  7788 | SCOTT  | ANALYST   |      5 |      7 |      5
  7839 | KING   | PRESIDENT |      4 |      9 |      4
  7844 | TURNER | SALESMAN  |      6 |      8 |      6
  7876 | ADAMS  | CLERK     |      5 |      5 |       
  7900 | JAMES  | CLERK     |      5 |      5 |       
  7902 | FORD   | ANALYST   |      4 |      7 |      4
  7934 | MILLER | CLERK     |      6 |      5 |      6
(14 rows)

57測試DECODE()函式
Oracle

SQL> select  DECODE(2,1,`內容為一`,2,`內容為二`),DECODE(2,1,`內容為一`,`沒有條件滿足`)
  2  from dual;

DECODE(2,1,`內容為一`,2, DECODE(2,1,`內容為一`,`沒有條件滿足`
------------------------ ------------------------------------
內容為二         沒有條件滿足

PPAS

scott=# select  DECODE(2,1,`內容為一`,2,`內容為二`),DECODE(2,1,`內容為一`,`沒有條件滿足`)
scott-# from dual;
  decode  |    decode    
----------+--------------
 內容為二 | 沒有條件滿足
(1 row)

58查詢僱員的姓名，職位，基本工資等資訊，但是要求將所有的職位資訊都替換為中文顯示
Oracle

SQL> select ename,sal,
  2  DECODE(job,
  3  `CLERK`,`業務員`,
  4  `SALESMAN`,`銷售人員`,
  5  `MANAGER`,`經理`,
  6  `ANALYST`,`分析員`,
  7  `PRESIDENT`,`總裁`)job
  8   from emp;

ENAME                SAL JOB
-------------------- ---------- ------------------------
SMITH                800 業務員
ALLEN               1600 銷售人員
WARD               1250 銷售人員
JONES               2975 經理
MARTIN               1250 銷售人員
BLAKE               2850 經理
CLARK               2450 經理
SCOTT               3000 分析員
KING               5000 總裁
TURNER               1500 銷售人員
ADAMS               1100 業務員

ENAME                SAL JOB
-------------------- ---------- ------------------------
JAMES                950 業務員
FORD               3000 分析員
MILLER               1300 業務員

14 rows selected.

PPAS

scott=# select ename,sal,
scott-# DECODE(job,
scott(# `CLERK`,`業務員`,
scott(# `SALESMAN`,`銷售人員`,
scott(# `MANAGER`,`經理`,
scott(# `ANALYST`,`分析員`,
scott(# `PRESIDENT`,`總裁`)job
scott-#  from emp;
 ename  |   sal   |   job    
--------+---------+----------
 SMITH  |  800.00 | 業務員
 ALLEN  | 1600.00 | 銷售人員
 WARD   | 1250.00 | 銷售人員
 JONES  | 2975.00 | 經理
 MARTIN | 1250.00 | 銷售人員
 BLAKE  | 2850.00 | 經理
 CLARK  | 2450.00 | 經理
 SCOTT  | 3000.00 | 分析員
 KING   | 5000.00 | 總裁
 TURNER | 1500.00 | 銷售人員
 ADAMS  | 1100.00 | 業務員
 JAMES  |  950.00 | 業務員
 FORD   | 3000.00 | 分析員
 MILLER | 1300.00 | 業務員
(14 rows)

59在DECODE()函式中只判斷部分內容
Oracle

SQL> select ename,sal,
  2   DECODE(job,
  3  `CLERK`,`業務員`,
  4   `SALESMAN`,`銷售人員`,
  5  `MANAGER`,`經理`)job
  6  from emp;

ENAME                SAL JOB
-------------------- ---------- ------------------------
SMITH                800 業務員
ALLEN               1600 銷售人員
WARD               1250 銷售人員
JONES               2975 經理
MARTIN               1250 銷售人員
BLAKE               2850 經理
CLARK               2450 經理
SCOTT               3000
KING               5000
TURNER               1500 銷售人員
ADAMS               1100 業務員

ENAME                SAL JOB
-------------------- ---------- ------------------------
JAMES                950 業務員
FORD               3000
MILLER               1300 業務員

14 rows selected.

PPAS

scott=# select ename,sal,
scott-# DECODE(job,
scott(# `CLERK`,`業務員`,
scott(# `SALESMAN`,`銷售人員`,
scott(# `MANAGER`,`經理`)job
scott-# from emp;
 ename  |   sal   |   job    
--------+---------+----------
 SMITH  |  800.00 | 業務員
 ALLEN  | 1600.00 | 銷售人員
 WARD   | 1250.00 | 銷售人員
 JONES  | 2975.00 | 經理
 MARTIN | 1250.00 | 銷售人員
 BLAKE  | 2850.00 | 經理
 CLARK  | 2450.00 | 經理
 SCOTT  | 3000.00 | 
 KING   | 5000.00 | 
 TURNER | 1500.00 | 銷售人員
 ADAMS  | 1100.00 | 業務員
 JAMES  |  950.00 | 業務員
 FORD   | 3000.00 | 
 MILLER | 1300.00 | 業務員
(14 rows)

60顯示每個僱員的姓名，工資，職位，同時顯示新的工資（新工資的標準為業務員增長10%、銷售人員增長20%、經理增長30%、其他職位的人增長50%）
Oracle

SQL> select ename,sal,
  2  CASE job WHEN `CLERK` THEN sal*1.1
  3  WHEN `SALESMAN` THEN sal*1.2
  4  WHEN `MANAGER` THEN sal*1.3
  5  ELSE sal*1.5
  6  END 新工資
  7  from emp;

ENAME                SAL     新工資
-------------------- ---------- ----------
SMITH                800        880
ALLEN               1600       1920
WARD               1250       1500
JONES               2975     3867.5
MARTIN               1250       1500
BLAKE               2850       3705
CLARK               2450       3185
SCOTT               3000       4500
KING               5000       7500
TURNER               1500       1800
ADAMS               1100       1210

ENAME                SAL     新工資
-------------------- ---------- ----------
JAMES                950       1045
FORD               3000       4500
MILLER               1300       1430

14 rows selected.

PPAS

scott=# select ename,sal,
scott-# CASE job WHEN `CLERK` THEN sal*1.1
scott-# WHEN `SALESMAN` THEN sal*1.2
scott-# WHEN `MANAGER` THEN sal*1.3
scott-# ELSE sal*1.5
scott-# END 新工資
scott-# from emp;
 ename  |   sal   |  新工資  
--------+---------+----------
 SMITH  |  800.00 |  880.000
 ALLEN  | 1600.00 | 1920.000
 WARD   | 1250.00 | 1500.000
 JONES  | 2975.00 | 3867.500
 MARTIN | 1250.00 | 1500.000
 BLAKE  | 2850.00 | 3705.000
 CLARK  | 2450.00 | 3185.000
 SCOTT  | 3000.00 | 4500.000
 KING   | 5000.00 | 7500.000
 TURNER | 1500.00 | 1800.000
 ADAMS  | 1100.00 | 1210.000
 JAMES  |  950.00 | 1045.000
 FORD   | 3000.00 | 4500.000
 MILLER | 1300.00 | 1430.000
(14 rows)

61驗證COALESCE()函式的功能
Oralce

SQL> select ename,sal,comm,
  2   COALESCE(comm,100,2000),
  3  COALESCE(comm,null,null)
  4  from emp;

ENAME                SAL       COMM COALESCE(COMM,100,2000)
-------------------- ---------- ---------- -----------------------
COALESCE(COMM,NULL,NULL)
------------------------
SMITH                800                    100

ALLEN               1600        300               300
             300

WARD               1250        500               500
             500

ENAME                SAL       COMM COALESCE(COMM,100,2000)
-------------------- ---------- ---------- -----------------------
COALESCE(COMM,NULL,NULL)
------------------------
JONES               2975                    100

MARTIN               1250       1400              1400
            1400

BLAKE               2850                    100

ENAME                SAL       COMM COALESCE(COMM,100,2000)
-------------------- ---------- ---------- -----------------------
COALESCE(COMM,NULL,NULL)
------------------------
CLARK               2450                    100

SCOTT               3000                    100

KING               5000                    100

ENAME                SAL       COMM COALESCE(COMM,100,2000)
-------------------- ---------- ---------- -----------------------
COALESCE(COMM,NULL,NULL)
------------------------
TURNER               1500      0             0
               0

ADAMS               1100                    100

JAMES                950                    100

ENAME                SAL       COMM COALESCE(COMM,100,2000)
-------------------- ---------- ---------- -----------------------
COALESCE(COMM,NULL,NULL)
------------------------
FORD               3000                    100

MILLER               1300                    100

14 rows selected.

PPAS

scott=# select ename,sal,comm,
scott-# COALESCE(comm,100,2000),
scott-# COALESCE(comm,null,null) 
scott-# from emp;
 ename  |   sal   |  comm   | coalesce | coalesce 
--------+---------+---------+----------+----------
 SMITH  |  800.00 |         |      100 |         
 ALLEN  | 1600.00 |  300.00 |   300.00 |   300.00
 WARD   | 1250.00 |  500.00 |   500.00 |   500.00
 JONES  | 2975.00 |         |      100 |         
 MARTIN | 1250.00 | 1400.00 |  1400.00 |  1400.00
 BLAKE  | 2850.00 |         |      100 |         
 CLARK  | 2450.00 |         |      100 |         
 SCOTT  | 3000.00 |         |      100 |         
 KING   | 5000.00 |         |      100 |         
 TURNER | 1500.00 |    0.00 |     0.00 |     0.00
 ADAMS  | 1100.00 |         |      100 |         
 JAMES  |  950.00 |         |      100 |         
 FORD   | 3000.00 |         |      100 |         
 MILLER | 1300.00 |         |      100 |         
(14 rows)

本連載部落格主要探討Oracle與PPAS(PostgreSQL)資料庫的差異，以幫助更多讀者瞭解如何實現資料庫遷移！

《卸甲筆記》-單行函式對比之一
2016-06-21
筆記函式
《卸甲筆記》-分組統計查詢對比
2016-06-27
筆記
ORACLE單行函式與多行函式之三：數值函式
2013-11-02
Oracle函式
《卸甲筆記》-PostgreSQL和Oracle的資料型別的對比系列五：其它型別
2016-07-15
筆記SQLOracle資料型別
《卸甲筆記》-PostgreSQL和Oracle的SQL差異分析之五：函式的差異(五)
2016-08-18
筆記SQLOracle函式
《卸甲筆記》-PostgreSQL和Oracle的SQL差異分析之五：函式的差異(三)
2016-08-12
筆記SQLOracle函式
《卸甲筆記》-PostgreSQL和Oracle的SQL差異分析之五：函式的差異(一)
2016-08-08
筆記SQLOracle函式
《卸甲筆記》-PostgreSQL和Oracle的資料型別的對比系列四：大資料型別
2016-07-13
筆記SQLOracle資料型別大資料
時間函式對比
2014-08-11
函式
學習筆記:11種行為型設計模式簡單對比
2009-10-13
筆記設計模式
SQL菜鳥筆記之第九篇 SQL單行函式（下）
2011-08-31
SQL筆記函式
SQL菜鳥筆記之第八篇 SQL單行函式（中）
2011-08-31
SQL筆記函式
MySQL和Oracle行值表示式對比(r11筆記第74天)
2017-02-13
MySqlOracle筆記
ES6函式比對ES5函式
2019-02-28
函式
常見函式之單行函式
2020-07-04
函式
SQL菜鳥筆記之第七篇 SQL單行函式（上）
2011-08-31
SQL筆記函式
CSS 小結筆記之三種樣式表
2018-08-01
CSS筆記
intrans函式（對比度拉伸）
2018-12-05
函式
SQLServer和Oracle常用函式對比
2007-08-28
SQLServerOracle函式
javascript 的函式宣告與表示式對比
2020-10-10
JavaScript函式
python筆記-函式
2021-09-09
Python筆記函式
筆記：Python函式
2018-10-01
筆記Python函式
C++筆記--函式
2016-05-25
C++筆記函式
【筆記】普通生成函式
2024-03-25
筆記函式
對beego的控制器函式進行單測
2019-02-16
Go函式
SQLServer和Oracle的常用函式對比
2010-11-30
SQLServerOracle函式
JavaScript 匿名函式與具名函式執行效率比較
2019-06-05
JavaScript函式
js匿名函式和具名函式執行效率比較
2017-03-29
JS函式
ORACLE單行函式與多行函式之一
2013-11-02
Oracle函式
非同步之三：Async 函式的使用及簡單實現
2019-02-25
非同步函式
async函式學習筆記。
2019-02-16
函式筆記
substr擷取函式筆記
2020-12-03
函式筆記
Go 函式學習筆記
2017-08-12
Go函式筆記
函式-PHP手冊筆記
2015-06-19
函式PHP筆記
mysql常用函式--個人筆記
2015-07-29
MySql函式筆記
【筆記】oracle 統計函式
2009-03-13
筆記Oracle函式
分析函式（學習筆記）
2007-05-02
函式筆記
生成函式學習筆記
2024-08-02
函式筆記

《卸甲筆記》-單行函式對比之三

相關文章