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 30PPAS
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 30PPAS
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.78900PPAS
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.78900PPAS
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-16PPAS
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 PMPPAS
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 171PPAS
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 171PPAS
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 3PPAS
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)
----------- -----------
1PPAS
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)数据库的差异，以帮助更多读者了解如何实现数据库迁移！