05单行函数

函数基本的结构：

返回值 函数名称（列、数据）

1.字符串函数

大小写转换函数：字符串 UPPER（列/字符串数据）

转小写：字符串 LOWER（列/字符串数据）

SELECT DISTINCT LOWER('HELLO')

FROM emp;

例：用户输入

SELECT * FROM emp WHERE ename='&inputname';--这样写的话没法控制用户输入值的大小写，如果用户输入的字母全是小写的会得到无返回值的结果；

改善用户输入：SELECT * FROM emp WHERE ename=UPPER('&inputname');

 

2.首字母大写

字符串 INITCAP（列/数据）；

例：将每一个雇员的姓名首字母大写；

SELECT INITCAP（ename) from emp；

 

3.长度LENGTH

SELECT ename, LENGTH(ename) from emp;

SELECT * FROM EMP WHERE LENGTH(ename) =5;

 

4.字符串替换

字符串 REPLACE （列、数据）

例：消除空格

SELECT REPLACE('HELLO WORLD NIHAO ZAIJIAN',' ','') FROM dual;

雇员姓名中有A字母的替换成下划线

SELECT REPLACE(ename,UPPER('a'),'_') FROM emp;

 

5.字符串截取

1）字符串 SUBSTR（列/数据，开始点）

2）字符串 SUBSTR（列/数据，开始点，长度），索引从1开始，即时设置成0 也是从1开始

SELECT SUBSTR（‘helloworldnihao',11) from dual;

 

 例：截取姓名的最后三个字母：

SELECT ename,SUBSTR（ename,-3) from dual; 或者SELECT ename,SUBSTR（ename,LENGTH(ename)-2) from dual;

 

数值函数

四舍五入函数：ROUND(列或数据）（小数点之后的内容直接四舍五入）

SELECT ROUND(13.126，2)，保留两个小数点

TRUNC()，直接抹掉小数点，不进位

MOD()，求模，就是余数

 

日期函数

如何表示当前日期，数据伪列，指的是一个列，但是不存在表中，SYSDATE

例：SELECT ENAME,SYSDATE FROM EMP;

表示当前时间：SELECT SYSDATE，SYSTIMESTAMP FROM dual;

对于日期时间提供三种计算模式：

1.日期+数字=日期，表示若干天之后的日期；

2.日期-数字=日期，表示若干之前的日期；

3.日期-日期=数字，表示两个日期间的天数

例：SELECT SYSDATE+10 FROM dual;

例：计算每一位雇员到现在为止的雇佣天数

SELECT ENAME,HIREDATE,SYSDATE-HIREDATE FROM emp;

日期处理函数：

1)两个日期间的月数总和；

SELECT ENAME,MONTHS_BETWEEN(SYSYDATE,hiredate）FROM emp;

2)年限

SELECT ENAME,TRANC(MONTHS_BETWEEN(SYSYDATE,hiredate）/12 ) Years FROM emp;

 

3)增加若干月之后的日期

日期 ADD_MONTHS(日期，月数）

计算四个月后的日期SELECT ADD_MONTHS（SYSYDATE,4） from dual;

 

4.计算指定日期所在月的最后一天 SELECT LAST_DAY(SYSDATE) FROM dual；

例：查询出所有在雇佣所在月倒数第二天被雇佣的雇员信息，先找出所在月最后一天，再用日期-数字=日期的方式；

SELECT ENAME,HIREDATE,LAST_DAY(HIREDATE),LAST_DAY(HIREDATE)-2 FROM emp

WHERE LAST_DAY(HIREDATE)-2=HIREDATE;

 

5.计算下一个指定的日期：日期 next_day(日期，一周时间数）

例：计算下一个周二：

SELECT NEXT_DAY(SYSYDATE,'星期二’）FROM dual；

 

综合分析：要求查询出雇员的编号、姓名、雇佣日期、以及每一位雇员到今天为止所被雇佣的年数、月数、天数；

SELECT empno,ename,hiredate,

TRANC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) YEAR，

TRANC(MOD(MONTHS_BETWEEN(SYSDATE,hiredate),12)) MONTH;

RTANC(SYSDATE-ADD_MONTHS(hiredate,MONTHS_BETWEEN(SYSDATE,hiredate))) day

FROM emp;

 

计算年：TRANC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) YEAR，

计算月：TRANC(MOD(MONTHS_BETWEEN(SYSDATE,hiredate),12)) MONTH;

计算天数：日期1-日期2；RTANC(SYSDATE-ADD_MONTHS(hiredate,MONTHS_BETWEEN(SYSDATE,hiredate))) day(避免闰年和闰月问题）

 

转换函数：

TO_CHAR()

TO_DATE()

TO_NUMBER()

语法：字符串 TO_CHAR(列或日期或数字，转换格式）

格式化日期：

SELECT TO_CHAR(SYSDATE,'yyy-mm-dd hh:mi:ss')  FROM dual;实际开发中意义不大，只是改变了格式，实际上会破坏程序的一致性；

但是这个tochar可以实现年月日的拆分；

例：求出雇员的姓名、雇佣年份

SELECT ENAME,TO_CHAR(HIREDATE,'yyyy') from emp;

例：求出雇佣月份为2的雇员；

SELECT * from emp WHERE TO_CHAR(HIREDATE,'mm')='2 ' ; 这里用数字2也可以，因为oracle提供了自动数据类型转换的功能；

 

数字转换：任意一位数字（9），货币（L）

SELECT TO_CHAR(888847362748，'L999，999，999，999，999') FROM dual;

 

转日期函数：TO_DATE()函数：SELECT TO_DATE(‘1989-12-1’,'yyy-mm-dd‘ )  FROM dual;

 

 通用函数，ORACLE自己的函数

 NVL(列/NULL,为空默认值);处理null,空的列用0来代替NVL(comm,0)才能正常进行数学运算，不然空值计算结果都是空值；

计算出每一个雇员的年薪，基本工资加上薪金

SELECT empno,ename,job,sal,comm,NVL(comm,0),(sal+NVL(comm,0))*12 income FROM emp;

 

DECODE();多数值判断，根据不同输出结果做数据转换，针对每一列值转换

例：将雇员的职位转换为中文

SELECT ename,job,DECODE(job,'CLERK','办事员’,'SALESMAN','销售员','暂无此信息');--未转换的默认为‘暂无此信息’，如果不加默认值，则显示为空；