《卸甲笔记》-单行函数对比之三

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)数据库的差异,以帮助更多读者了解如何实现数据库迁移!

上一篇:【分享】通过Excel生成批量SQL语句,处理大量数据的好办法


下一篇:spring依赖注入失效或不成功的一种情况和解决办法