Oracle有很多内置函数:单行函数和集合函数
单行函数是指当查询表或视图时每行都能返回一个结果,可用于SELECT,WHERE,ORDER BY 等子句中,而集合函数是作用在多行记录上返回一个结果,可用于带GROUP BY或HAVING子句的查询中,单行函数数量比较多
注:dual是一个虚拟表,用来构成select的语法规则,Oracle里保证dual里面永远只有一条数据,所以可以使用dual来测试这些函数。部分操作:select user from dual;查看当前用户
select "TO_CHAR"(sysdate,'yyyy-mm-dd hh:MM:ss')from dual;–获得当前系统时间
select SYS_CONTEXT('USERENV','TERMINAL') from dual; -获得主机名
select SYS_CONTEXT('USERENV','language') from dual; -获得当前local
select dbms_random.random from dual; -获得一个随机数
ABS(n)函数:用于返回绝对值,输入参数为数值型,如果参数可以隐式转换为数值类型,也可以
SELECT ABS(100),ABS(-100),ABS('100') FROM DUAL;
MOD(n2,n1)函数,该函数表示返回n2除以n1的余数,参数为任意数值或可以隐式转换为数值的类型,如果n1为0,那么函数返回n2:
SELECT MOD(5,2),MOD(8/3,5),MOD('10',5),MOD(-10,6),MOD(1,0) FROM DUAL;
SING(n)函数,返回参数n的符号,正数返回1,0返回0,负数返回-1,但如果n为binary_float或者binary_double类型是,n>=0或者n=NaN函数会返回1:
SELECT SIGN('9'),SIGN(-9),SIGN(0.00),SIGN(-2*'9') FROM DUAL;
ceil()取整函数(ceil向上取整,floor向下取整)
SELECT ceil(66.6) num1,floor(66.6) num2 from dual;
返回固定小数位数(round(),四舍五入,trunc:直接截断)
SELECT round(66,666,2) num1,trunc(66.666,2) from dual;
获取字符串长度:
LENGTH函数,该函数可以得到指定字符串的长度,返回类型是数字:
SELECT "LENGTH"('ABCDE神FGHI') FROM DUAL;
字符串连接:
concat(char1,char2)函数,该函数连接两个参数并返回(只支持两个)。效果和连接符||相似
SELECT "CONCAT"('战神', '刑天'),'战神'||'刑天' FROM DUAL;
连接符||
SELECT '张三'||'name' 姓名 from DUAL;
字符串搜索函数:
INSTR函数,该函数可以让我们在指定字符串中搜索是否存在另一个字符串
INSTR|INSTRB|INSTRC|INSTR2|INSTR4 string,substring,position,occurrence
INSTR:字符为单位 INSTRB字节为单位 INSTRC 以unicode字符为单位
string:待搜索的字符串,substring:要搜索的字符串,position:搜索开始位置,默认为1,表示字符串左边第一个位置,负数从右边开始
occurrence:substring第几次出现,默认为1;
SELECT INSTR('THIS IS A 测试','测') FROM DUAL;
替换字符串函数:
语法结构:REPLACE(char,search_string[,replacement_string];
SELECT "REPLACE"('THIS IS A TEST','TES','RESUL') FROM DUAL;
Oracle中的日期类型只有date和TIMESTAMP,TIMESTAMP是比date更精确的类型。日期时间的函数用于处理日期时间类型的数据,Oracle以7位数字格式来存放日期数据,包括世纪,年,月,日,小时,分钟,秒,并且日期格式为DD-MON-YY。
在Oracle中准确是从星期日开始到星期六结束,时间差以天数为单位
SYSDATE函数,该函数没有参数,可以得到系统的当前日期
SELECT SYSDATE FROM DUAL;
SYSTIMESTAMP函数,该函数没有参数,返回系统时间,返回类型为带时区的TIMESTAMP类型
SELECT SYSTIMESTAMP FROM DUAL;
得到数据库时区函数:
SELECT DBTIMEZONE FROM DUAL;
与date操作的两个转换函数:
to_date(char[fmt[,'nls_param’]]),to_char(date[,fmt,[,nls_param]])
to_date(char[fmt[,'nls_param’]]):将字符类型按一定格式转换为日期类型:
to_date(‘2019-01-23’,‘yyyy-mm-dd’):前者为字符串后者为转化格式,注意对应
select to_date('2017-12-11','yyyy-mm-dd') from dual
to_char(date,’yyyy-mm-dd hh24:mi:ss):将日期按一定格式转换为字符类型
select to_char(sysdate,'yyyy:mm:dd hh24:mi:ss ') from dual
返回多种日期格式:to_char(sysdate,’’);
select to_char(sysdate,'yyyy') 年, to_char(sysdate,'mm')月, to_char(sysdate,'DD') 日, to_char(sysdate,'HH24') 时, to_char(sysdate,'MI')分, to_char(sysdate,'SS')秒, to_char(sysdate,'DAY') 天, to_char(sysdate,'Q') 第几季度, to_char(sysdate,'W') 第几周 from dual |
转换函数可以完成不同数据类型之间的转换,是平常使用比较多的函数类型之一
自动类型转换:select 1+'1' from dual;
数字转换成字符串:select 12,to_char(12) from dual;
select 123,to_char(1234,'999') from dual;
将字符串转换为数字:select '123',to_number('3.14') from dual;
CAST(expr as type_name)函数,该函数是数据类型转换,可以把expr参数转换成type_name类型,基本上用于数字与字符之间以及字符与日期类型之间的转换。
SELECT CAST('123'AS INTEGER) AS VHR,CAST(123 AS VARCHAR2(8)) AS NUM,CAST(SYSDATE AS VARCHAR2(12))
AS DT FROM DUAL;
字符串转换成date类型:select add_months('1-7月-2017',2) from dual;
返回表达式为NULL的函数
COALESCE(expr)函数。返回列表中第一个不为null的表达式,如果都为null,返回null
SELECT COALESCE(NULL,9-9,NULL) FROM DUAL
排除指定条件函数
LNNVL(condition)函数,该函数可以得到除了condition要求条件之外的数据,包括null的条件,通常用于WHERE条件中
SELECT * FROM PRODUCTINFO WHERE LNNVL(QUANTITY>=70)
替换NULL值函数
NVL(expr1,expr2)函数,替换NULL值,如果expr1为NULL值,则返回expr2的值,否则返回expr1的值。要求两个参数类型一致,至少相互之间进行隐式转换。
SELECT PRODUCTNAME,NVL(QUANTITY,0),CATEGORY FROM PRODUCTINFO;
NVL2(expr1,expr2,expr3)函数,该函数通NVL类似,不同的是当expr1为NULL时,返回expr3的值,当expr1不为空时,返回expr2的值
集合函数经常配合GROUP BY或HAVING子句使用,该类型函数中除了COUNT函数都会忽略列值为NULL的数据
AVG([distinct[all] expr]函数,该函数可求取指定列的平均值,表示某组的平均值,返回数值类型,参数含义:
distinct:去除重复的值 all:表示所有的值,包括重复的值,默认值 expr:表达式,只能是数值类型
SELECT AVG(ALL age+20) FROM student;
使用该函数式,WHERE条件子句中可以使用条件,与group by子句一起使用
SELECT AVG(ALL PRODUCTPRICE) FROM PRODUVTINFO GROUP BY CATEGORY;
COUNT(*|[distinct][all]expr)函数,该函数用来计算记录的数量或某列的个数,函数必须指定列名,或使用*,参数含义:
*:表示计算所有记录 distinct:表示去除重复记录 all:代表所有的,默认 expr:要计算的值,通常是表的列
SELECT COUNT(*) FROM PRODUCTINFO;
MAX([distinct|all]expr)函数,返回指定列的 最大值
MIN([distinct|all]expr)函数,返回指定列的最小值
SELECT * FROM PRODUCTINFO
WHERE PRODUCTPRICE = (SELECT MAX(PRODUCTPRICE) FROM PRODUCTINFO)
SUM([distinct|all] expr)函数,分组计算指定列的和,如果不分组,则函数默认把整个表作为一组,参数代表含义:
例:计算不同类型产品的 数量和
SELECT SUM(ALL QUANTITY),CATEGORY FROM PRODUCTINFO GROUP BY CATEGORY;
GROUPING函数可以接受一列,返回0或者1,如果列值为空,那么GROUPING()返回1,如果列值非空返回0,GROUPING只能在使用ROLLUP或CUBE的查询中使用。当需要在返回空值的地方返回某个值时,GROUPING()非常有用。
GROUPING函数既可以与CUBE操作一起使用,也可以与ROLLUP一起使用。GROUPING函数使用一个单独的列表示,在GROUPING函数中的expr必须匹配一个GROUP BY子句中的表达式,该函数返回值0或者1
select grouping(division_id),division_id,sum(salary) from employees group by rollup(division_id)
使用CASE函数:
select case grouping(division_id) when 1 then ‘all divisions’ else division_id end as div, sum(salary)
from employee group by rollup(division_id) order by division_id;
结构:case when 条件值then 语句when 条件值 then 语句 else 否则语句 end
示例:select case when t.result=’1’ then‘成功’ when t.result=’2’ then‘失败’ else ‘不知道’
end result from user;
多条件分支判断,最后一个参数表示默认值
select decode(字段名,‘条件1’,‘值1’,’条件2’,’值2’,默认值) from 表名
select decode(identity, 'student', '学生', 'teacher', '老师', 'nnn', '不知道',‘神秘’) as identity from person
row_number() over的用法 用于排序去重操作
因篇幅问题不能全部显示,请点此查看更多更全内容