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

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

时间： 2024-07-30 15:50:04

《卸甲笔记》-单行函数对比之三的相关文章

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

1验证UPPER().LOWER()函数 Oracle SQL> select UPPER('SongYuejie'),LOWER('VASTDATA') from dual; UPPER('SONGYUEJIE') LOWER('VASTDATA' -------------------- ---------------- SONGYUEJIE vastdata PPAS scott=# select UPPER('SongYuejie'),LOWER('VASTDATA') from dua

《卸甲笔记》-PostgreSQL和Oracle的SQL差异分析之三：rownum和聚合函数

PostgreSQL是世界上功能最强大的开源数据库,在国内得到了越来越多机构和开发者的青睐和应用.随着PostgreSQL的应用越来越广泛,Oracle向PostgreSQL数据库的数据迁移需求也越来越多.数据库之间数据迁移的时候,首先是迁移数据,然后就是SQL.存储过程.序列等程序中不同的数据库中数据的使用方式的转换.下面根据自己的理解和测试,写了一些SQL以及数据库对象转换方面的文章,不足之处,尚请多多指教. rownum rownum是Oracle内部的一个伪列,用来表示数据在结果集中的行

《卸甲笔记》-分组统计查询对比之二

13在分组查询的SELECT子句中出现其他字段(ename) Oracle SQL> select deptno,ename,COUNT(empno) 2 from emp 3 GROUP BY deptno; select deptno,ename,COUNT(empno) * ERROR at line 1: ORA-00979: not a GROUP BY expression PPAS scott=# select deptno,ename,COUNT(empno) scott-# f

《卸甲笔记》-PostgreSQL和Oracle的SQL差异分析之五：函数的差异(六)

《卸甲笔记》-PostgreSQL和Oracle的数据类型的对比系列一：字符类型

PostgreSQL是世界上功能最强大的开源数据库,在国内得到了越来越多机构和开发者的青睐和应用.随着PostgreSQL的应用越来越广泛,Oracle向PostgreSQL数据库的数据迁移需求也越来越多.数据库之间数据迁移的时候,首先遇到的,并且也是最重要的,就是数据类型之间的转换.下面根据自己的理解和测试,写了一些数据类型之间的差异以及迁移时的注意事项的文章,不足之处,尚请多多指教. 字符类型大家知道,Oracle有四种字符类型,分别是char,varchar2,nchar,nvarcha

《卸甲笔记》-PostgreSQL和Oracle的数据类型的对比系列五：其它类型

PostgreSQL是世界上功能最强大的开源数据库,在国内得到了越来越多机构和开发者的青睐和应用.随着PostgreSQL的应用越来越广泛,Oracle向PostgreSQL数据库的数据迁移需求也越来越多.数据库之间数据迁移的时候,首先遇到的,并且也是最重要的,就是数据类型之间的转换.下面根据自己的理解和测试,写了一些数据类型之间的差异以及迁移时的注意事项的文章,不足之处,尚请多多指教. 其它类型 Oracle的内建数据类型中,还有一些其它的类型.不能够归类到字符.数字.时间和大数据类型等.比如

《卸甲笔记》-PostgreSQL和Oracle的数据类型的对比系列四：大数据类型

PostgreSQL是世界上功能最强大的开源数据库,在国内得到了越来越多机构和开发者的青睐和应用.随着PostgreSQL的应用越来越广泛,Oracle向PostgreSQL数据库的数据迁移需求也越来越多.数据库之间数据迁移的时候,首先遇到的,并且也是最重要的,就是数据类型之间的转换.下面根据自己的理解和测试,写了一些数据类型之间的差异以及迁移时的注意事项的文章,不足之处,尚请多多指教. 大数据类型 Oracle的大数据类型主要包括三类.分别是存储在数据库内部的类型,包括BLOB, CLOB,

《卸甲笔记》-基础语法对比

以Oracle中sottt用户下的数据为例,PPAS 中scott用户下面的数据由Oracle迁移而来 1 查询emp表中的数据 Oracle [root@test03 ~]# su - oracle [oracle@test03 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Thu Jun 16 22:05:19 2016 Copyright (c) 1982, 2013, Oracle. All righ

《卸甲笔记》-PostgreSQL和Oracle的数据类型的对比系列二：数字类型

PostgreSQL是世界上功能最强大的开源数据库,在国内得到了越来越多机构和开发者的青睐和应用.随着PostgreSQL的应用越来越广泛,Oracle向PostgreSQL数据库的数据迁移需求也越来越多.数据库之间数据迁移的时候,首先遇到的,并且也是最重要的,就是数据类型之间的转换.下面根据自己的理解和测试,写了一些数据类型之间的差异以及迁移时的注意事项的文章,不足之处,尚请多多指教. 数字类型 Oracle内建的数字类型有四种,分别是number,float,binary_float,bin