一、转换函数 函数的转换主要对数据类型的转换 包括： 隐式数据类型转换、显示数据类型转换 1、隐式转换： 在后台转换，数据类型与默认格式匹配 Oracle服务器可以在表达式中自动执行数据类型转换。 可以隐式地将VARCHAR2或CHAR值转换为表达式中的数字或日期数据类型。 2、显示数据转换 a、使用TO_CHAR函数对日期的转换 TO_CHAR将datetime数据类型转换为format_model指定格式的VARCHAR2数据类型值。 格式模型是描述存储在字符串中的datetime格式的字符文字。
to_char(date,‘format_model’) 格式：必须包含在单引号中 大小写敏感 可以包含任意的有效的日期格式 可以使用fm去掉多余的空格或者前导零 与日期值用逗号隔开 SQL> select hiredate,to_char(hiredate,‘mm-yyyy’) from emp;
HIREDATE TO_CHAR --------- ------- 17-DEC-80 12-1980 20-FEB-81 02-1981 22-FEB-81 02-1981 02-APR-81 04-1981 28-SEP-81 09-1981 01-MAY-81 05-1981 09-JUN-81 06-1981 19-APR-87 04-1987 17-NOV-81 11-1981 08-SEP-81 09-1981 23-MAY-87 05-1987 03-DEC-81 12-1981 03-DEC-81 12-1981 23-JAN-82 01-1982 14 rows selected.日期格式的组成
b 、TO_CHAR函数对数字进行转换
to_char(number,‘format_model’) 常用的格式： Oracle服务器显示一串数字符号(#)，而不是数字超过格式模型中提供的数字的整数。 Oracle服务器将存储的decimal值四舍五入到格式模型中提供的小数位数。
c 、to_number函数 使用to_number函数将字符转换为数字格式： to_number(char,[ , ‘format_madel’])
d 、 to_date 函数 使用 to_date 函数将字符串转换为日期格式： to_date(char,[ , ‘format_madel’])
使用TO_NUMBER和TO_DATE函数 字符串转换为数字或日期。要完成此任务，可使用TO_NUMBER或TO_DATE函数。 • 字符参数中的标点符号和引号必须与格式模型的相应部分完全匹配(大小写除外)。 • 字符参数不能有额外的空格。 • 字符参数中的数字数据必须与格式模型中的相应元素具有相同的位数。如果没有fm，字符参数中的数字可以省略前导零。
3. 嵌套函数 单层函数可以嵌套到任意层级 嵌套函数的执行顺序是由内到外。
4. 通用函数
a、 nvl 函数 nvl (expr1,expr2)
将空值转换成一个已知的值： 可以使用的数据类型有数字、日期、字符。 数据类型必须匹配： b 、nvl2 函数 nvl2(expr1,expr2,expr3)
如果expr1不是null，那么转换为expr2，如果exprt1 是空值，则转换为expr3
SQL> select ename , sal , comm , sal+nvl(comm,0),nvl2(comm,sal+comm,sal) 2 from emp; ENAME SAL COMM SAL+NVL(COMM,0) NVL2(COMM,SAL+COMM,SAL) ---------- ---------- ---------- --------------- ----------------------- SMITH 800 800 800 ALLEN 1600 300 1900 1900 WARD 1250 500 1750 1750 JONES 2975 2975 2975 MARTIN 1250 1400 2650 2650 BLAKE 2850 2850 2850 CLARK 2450 2450 2450 SCOTT 3000 3000 3000 KING 5000 5000 5000 TURNER 1500 0 1500 1500 ADAMS 1100 1100 1100 JAMES 950 950 950 FORD 3000 3000 3000 MILLER 1300 1300 1300 14 rows selected.c 、 nullif 函数 NULLIF函数比较两个表达式。如果它们相等，函数返回null。 如果它们不相等，函数返回第一个表达式。但是不能为第一个表达式指定文字NULL。
SQL> select length(ename),length(job),nullif(length(ename),length(job)) 2 from emp; LENGTH(ENAME) LENGTH(JOB) NULLIF(LENGTH(ENAME),LENGTH(JOB)) ------------- ----------- --------------------------------- 5 5 5 8 5 4 8 4 5 7 5 6 8 6 5 7 5 5 7 5 5 7 5 4 9 4 6 8 6 5 5d 、coalesce 函数 返回表达式列表中的第一个非空表达式。 coalesce与nvl相比的优点在于coalesce可以同时处理交替的多个值。 如果第一个表达式非空，则返回这个表达式，对其他的参数进行coalesce。
二 、条件表达式
在SQL语句中使用 IF - THEN - ELSE逻辑 使用两种方式：
CASE表达式DECODE函数#查询员工姓名，职位，薪资和奖金 当职位为ANALYST的员工年薪按照150%进行发放 如果职位是salesman的员工年薪按照140%进行发放 如果职位是MANAGER的员工年薪按照130%进行发放 其他职位的员工年薪按照正常的进行发放
1、case表达式 SQL> select ename,job,sal,comm, 2 case job when ‘ANALYST’ then (sal*12+nvl(comm,0))1.5 3 when ‘SALESMAN’ then (sal12+nvl(comm,0))1.4 4 when ‘MANAGER’ then (sal12+nvl(comm,0))1.3 5 else sal12+nvl(comm,0) end “year_sal” 6 from emp;
ENAME JOB SAL COMM year_sal ---------- --------- ---------- ---------- ---------- SMITH CLERK 800 9600 ALLEN SALESMAN 1600 300 27300 WARD SALESMAN 1250 500 21700 JONES MANAGER 2975 46410 MARTIN SALESMAN 1250 1400 22960 BLAKE MANAGER 2850 44460 CLARK MANAGER 2450 38220 SCOTT ANALYST 3000 54000 KING PRESIDENT 5000 60000 TURNER SALESMAN 1500 0 25200 ADAMS CLERK 1100 13200 ENAME JOB SAL COMM year_sal ---------- --------- ---------- ---------- ---------- JAMES CLERK 950 11400 FORD ANALYST 3000 54000 MILLER CLERK 1300 15600 14 rows selected.2，decode函数
SQL> select ename,job,sal,comm, 2 decode(job,'ANALYST',(sal*12+nvl(comm,0))*1.5, 3 'SALESMAN',(sal*12+nvl(comm,0))*1.4, 4 'MANAGER',(sal*12+nvl(comm,0))*1.3, 5 sal*12+nvl(comm,0)) "new_sal" 6 from emp; ENAME JOB SAL COMM new_sal ---------- --------- ---------- ---------- ---------- SMITH CLERK 800 9600 ALLEN SALESMAN 1600 300 27300 WARD SALESMAN 1250 500 21700 JONES MANAGER 2975 46410 MARTIN SALESMAN 1250 1400 22960 BLAKE MANAGER 2850 44460 CLARK MANAGER 2450 38220 SCOTT ANALYST 3000 54000 KING PRESIDENT 5000 60000 TURNER SALESMAN 1500 0 25200 ADAMS CLERK 1100 13200 ENAME JOB SAL COMM new_sal ---------- --------- ---------- ---------- ---------- JAMES CLERK 950 11400 FORD ANALYST 3000 54000 MILLER CLERK 1300 15600 14 rows selected.