Oracle培训（二十八）——Oracle 11g 第三章知识点总结——单行函数

tqpfminjcz

Oracle培训（二十八）——Oracle 11g 第三章知识点总结——单行函数

　　知识点预览
　　单行函数
　　
　　单行函数
　　
　　1. SQL 函数
　　

　　
　　2.两种 SQL 函数
　　

　　
　　3.单行函数
　　a) 操作数句对象
　　b)接受函数返回一个结果
　　c)只对一行进行变换
　　d)每行返回一个结果
　　e)可以转换数据类型
　　f) 可以嵌套
　　g)参数可以是一列或一个值
　　
　　function_name [(arg1, arg2,...)]
　　
　　4.单行函数
　　
　　
　　

　　5.字符函数
　　

　　
　　
　　

　　6. 大小写控制函数
　　这类函数改变字符的大小写。
　　
　　


　　

　　显示员工 Higgins的信息:
　　

SELECT employee_id, last_name, department_id
FROM   employees
WHERE  last_name = 'higgins';


　　no rows selected
　　
　　

SELECT employee_id, last_name, department_id
FROM   employees
WHERE  LOWER(last_name) ='higgins';


　　

　　7. 这类函数控制字符:
　　


　　
　　

SELECT employee_id, CONCAT(first_name, last_name) NAME,
job_id, LENGTH (last_name),
INSTR(last_name, 'a')"Contains 'a'?"
FROM   employees
WHERE  SUBSTR(job_id, 4) ='REP';


　　8.数字函数
　　
　　a)ROUND: 四舍五入
　　ROUND(45.926, 2) 45.93
　　b)TRUNC: 截断
　　TRUNC(45.926, 2)  45.92
　　c)MOD: 求余
　　MOD(1600, 300) 100
　　

　　9.ROUND 函数
　　
　　

SELECTROUND(45.923,2), ROUND(45.923,0),
ROUND(45.923,-1)
FROM   DUAL;



　　DUAL 是一个‘伪表’，可以用来测试函数和表达式
　　10.TRUNC 函数
　　
　　

SELECT  TRUNC(45.923,2), TRUNC(45.923),
TRUNC(45.923,-2)
FROM   DUAL;


　　11. MOD 函数
　　
　　

SELECTlast_name, salary, MOD(salary, 5000)
FROM   employees
WHERE  job_id = 'SA_REP';


　　12.日期
　　a) Oracle 内部使用数字存储日期: 世纪,年,月,日,小时,分钟,秒。
　　b) 默认的日期格式是 DD-MON-RR.
　　可以只指定年的后两位在20世纪存放21世纪的日期。
　　同样可以在21世纪存放20世纪的日期。
　　
　　

SELECTlast_name, hire_date
FROM   employees
WHERE  last_name like 'G%';


　　c) 函数SYSDATE 返回:
　　日期
　　时间
　　d) 日期的数学运算
　　
　　在日期上加上或减去一个数字结果仍为日期。
　　两个日期相减返回日期之间相差的天数。
　　可以用数字除24来向日期中加上或减去小时。
　　
　　

SELECTlast_name, (SYSDATE-hire_date)/7 AS WEEKS
FROM   employees
WHERE  department_id = 90;


　　e)日期函数
　　

　　
　　
　　MONTHS_BETWEEN('01-SEP-95','11-JAN-94') -------->19.6774194
　　ADD_MONTHS('11-JAN-94',6) --------> '11-JUL-94'
　　NEXT_DAY('01-SEP-95','FRIDAY') --------> '08-SEP-95'
　　LAST_DAY('01-FEB-95')-------->'28-FEB-95'
　　
　　Assume SYSDATE ='25-JUL-95':
　　ROUND(SYSDATE,'MONTH')-------->01-AUG-95
　　ROUND(SYSDATE,'YEAR') -------->1-JAN-96
　　TRUNC(SYSDATE,'MONTH') --------> 01-JUL-95
　　TRUNC(SYSDATE,'YEAR') --------> 01-JAN-95
　　13.转换函数
　　
　　

　　
　　

　　14.隐式数据类型转换
　　
　　Oracle 自动完成下列转换：
　　

　　表达式计算中, Oracle 自动完成下列转换:
　　

　　15. 显式数据类型转换
　　

　　
　　16.TO_CHAR 函数对日期的转换
　　a) 格式：TO_CHAR(date, 'format_model')
　　b) 必须包含在单引号中而且大小写敏感。
　　c) 可以包含任意的有效的日期格式。
　　d) 可以使用 fm 去掉多余的空格或者前导零。
　　e) 与日期指用逗号隔开。
　　17. 日期格式的元素
　　
　　

　　

　　时间格式

　　使用双引号向日期中添加字符

　　日期在月份中的位置

　　
　　

SELECT last_name,
TO_CHAR(hire_date,'fmDD Month YYYY')
AS HIREDATE
FROM   employees;


　　18.TO_CHAR 函数对数字的转换
　　格式：TO_CHAR(number, 'format_model')
　　下面是在TO_CHAR 函数中经常使用的几种格式:
　　


　　

　　
　　

SELECT TO_CHAR(salary, '$99,999.00') SALARY
FROM   employees
WHERE  last_name = 'Ernst';


　　19. TO_NUMBER 和 TO_DATE 函数
　　a) 使用 TO_NUMBER 函数将字符转换成数字:
　　
　　TO_NUMBER(char[,'format_model'])
　　
　　b)使用 TO_DATE 函数将字符转换成日期:
　　
　　TO_DATE(char[,'format_model'])
　　
　　c) 这些函数可以使用fx 修饰符。 n
　　20.RR 日期格式
　　


　　

　　
　　使用RR日期格式查找雇佣日期在1990年之前的员工，
　　在1999或现在使用下面的命令会产生相同的结果:
　　

　　


SELECT last_name, TO_CHAR(hire_date, 'DD-Mon-YYYY')
FROM  employees
WHERE hire_date < TO_DATE('01-Jan-90', 'DD-Mon-RR');


　　21.嵌套函数
　　a)单行函数可以嵌套。
　　b)嵌套函数的执行顺序是由内到外。
　　

　　
　　

SELECT last_name,
NVL(TO_CHAR(manager_id), 'No Manager')
FROM   employees
WHERE  manager_id IS NULL;

　　

　　22.通用函数
　　a) 这些函数适用于任何数据类型，同时也适用于空值：
　　NVL (expr1, expr2)
　　NVL2 (expr1, expr2, expr3)
　　NULLIF (expr1, expr2)
　　COALESCE (expr1, expr2, ..., exprn)
　　23.NVL 函数
　　
　　将空值转换成一个已知的值：
　　可以使用的数据类型有日期、字符、数字。
　　函数的一般形式:
　　NVL(commission_pct,0)
　　NVL(hire_date,'01-JAN-97')
　　NVL(job_id,'No Job Yet')
　　
　　

SELECT last_name, salary,NVL(commission_pct, 0),
(salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SAL
FROM employees;


　　24.使用 NVL2 函数
　　
　　

SELECT last_name,  salary,commission_pct,
NVL2(commission_pct,
'SAL+COMM', 'SAL')income
FROM   employees WHEREdepartment_id IN (50, 80);


　　25.使用 NULLIF 函数
　　
　　

SELECT first_name, LENGTH(first_name) "expr1",
last_name,  LENGTH(last_name)  "expr2",
NULLIF(LENGTH(first_name), LENGTH(last_name)) result
FROM   employees;


　　26.使用 COALESCE 函数
　　a)COALESCE 与 NVL 相比的优点在于 COALESCE 可以同时处理交替的多个值。
　　b)如果第一个表达式非空,则返回这个表达式，对其他的参数进行COALESCE 。
　　
　　

SELECT   last_name,
COALESCE(commission_pct, salary, 10) comm
FROM     employees
ORDER BY commission_pct;


　　27.条件表达式
　　a)在 SQL 语句中使用IF-THEN-ELSE逻辑。
　　b)使用两种方法:
　　i. CASE 表达式
　　ii.DECODE 函数
　　
　　28.CASE 表达式
　　
　　在需要使用 IF-THEN-ELSE 逻辑时:
　　CASE expr WHEN
comparison_expr1 THEN return_expr1
　　[WHEN comparison_expr2
THEN return_expr2
　　 WHENcomparison_exprn
THEN return_exprn
　　 ELSE else_expr]
　　END
　　
　　下面是使用case表达式的一个例子：
　　
　　

SELECT last_name, job_id, salary,
CASE job_id WHEN'IT_PROG'  THEN  1.10*salary
WHEN'ST_CLERK' THEN  1.15*salary
WHEN'SA_REP'   THEN  1.20*salary
ELSE      salary END     "REVISED_SALARY"
FROM   employees;


　　29.DECODE 函数
　　
　　在需要使用 IF-THEN-ELSE 逻辑时:
　　DECODE(col|expression, search1, result1

　　[, search2, result2,...,]
　　[, default])
　　
　　

SELECT last_name, job_id, salary,
DECODE(job_id,'IT_PROG',  1.10*salary,
'ST_CLERK', 1.15*salary,
'SA_REP',   1.20*salary,
salary)
REVISED_SALARY
FROM   employees;


　　
　　使用decode函数的一个例子：
　　

SELECT last_name, salary,
DECODE(TRUNC(salary/2000, 0),
0,0.00,
1,0.09,
2,0.20,
3,0.30,
4,0.40,
5,0.42,
6,0.44,
0.45) TAX_RATE
FROM   employees
WHERE  department_id = 80;

　　

　　30.总结
　　a) 使用函数对数据进行计算
　　b) 使用函数修改数据
　　c)使用函数控制一组数据的输出格式
　　d)使用函数改变日期的显示格式
　　e)使用函数改变数据类型
　　f)使用 NVL 函数
　　g)使用IF-THEN-ELSE 逻辑
　　
　　31.示例
　　
　　a)initcap 首字母大写
　　
　　b) 伪列/表——方便计算
　　dual
　　
　　伪列/伪表dual
　　

select substr(‘helloworld’,1,5) from dual;
select substr(‘helloworld’,1,5) from dual;——从1开始
select sysdate from dual; 查询当前时间
select sysdate + 1 from dual; --天数加1


　　
　　c) 计算出每个员工的工龄（整数）
　　

selectround((sysdate - hiredate)/365,0) from emp;


　　
　　d)将工龄超过30年的雇员的薪水提成30%
　　

select ename,round((sysdate -hiredate)/365,0),sal,sal*(1+0.3) from emp whe
re (round((sysdate - hiredate)/365,0))> 20;


　　
　　

select ename,round((sysdate -hiredate)/365,0) as "雇佣日期",sal,sal*(1+0.3
) from emp where round((sysdate -hiredate)/365,0) > 31;


　　
　　

select ename,round((sysdate -hiredate)/365,0) as hireage,sal,sal*(1+0.3
) from emp where round((sysdate -hiredate)/365,0) > 31;


　　
　　e) 大问题化整为零
　　
　　

select next_day(sysdate, '星期一') from dual;
select last_day(sysdate) from dual;
--to_number(‘’); 数字形式的字符串
select to_char(sysdate,'fmYYYY-MM-DD')from dual; 去掉前导0
select to_char(sal, '$9,999,999.00') fromemp;
select to_char(sal, 'L9,999,999.00') fromemp;



　　
　　
　　f) 去除月字
　　

selectto_char(to_date('2012-12-21','YYYY-MM-DD'),'YYYY-MM-DD') from dual;


　　
　　g) nvl
　　

selectnvl(comm,0),ename from emp;
select sal,nvl(sal+comm,sal) from emp;
select deptno,ename,sal,
case deptno when 10 then nvl(comm,0) + 100
when 20 thennvl(comm,0) + 200
when 30 thennvl(comm,0) + 300
end "Comm"
from emp;
select deptno,ename,sal,
case deptno when 10 then nvl(comm,0) + deptno  *  10
when 20 thennvl(comm,0) + deptno  *  10
when 30 thennvl(comm,0) + deptno  *  10
else comm end "Comm"
from emp;
select deptno,ename,sal,
decode(deptno, 10,nvl(comm,0) + 100,
20, nvl(comm,0) +200,
30, nvl(comm, 0) +300,
comm) "奖金"
from emp;

　　h) 计算出每个员工的工龄（整数）
　　Sysdate / round(,0)
　　 1).先求出每个雇员的雇佣天数
　　

   Selectsysdate-hiredate from emp;

　　2).求雇佣年份
　　

  Select(sysdate-hiredate)/365 from emp;

　　3).将雇员年份计算成整数（四舍五入）
　　

    Select round((sysdate-hiredate)/365,0) hireage from emp;

　　
　　i) 将工龄超过30年的雇员的薪水提成30%
　　

Select sal*1.3,round((sysdate-hiredate)/365,0) as hireage
from emp e
where round((sysdate-hiredate)/365,0)  >30;


　　
　　j)日期函数
　　Months_between(‘date1’ ,’date2’)
　　

selectmonths_between(sysdate,hiredate) from emp;



　　
　　Add_months(‘日期’,5)


select add_months(sysdate,12) from dual;

　　
　　
　　k)字符串转换成数字
　　“1abc” ->selectto_number(‘1abc’) from dual;
　　


　　

　　l) to_char()
　　将当前系统日期以中文格式输出（年-月-日）
　　

Selectto_char(sysdate,’YYYY-MM-DD’) from dual;


　　m) to_date()
　　

Select to_char(to_date(‘2012-12-21’,’YYYY-MM-DD’),’YYYY年MM月DD日’) from dual;


　　n) nvl
　　slect NVL(comm,0) from emp;
　　
　　将雇员的基本工资和奖金相加输出
　　

　　
　　

select sal,sal+ NVL(comm,0)from emp;

　　o) case语句
　　对所有员工奖金（comm）加上对应的部门编号乘10；只考虑部门编号是10,20,30的情况