Oracle知识点总结—单行函数

单行函数
字符函数、数字函数、日期函数、转换函数、通用函数

1.字符函数
UPPER(字符串|列)：将输入的字符串变为大写返回
LOWER(字符串|列)：将输入的字符串变为小写返回
INITCAP(字符串|列)：开头首字母大写
LENGTH(字符串|列)：求出字符传长度
REPLACE(字符串|列,要替换的字符串,替换为的字符串)：进行替换
SUBSTR(字符串|列,开始点[,结束点])：字符串截取
Oracle中有一点比较麻烦、即使要验证字符串、也必须编写完整的sql语句，所以在Oracle中为了方便用户的查询验证，所以专门提供了一个"dual"的虚拟表
转大写
select UPPER('hello') from dual;
要求用户输入一个字符串，&替代变量
select * from emp where ename = upper('&str');

查询名字长度是5的雇员
select * from emp where LENGTH(ename) = 5;

字符串截取
select ename,substr(ename,3) from emp;
oracle中截取字符串的开始点0和1都代表第一个字符
select ename,substr(ename,0,3) from emp;
select ename,substr(ename,1,3) from emp;
截取雇员姓名的后三个字符
select ename,substr(ename,length(ename)-2) from emp;
设置负数，表示从后指定位置截取
select ename,substr(ename,-3) from emp;

2.数字函数：
ROUND(数字|列[,保留小数的位数])：四舍五入的操作
TRUNC(数字|列[,保留小树的位数])：舍弃指定位置的内容
MOD（数字1,数字2）：取模，取余数

验证ROUND()函数；
select ROUND(-345.5344),ROUND(345.4344),ROUND(345.4344,2),ROUND(345.4344,-1) from dual;

验证TRUNC()函数
select TRUNC(-345.5344),TRUNC(345.4344),TRUNC(345.4344,2),TRUNC(345.4344,-1) from dual;

验证MOD()函数
select MOD(10,3) from dual;

3.日期函数
取得当前日期：
select SYSDATE from dual;
日起计算
日期+ 数字 若干天后的日期：
select SYSDATE+3 from dual;
日期- 数字 若干天前的日期
select SYSDATE-3 from dual;
日期- 日期 两个日期间的天数
select SYSDATE-hiredate from emp;

LAST_DAY(日期)：求出指定日期的最后一天 (月最后一天)
select LAST_DAY(SYSDATE) from dual;

NEXT_DAY(日期,星期数)：求出下一个指定星期X的日期
select NEXT_DAY(SYSDATE,'星期一') from dual;

ADD_MONTHS(日期,数字)：求出若干月之后的日期
select ADD_MONTHS(SYSDATE,2) from dual;

MONTHS_BETWEEN(日期1,日期2):
select trunc(MONTHS_BETWEEN(SYSDATE,hiredate)) from emp;
以上日期可以避免闰年的月份

4.转换函数
TO_CHAR(字符串|列,格式字符串)：将日期或者数字变为字符串显示
TO_DATE(字符串,格式字符串)：将字符串变为DATE数据显示
TO_NUMBER(字符串)：将字符串变为数字显示

select to_char(sysdate,'yyyy-mm-dd') from dual;
select to_char(sysdate,'fmyyyy-mm-dd') from dual;
select to_char(sysdate,'fmyyyy-mm-dd hh:mi ss') from dual;
select to_char(sysdate,'yyyy-mm-dd hh24:mm ss') from dual;
使用to_char格式化数字
select to_char(898948938493,'999,999,999,999') from dual;
字母L表示当前语言环境下的货币符号
select to_char(898948938493,'L999,999,999,999') from dual;

TO_DATE()函数
select to_date('2012-01-11','yyyy-mm-dd') from dual;

TO_NUMBER()函数
select to_number('2')+to_number('4') from dual;
oracle很智能
select '2'+'4' from dual;

5.通用函数
NVL() 处理null
DECODE(数值|列,判断值1,显示值1,判断值2,显示值2.....) 多数值判断

要求查处每个雇员的全部年薪
select (sal+comm)*12 from emp; //有些数据为空
select (sal+ nvl(comm,0))*12 from emp;

DECODE()函数非常类似于程序中的if else语句，唯一不同的是decode函数判断的是数值，而不是逻辑条件
显示全部雇员的职位 按中文显示
select empno,ename,job,decode(job,'CLERK','办事员','SALESMAN','销售员','MANAGER','经理','ANALYST','分析员','PRESIDENT','总裁') from emp;

以年月份方式显示所有员工的服务年限

年：select ename,TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) year from emp;
月：select ename, TRUNC(MOD(MONTHS_BETWEEN(SYSDATE,hiredate),12)) month from emp;
日：select ename, TRUNC(SYSDATE-ADD_MONTHS(hiredate,MONTHS_BETWEEN(SYSDATE,hiredate))) day from emp;

select ename, hiredate,TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) year,TRUNC(MOD(MONTHS_BETWEEN(SYSDATE,hiredate),12)) month,TRUNC(SYSDATE-ADD_MONTHS(hiredate,MONTHS_BETWEEN(SYSDATE,hiredate))) day from emp;