关于Oracle单行函数与多行函数

209次阅读

没有评论

共计 2876 个字符，预计需要花费 8 分钟才能阅读完成。

Oracle 单行函数分为五种类型：字符函数、数值函数、日期函数、转换函数、通用函数

字符串函数

1、大小写转换函数：
· 转大写：字符串 UPPER(字符串 | 数据列)；
· 转小写：字符串 LOWER(字符串 | 数据列)。

2、首字母大写，其余字母小写：字符串 INITCAP(字符串 | 列)

3、取得字符串长度：数字 LENGTH(字符串 |

4、替换指定字符串的内容：字符串 REPLACE(字符串 | 列，要替换的内容，新的内容)

5、字符串截取操作：
· 由指定位置截取到结尾：字符串 SUBSTR(字符串 | 数据列，截取开始索引)；
· 指定截取的开始和结束位置：字符串 SUBSTR(字符串 | 数据列，截取开始索引，截取结束索引)；

字符串的索引下标是从 1 开始的

SUBSTR()函数还可以设置为负数，表示由后的指定位置开始

6、去掉左右空格函数：字符串 TRIM(字符串 | 列)

数字函数

1、四舍五入函数：数字 ROUND(数字 | 列 [，保留小数位])

2、截取小数函数：数字 TRUNC(数字 | 列 [，保留小数位])

正数向后截取，负数向前截取

select trunc(sysdate, ‘yy’) from dual;– 当年第一天

select trunc(sysdate, ‘q’) from dual;– 当前时间所在的季度的第一天

select trunc(sysdate, ‘mm’) from dual;– 当月第一天

select trunc(sysdate, ‘d’) from dual;– 返回本周的第一天（周日为第一天）

3、求模：数字 MOD(数字 1 | 列 1，数字 2 | 列 2)

日期函数

当前的系统日期时间，伪列：SYSDATE、SYSTIMESTAMP。

· 日期 + 数字 = 日期，表示若干天之后的日期；
· 日期 – 数字 = 日期，表示若干天之前的日期；
· 日期 – 日期 = 数字，表示两个日期之间的间隔天数。

1、计算两个日期之间所经历的月数：数字 MONTHS_BETWEEN(日期 1 | 列 1，日期 2 | 列 2)

2、加上指定月之后的日期：日期 ADD_MONTHS(日期 | 列，月数)

思考题：要求计算出每一位雇员到今天为止雇佣的年限
A、例如：今天是 2014 年 08 月 12 日，CLARK 的雇佣日期是：1981 年 06 月 09 日
B、到今天 CLARK 已经在公司服务了：33 年、02 月、03 天

SELECT ename,hiredate,
TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) year ,
TRUNC(MOD(MONTHS_BETWEEN(SYSDATE,hiredate),12)) months ,
TRUNC(SYSDATE – ADD_MONTHS(hiredate,MONTHS_BETWEEN(SYSDATE,hiredate))) day
FROM emp ;

3、求出指定的下一个一周时间数的日期：日期 NEXT_DAY(日期 | 列，一周时间数)

4、求出指定日期所在月的最后一天日期：日期 LAST_DAY(日期 | 列)

转换函数

1、转字符串数据：字符串 TO_CHAR(数字 | 日期 | 列，转换格式)
在进行转换格式设置的时候要根据不同的数据类型进行格式标记的定义：
· 日期：年（yyyy）、月（mm）、日（dd）、时（HH、HH24）、分（mi）、秒（ss）；
· 数字：一位任意数字（9）、本地货币（L）。

SELECT TO_CHAR(SYSDATE,’yyyy-mm-dd hh24:mi:ss’) FROM dual ;
范例：拆分日期数据
SELECT TO_CHAR(SYSDATE,’yyyy’) year ,
TO_CHAR(SYSDATE,’mm’) months ,
TO_CHAR(SYSDATE,’dd’) day
FROM dual ;
范例：格式化数字显示 SELECT TOCHAR(234789234798,’L999,999,999,999,999′) FROM dual

2、转日期数据：日期 TO_DATE(字符串，转换格式)

范例：将字符串变为日期
SELECT ‘1981-09-15’,TO_DATE(‘1981-09-15′,’yyyy-mm-dd’) FROM dual ;
但是在 Oracle 之中提供有自动的转换方式，如果字符串按照日期的格式编写，那么可以自动由字符串变为日期。

3、转数字类型：数字 TO_NUMBER(字符串，转换格式)

范例：将字符串变为数字
SELECT TO_NUMBER(‘1’) + TO_NUMBER(‘2’) FROM dual ;
SELECT ‘1’ + ‘2’ FROM dual ;
在 Oracle 里面这些自动的数据类型转换功能是非常方便的，包括日期和字符串、字符串和数字。

通用函数

1、处理 null 数据：数字 NVL(列 | 数据，默认值)

2、多数据判断：数据 DECODE(字段 | 数据，判断数据 1，显示数据 1，判断数据 2，显示数据 2，… [默认显示])

SELECT ename,job ,
DECODE(job,’CLERK’,’ 办事员 ’,’SALESMAN’,’ 销售 ’,’MANAGER’,’ 经理 ’,’ANALYST’,’ 分析员 ’,’PRESIDENT’,’ 总裁 ’,’ 未
处理 ’)

和单行函数相比，oracle 提供了丰富的基于组的，多行的函数。这些函数能在 select 或 select 的 having 子句中使用，当用于 select 子串时常常都和 GROUP BY 一起使用。多行函数分为接收多个输入，返回一个输出。

组函数：

– 求员工的工资总和
select sum(sal) from emp;
– 求个数
select count(*) from emp;
– 求平均工资
select sum(sal)/count(*) 方式一, avg(sal) 方式二 from emp;
– 关于空值：组函数会自动滤空
select count(*), count(comm) from emp;
–max 和 min：求最高工资和最低工资
select max(sal) 最高工资,min(sal) 最低工资 from emp;
– 分组数据：求各个部门的平均工资
select deptno,avg(sal) from emp group by deptno;
–group by 作用于多列：按部门，不同的工种，统计平均工资
–group by 作用于多列：先按照第一列分组；如果相同，再按照第二列分组
select deptno,job,avg(sal) from emp group by deptno,job;
–：求部门的平均工资大于 2000 的部门
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
–group by 的增强
select deptno,job,sum(sal) from emp group by rollup(deptno,job);
– 不同的 deptno 空两行 / 取消设置
break on deptno skip 2/break on null