Oracle SQL 提供了用于执行特定操作的专用函数。这些函数增强了 SQL 语言的功能。函数可以接受零个或者多个输入参数，并返回一个输出结果。 Oracle 数据库中主要使用两种类型的函数：
-
单行函数：对每一个函数应用在表的记录中时，只能输入一行结果，返回一个结果。
例如：MOD(x,y)返回 x 除以 y 的余数（x 和 y 可以是两个整数，也可以是表中的整数列）。常用的单行函数有：
字符函数：对字符串操作。
数字函数：对数字进行计算，返回一个数字。
转换函数：可以将一种数据类型转换为另外一种数据类型。
日期函数：对日期和时间进行处理。 -
聚合函数：聚合函数同时可以对多行数据进行操作，并返回一个结果。例如：SUM(x)，返回结果集中 x 列的总合。
以下详细介绍各类函数的使用。
（1）转换函数
转换函数将值从一种数据类型转换为另外一种数据类型。
TO_DATE(X,[,fmt]) 把一个字符串以fmt格式转换成一个日期类型
具体如何输出需要看本地PL/SQL的首选项设置时间格式设置。以下例子的输出结果，格式均为 yyyy/mm/dd hh:mi:ss
--将字符串转换为日期
SELECT TO_DATE('2019-12-31', 'yyyy-mm-dd') FROM DUAL;--输出：2019/12/31
SELECT TO_DATE('2019-12-31 15:20:45', 'yyyy-mm-dd hh24:mi:ss') FROM DUAL; --输出：2019/12/31 15:20:45
SELECT TO_DATE('2019年12月31日', 'yyyy"年"mm"月"dd"日') AS T FROM DUAL;--输出：2019/12/31
--两个日期间的天数
SELECT FLOOR(SYSDATE - TO_DATE('20191201', 'yyyymmdd')) FROM DUAL;--输出：170
--查询某个时间段内的数据
select * from t_user l
where l.start_date between to_date(q'/2020-05-12 00:00:00/', 'yyyy-mm-dd hh24:mi:ss')
and to_date(q'/2020-05-12 23:00:00/', 'yyyy-mm-dd hh24:mi:ss');
TO_CHAR：转换为字符串
--日期转化为字符串
SELECT TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') AS NOWTIME FROM DUAL; --2020-05-19 16:09:38
--获取时间的年
SELECT TO_CHAR(SYSDATE, 'yyyy') AS NOWYEAR FROM DUAL; -- 2020
--获取时间的月
SELECT TO_CHAR(SYSDATE, 'mm') AS NOWMONTH FROM DUAL; -- 05
--获取时间的日
SELECT TO_CHAR(SYSDATE, 'dd') AS NOWDAY FROM DUAL; -- 19
--获取时间的时
SELECT TO_CHAR(SYSDATE, 'hh24') AS NOWHOUR FROM DUAL; -- 16
--获取时间的分
SELECT TO_CHAR(SYSDATE, 'mi') AS NOWMINUTE FROM DUAL; -- 10
--获取时间的秒
SELECT TO_CHAR(SYSDATE, 'ss') AS NOWSECOND FROM DUAL; -- 26
TO_NUMBER(X,[,fmt]) 把一个字符串以fmt格式转换为一个数字
SELECT TO_NUMBER('-$12,345.67','$99,999.99')"num" FROM dual;
结果:-12345.67
（2）字符函数
字符函数接受字符参数，这些参数可以是表中的列，也可以是一个字符串表达式。
INSTR (string, set[, start[, occurrence] ] ),该命令“string”中从“start”位置开始查找字符集合的位置，再查找“set”出现的第一次、第二次等等的“occurrence”（次数）。“start”的值也可以是负数，代表从字符串结尾开始向反方向搜索。
select instr('abc','a') from dual; -- 返回 1
select instr('abc','bc') from dual; -- 返回 2
select instr('abc abc','a',1,2) from dual; -- 返回 5
select instr('abc','bc',-1,1) from dual; -- 返回 2
select instr('abc','d') from dual; -- 返回 0
LTRIM(string,trim_set)从左边删除字符，此处“string”是数据库的列，或者是字面字符串，而“trim_set” 是要去掉的字符的集合。
SELECT LTRIM('abcdab','a') FROM DUAL;
结果:bcdab
RTRIM(string,trim_set)从右侧删除字符，此处“string”是数据库的列，或者是字面字符串，而“trim_set” 是要去掉的字符的集合。
SELECT RTRIM('abcdef', 'f') FROM DUAL;
结果:abcde
REPLACE(string, if, then)用 0 或其他字符代替字符串中的字符。“if”是字符或字符串，对于每个出现在“string”中的“if”，都用“then”的内容代替。
SELECT REPLACE('JACK and JUE','J','BL') FROM DUAL;
结果:BLACK and BLUE
SUBSTR(string, start [,count])返回“string”中截取的一部分。该命令截取“string”的一个子集，从“start”位置开始，持续“count”个字符。如果不指定“count”，则从“start”开始截取到“string”的尾部。
SELECT SUBSTR('ABCDEFGIJKLM',3,4) FROM DUAL;
结果:CDEF
TRANSLATE(string, if, then)“if”中字符的位置，并检查“then”的相同位置，然后用该位置的字符替换 “string”中的字符。
SELECT TRANSLATE('sdfdfAEIOU3333','AEIOU', '1111') FROM DUAL;
结果:sdfdf11113333
UPPER(string) 返回大写的“string”。
SELECT UPPER('aptech computer education') FROM dual;
结果:APTECH COMPUTER EDUCATION
（3）数值型常用函数
数字函数接受数字参数，参数可以来自表中的一列，也可以是一个数字表达式。
round 取四舍五入的几位小数
select round(1.2345, 3) from dual;
结果:1.235
trunc(x,[y]) 返回截尾到y位小数的x值
y是正整数，就是四舍五入到小数点后 y 位。TRUNC (5.654,2)=5.65。
y 是负整数，四舍五入到小数点左边|y|位。TRUNC (351.654,-2)=300。
select trunc(1.2345, 2) from dual;
结果:1.23
select trunc(23.33) from dual;
结果: 23
select trunc(1.2399, 2) from dual;
结果:1.23
select CASE WHEN trunc(1.00)-1.00=0 THEN '整数' ELSE '不是整数' END from dual;
结果:整数
ceil(n) 返回大于或等于x的最大整数
select ceil(23.33) from dual;
结果: 24
floor(n) 返回等于或小于n的最大整数:
select floor(23.33) from dual;
结果: 23
round(n,m) 将n四舍五入,保留小数点后m位
select round(123.5678,2) from dual;
结果: 123.57
select round(23.33) from dual;
结果: 23
mod(m,n) m除以n的余数,若n=0,则返回m
select mod(7,5) from dual;
结果: 2
sign(n) 若n=0,则返回0,否则,n>0,则返回1,n<0,则返回-1
select sign(12) from dual;
结果: 1
power(m,n) m的n次方
select power(3,2) from dual;
结果: 9
sqrt(n) n的平方根
select sqrt(25) from dual ;
结果: 5
（4）常用字符函数
字符函数接受字符参数，这些参数可以是表中的列，也可以是一个字符串表达式。
initcap(char) 把每个字符串的第一个字符换成大写
select initicap('mr.ecop') from dual;
结果: Mr.Ecop
lower(char) 整个字符串换成小写
select lower('MR.ecop') from dual;
结果: mr.ecop
replace(char,str1,str2) 字符串中所有str1换成str2
select replace('Scott','s','Boy') from dual;
结果: Boycott
substr(char,m,n) 取出从m字符开始的n个字符的子串
select substr('ABCDEF',2,2) from dual;
结果: CD
length(char) 求字符串的长度
select length('ACD') from dual;
结果: 3
|| 并置运算符
select 'ABCD'||'EFGH' from dual;
结果: ABCDEFGH
（5）聚合函数
聚合函数同时对一组数据进行操作，返回一行结果，比如计算一组数据的总和，平均值等。
AVG（表达式）求平均值
SUM(表达式) 求和
MIN(表达式)、MAX(表达式) 最小值、最大值
COUNT（表达式）数据统计
（6）其它单行函数
NVL(exp1,exp2)：如果 exp1的值为null,则返回exp2的值，否则返回exp1的值。
SELECT ename,NVL(col, 0) FROM TEST; --如果col列为null，则返回0
NVL2(exp1,exp2,exp2)：
如果exp1的值为null,则返回exp2的值，否则返回exp3的值。
网友评论