13.转换函数
1.隐式与显示数据转换
--在表达式中Oracle服务器能自动转换
-- from varchar2 or char to number
-- from varchar2 or char to date
-- from number to varchar2 or char
-- from date to varchar2 or char
2.处理日期函数
--必须用单引号引起来
--区分大小写
--可用有效日期格式元素
--能去除填充的空格或前置的零
--用逗号与日期隔开
hr@ORCLPDB01 2023-02-19 11:59:00> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss am') from dual;
TO_CHAR(SYSDATE,'YYYY-
----------------------
2023-02-19 11:59:00 am
Elapsed: 00:00:00.00
hr@ORCLPDB01 2023-02-19 11:59:00> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss AM') from dual;
TO_CHAR(SYSDATE,'YYYY-
----------------------
2023-02-19 11:59:01 AM
Elapsed: 00:00:00.00
-- FM 去除开头和结尾的空格
hr@ORCLPDB01 2023-02-19 11:59:01> select to_char(sysdate,'Day,"the" ddth "of" Month,yyyy') from dual;
TO_CHAR(SYSDATE,'DAY,"THE"DDTH"OF"MONTH,YYYY')
------------------------------------------------------------------------------------------
Sunday ,the 19th of February ,2023
Elapsed: 00:00:00.00
hr@ORCLPDB01 2023-02-19 12:00:23> select to_char(sysdate,'FMDay,"the" ddth "of" Month,yyyy') from dual;
TO_CHAR(SYSDATE,'FMDAY,"THE"DDTH"OF"MONTH,YYYY')
------------------------------------------------------------------------------------------
Sunday,the 19th of February,2023
Elapsed: 00:00:00.01
--使用to_char函数对数字进行转换
scott@ORCLPDB01 2023-02-19 12:02:24> select sal,to_char(sal,'$99,999.00') from emp;
SAL TO_CHAR(SAL
---------- -----------
800 $800.00
1600 $1,600.00
1250 $1,250.00
2975 $2,975.00
1250 $1,250.00
2850 $2,850.00
2450 $2,450.00
3000 $3,000.00
5000 $5,000.00
1500 $1,500.00
1100 $1,100.00
950 $950.00
3000 $3,000.00
1300 $1,300.00
14 rows selected.
Elapsed: 00:00:00.01 
--to_number 将字符串转换为数字格式
scott@ORCLPDB01 2023-02-19 12:02:26> select to_number('$12,345.00','$99,999.00') from dual;
TO_NUMBER('$12,345.00','$99,999.00')
------------------------------------
12345
Elapsed: 00:00:00.00
scott@ORCLPDB01 2023-02-19 12:08:04> select * from emp where hiredate>=to_date('1987-05-01','yyyy-mm-dd');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
Elapsed: 00:00:00.003.空值函数
--适合空值函数如下：
--nvl(expr1,expr2)
--如果expr1为空，返回expr2，否则返回expr1
hr@ORCLPDB01 2023-02-19 12:16:10> select last_name,salary,nvl(commission_pct,0),(salary*12)+(salary*12*nvl(commission_pct,0)) an_sal
2 from employees;
LAST_NAME SALARY NVL(COMMISSION_PCT,0) AN_SAL
------------------------- ---------- --------------------- ----------
King 24000 0 288000
Kochhar 17000 0 204000
De Haan 17000 0 204000
Hunold 9000 0 108000
Ernst 6000 0 72000
--nvl2(expr1,expr2,expr3)
--如果expr1不为空返回expr2，为空则返回expr3
hr@ORCLPDB01 2023-02-19 12:17:35> select last_name,salary,commission_pct,
2 nvl2(commission_pct,'sal+comm','sal') income
3 from employees
4 where department_id in (50,80);
LAST_NAME SALARY COMMISSION_PCT INCOME
------------------------- ---------- -------------- --------
Weiss 8000 sal
Fripp 8200 sal
Kaufling 7900 sal
Vollman 6500 sal
Mourgos 5800 sal
Nayer 3200 sal
Mikkilineni 2700 sal
Landry 2400 sal
Markle 2200 sal
Bissot 3300 sal
Atkinson 2800 sal
Marlow 2500 sal
Olson 2100 sal
Mallin 3300 sal
Rogers 2900 sal
Gee 2400 sal
Philtanker 2200 sal
Ladwig 3600 sal
Stiles 3200 sal
Seo 2700 sal
Patel 2500 sal
Rajs 3500 sal
Davies 3100 sal
Matos 2600 sal
Vargas 2500 sal
Russell 14000 .4 sal+comm
Partners 13500 .3 sal+comm
Errazuriz 12000 .3 sal+comm
--nullif(expr1,expr2)
--如果相同返回空，否则返回expr1
scott@ORCLPDB01 2023-02-19 12:25:11>select ename,job,length(ename) n1,length(job) n2, nullif(length(ename),length(job)) from emp;
ENAME JOB N1 N2 NULLIF(LENGTH(ENAME),LENGTH(JOB))
---------- --------- ---------- ---------- ---------------------------------
SMITH CLERK 5 5
ALLEN SALESMAN 5 8 5
WARD SALESMAN 4 8 4
JONES MANAGER 5 7 5
MARTIN SALESMAN 6 8 6
BLAKE MANAGER 5 7 5
CLARK MANAGER 5 7 5
SCOTT ANALYST 5 7 5
KING PRESIDENT 4 9 4
TURNER SALESMAN 6 8 6
ADAMS CLERK 5 5
JAMES CLERK 5 5
FORD ANALYST 4 7 4
MILLER CLERK 6 5 6
14 rows selected.
--coalesce(expr1,expr2,...,exprn)
--如果第一个表达式为非空，就返回该表达式，如果时空值，就返回第二个表达式，如果前两个表达式为空，就返回第三个表达式，依次类推，就返回第n个。
hr@ORCLPDB01 2023-02-19 12:28:38> select last_name,employee_id,
2 coalesce(to_char(commission_pct),to_char(manager_id),'No commission and no manager')
3 from employees;
LAST_NAME EMPLOYEE_ID COALESCE(TO_CHAR(COMMISSION_PCT),TO_CHAR
------------------------- ----------- ----------------------------------------
King 100 No commission and no manager
Kochhar 101 100
De Haan 102 1004.条件表达式
--case语法
--第一种写法
scott@ORCLPDB01 2023-02-19 12:35:09>
select empno,ename,sal,job,
case job
when 'ANALYST' then sal*1.1
when 'CLERK' then sal*1.15
when 'MANAGER' then sal*1.2
else sal*1.25
end new_sal
from emp order by job;
EMPNO ENAME SAL JOB NEW_SAL
---------- ---------- ---------- --------- ----------
7788 SCOTT 3000 ANALYST 3300
7902 FORD 3000 ANALYST 3300
7934 MILLER 1300 CLERK 1495
7900 JAMES 950 CLERK 1092.5
7369 SMITH 800 CLERK 920
7876 ADAMS 1100 CLERK 1265
7698 BLAKE 2850 MANAGER 3420
7566 JONES 2975 MANAGER 3570
7782 CLARK 2450 MANAGER 2940
7839 KING 5000 PRESIDENT 6250
7844 TURNER 1500 SALESMAN 1875
7654 MARTIN 1250 SALESMAN 1562.5
7521 WARD 1250 SALESMAN 1562.5
7499 ALLEN 1600 SALESMAN 2000
14 rows selected.
Elapsed: 00:00:00.00
--第二种写法
select empno,ename,sal,job,
case when job='ANALYST' then sal*1.1
when job='CLERK' then sal*1.15
when job='MANAGER' then sal*1.2
else sal*1.25
end new_sal
from emp order by job;
EMPNO ENAME SAL JOB NEW_SAL
---------- ---------- ---------- --------- ----------
7788 SCOTT 3000 ANALYST 3300
7902 FORD 3000 ANALYST 3300
7934 MILLER 1300 CLERK 1495
7900 JAMES 950 CLERK 1092.5
7369 SMITH 800 CLERK 920
7876 ADAMS 1100 CLERK 1265
7698 BLAKE 2850 MANAGER 3420
7566 JONES 2975 MANAGER 3570
7782 CLARK 2450 MANAGER 2940
7839 KING 5000 PRESIDENT 6250
7844 TURNER 1500 SALESMAN 1875
7654 MARTIN 1250 SALESMAN 1562.5
7521 WARD 1250 SALESMAN 1562.5
7499 ALLEN 1600 SALESMAN 2000
14 rows selected.
Elapsed: 00:00:00.00
--decode语法
scott@ORCLPDB01 2023-02-19 12:32:48> select empno,ename,job,sal,
decode(job,'ANALYST', sal*1.1,
'CLERK', sal*1.15,
'MANAGER', sal*1.20,
sal*1.25)
new_sal
7 from emp order by job;
EMPNO ENAME JOB SAL NEW_SAL
---------- ---------- --------- ---------- ----------
7788 SCOTT ANALYST 3000 3300
7902 FORD ANALYST 3000 3300
7934 MILLER CLERK 1300 1495
7900 JAMES CLERK 950 1092.5
7369 SMITH CLERK 800 920
7876 ADAMS CLERK 1100 1265
7698 BLAKE MANAGER 2850 3420
7566 JONES MANAGER 2975 3570
7782 CLARK MANAGER 2450 2940
7839 KING PRESIDENT 5000 6250
7844 TURNER SALESMAN 1500 1875
7654 MARTIN SALESMAN 1250 1562.5
7521 WARD SALESMAN 1250 1562.5
7499 ALLEN SALESMAN 1600 2000
14 rows selected.
Elapsed: 00:00:00.00
