转换函数和条件表达式

1、转换函数

数据转换类型：

隐式数据类型转换显式数据类型转换

1.1隐式数据类型转换

隐式数据类型转换：在后台转换隐式数据类型转换：数据类型与默认格式匹配 FromToVARCHAR2 or CHARNUMBERVARCHAR2 or CHARDATENUMBERVARCHAR2 or CHARDATEVARCHAR2 or CHAR

Oracle服务器可以在表达式中自动执行数据类型转换。 例如，hire_date>'01-JAN-90’会导致从字符串’01-JAN-90’到日期的隐式转变； grade列时CHAR(2)类型，grade=2将导致数值2隐式转换为字符串"2"。

1.2显式数据类型转换

1）使用TO_CHAR函数对日期的转换

格式：TO_CHAR(date,‘format_model’) 注意事项：

必须包含在单引号中大小写敏感可以包含任意的有效的日期格式可以使用fm去掉多余的空格或者前导零与日期值使用逗号隔开

日期格式组成：

元素含义YYYY完整的年份YEAR年（英文）MM双位数字月份MONTH完整的月份名称MON月份的三个字母缩写DY星期三个字母的缩写DAY完整的星期名称DD月份的数字天

实例

SQL> select to_char(sysdate,'yyyy-mm-dd') 2 from dual; # 将当前日期以字符用数字年-月-日显示出来 TO_CHAR(SY ---------- 2019-07-20 时间格式： HH24:MI:SS AM15:45:32 PM 使用双引号向日期中添加数字： DD “of” MONTH12 of OCTOBER 日期在月份中的位置 ddspthfourteenth

实例

SQL> select to_char(sysdate,'dd "of" mm,yyyy,hh24:mi:ss am') 2 from dual; TO_CHAR(SYSDATE,'DD"OF"MM ------------------------- 20 of 07,2019,21:41:49 pm

2）使用TO_CHAR函数对数字进行转换

格式：TO_CHAR(number,‘format_model’) 下面是TO_CHAR函数常用的几种格式：

元素含义9代表一个数字0强制显示0$放置一个浮动的美元符号L采用浮动本地货币符号.打印小数点,打印一个逗号作为千位分隔符

实例

SQL> select ename,to_char(sal,'L99,999.00') sal 2 from emp 3 where ename='KING'; ENAME SAL ---------- -------------------- KING $5,000.00

3)使用TO_NUMBER函数

格式：TO_CHAR(char[,‘format_model’])

使用TO_NUMBER函数将字符转换为数字格式

4)使用TO_DATE函数

格式：TO_DATE(char[,‘format_model’])

使用TO_DATE函数将字符串转换为日期格式

2、嵌套函数

单行函数可以嵌套到任意层级嵌套函数的执行顺序是由内到外 SQL> select to_char(add_months(to_date('1995-09-01','yyyy-mm-dd'),12*18),'yyyy-mm-dd day') birthday_18 2 from dual; BIRTHDAY_18 -------------------- 2013-09-01 sunday

执行顺序： 1、to_date函数执行，得到一个日期 2、add_months函数执行，将日期添加12*18个月，得到第二个日期 3、to_char函数执行，将第二个日期以’yyyy-mm-dd day’的形式输出

3、通用函数

这些函数适用于任何数据类型，同时也适用于空值：

函数含义NVL(expr1,expr2)转换null值为实际的值NVL2(expr1,expr2,expr3)如果expr1不是null，那么转换为expr2，如果expr1是空值，则转换为expr3NULLIF(expr1,expr2)比较两个表达式，如果它们相等，返回null；如果它们不相等，返回第一个表达式COALESCE(expr1,expr2,…,exprn)返回表达式列表中的第一个非空表达式

3.1NVL函数

将空值转换成一个已知的值：

可以使用的数据类型有数字、日期、字符数据类型必须匹配： 实例 SQL> select ename,nvl(to_char(mgr),'I''m boss') mgr 2 from emp; ENAME MGR ---------- ---------------------------------------- SMITH 7902 ALLEN 7698 WARD 7698 JONES 7839 MARTIN 7698 BLAKE 7839 CLARK 7839 SCOTT 7566 KING I'm boss TURNER 7698 ADAMS 7788 JAMES 7698 FORD 7566 MILLER 7782 14 rows selected.

将MGR一栏中的空值改为”I’m boss“，因为MGR一栏为数值类型，所以需要将MGR一栏改为字符格式。

3.2NVL2函数

实例

SQL> select ename,sal,comm,nvl2(to_char(comm),to_char(sal*12+comm),'sorry') yeal_sal 2 from emp; ENAME SAL COMM YEAL_SAL ---------- ---------- ---------- ---------------------------------------- SMITH 800 sorry ALLEN 1600 300 19500 WARD 1250 500 15500 JONES 2975 sorry MARTIN 1250 1400 16400 BLAKE 2850 sorry CLARK 2450 sorry SCOTT 3000 sorry KING 5000 sorry TURNER 1500 0 18000 ADAMS 1100 sorry JAMES 950 sorry FORD 3000 sorry MILLER 1300 sorry 14 rows selected.

comm为空值则返回字符sorry，不为空则返回年薪，并且数据类型都要匹配。

3.3NULLIF函数

NULLIF函数比较两个表达式。如果它们相等，函数返回null。如果它们不相等，函数返回第一个表达式。但是，不能为第一个表达式指定文字NULL。

SQL> select ename,job,length(ename) n1,length(job) n2, 2 nullif(length(ename),length(job)) 3 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.

4、条件表达式

在SQL语句中使用IF-THEN-ELSE逻辑使用两种方法： CASE表达式DECODE函数

4.1CASE表达式

实例

SQL> select ename,job,sal, 2 case job when 'CLERK' then 1.2*sal 3 when 'SALESMAN' then 1.5*sal 4 else sal 5 end aaa 6 from emp; ENAME JOB SAL AAA ---------- --------- ---------- ---------- SMITH CLERK 800 960 ALLEN SALESMAN 1600 2400 WARD SALESMAN 1250 1875 JONES MANAGER 2975 2975 MARTIN SALESMAN 1250 1875 BLAKE MANAGER 2850 2850 CLARK MANAGER 2450 2450 SCOTT ANALYST 3000 3000 KING PRESIDENT 5000 5000 TURNER SALESMAN 1500 2250 ADAMS CLERK 1100 1320 JAMES CLERK 950 1140 FORD ANALYST 3000 3000 MILLER CLERK 1300 1560 14 rows selected.

查询名字，工作，月薪，工作为CLERK的显示1.2倍月薪，工作为SALESMAN的显示1.5倍月薪，其他的显示月薪。

实例

SQL> select ename,sal, 2 case when sal>3000 then 'good job' 3 when sal between 2000 and 3000 then 'nice' 4 when sal<1000 then 'bye~' 5 else 'Emmm~' 6 end lala 7 from emp; ENAME SAL LALA ---------- ---------- -------- SMITH 800 bye~ ALLEN 1600 Emmm~ WARD 1250 Emmm~ JONES 2975 nice MARTIN 1250 Emmm~ BLAKE 2850 nice CLARK 2450 nice SCOTT 3000 nice KING 5000 good job TURNER 1500 Emmm~ ADAMS 1100 Emmm~ JAMES 950 bye~ FORD 3000 nice MILLER 1300 Emmm~ 14 rows selected.

查询名字，月薪，月薪大于3000显示good job，在2000和3000之间显示nice，小于1000显示bye_{，其他显示Emm}。

4.2DECODE函数

实例

SQL> select ename,job,sal, 2 decode(job,'CLERK',1.2*SAL,'SALESMAN',1.5*SAL,SAL) 3 FROM EMP; ENAME JOB SAL DECODE(JOB,'CLERK',1.2*SAL,'SALESMAN',1.5*SAL,SAL) ---------- --------- ---------- -------------------------------------------------- SMITH CLERK 800 960 ALLEN SALESMAN 1600 2400 WARD SALESMAN 1250 1875 JONES MANAGER 2975 2975 MARTIN SALESMAN 1250 1875 BLAKE MANAGER 2850 2850 CLARK MANAGER 2450 2450 SCOTT ANALYST 3000 3000 KING PRESIDENT 5000 5000 TURNER SALESMAN 1500 2250 ADAMS CLERK 1100 1320 JAMES CLERK 950 1140 FORD ANALYST 3000 3000 MILLER CLERK 1300 1560 14 rows selected.

查询名字、工作、月薪、工作为CLERK的显示1.2倍月薪，为SALESMAN的显示1.5倍月薪，其他显示月薪

实例

SQL> select ename,sal, 2 decode(sign(sal-3000),-1,'a','b') 3 abc 4 from emp; ENAME SAL A ---------- ---------- - SMITH 800 a ALLEN 1600 a WARD 1250 a JONES 2975 a MARTIN 1250 a BLAKE 2850 a CLARK 2450 a SCOTT 3000 b KING 5000 b TURNER 1500 a ADAMS 1100 a JAMES 950 a FORD 3000 b MILLER 1300 a 14 rows selected.

查询名字，月薪，月薪大于等于3000显示b，小于3000显示a。 sign函数括号里的值为正、负、零，分别对用1、-1、0。若与-1相同，则显示a；与-1不同，显示b