Oracle 11g的内置函数(常用)

/*************************************************************************************************
内置函数
***************************************************************************************************/
-- 绝对值
select ABS(-100) from dual;
-- 取余数
select mod(100,0),mod(102,100),mod(-6,8),mod(-10,5),mod(-10,9) from dual;
-- SIGN(n) 函数返回n的符号,当n为正数时,返回1,当n为负数时返回-1,当n为0时返回0,当n为binary_float 或者binary_double类型时,n>0或者n=NaN时返回1
select sign(9),sign(0),sign(-5) from dual;
--三角函数  cos(3.1415926) 此外还有sin(n) tan(n)等等
select cos(3.1415926) from dual;
--CEIL(n) : 返回结果为大于等于输入参数的最小整数;输入参数要求是10进制数值类型,可以是非整数,也可以是可以转换成数值类型的字符串
select CEIL(5.6),CEIL(-5.6),CEIL('10.5') from dual;
-- FLOOR(n) 函数,返回小于等于参数的最小整数,输入参数要求是10进制数值类型,可以是非整数,也可以是可以转换成数值类型的字符串
select floor(10.6),floor(-10.6),floor('10.6') from dual;
-- 开根号
select sqrt(100),sqrt('53.9') from dual;--10
-- 幂运算
select power(2,2),power('2',3) from dual;
-- 四舍五入函数
select round(5.0636,2),round(-56.369,2) from dual; -- 5.06 -56.37
-- 按位数截取数值
select trunc(56369.355556,4) from dual; -- 56369.3555 

/*
字符串函数
*/
-- 获取字符串长度函数
select length('123456') from dual; -- 6
-- 字符串截取
select substr('ABCDEFG',2,2) from dual; -- BC
select substr('ABCDEFG',-2,2) from dual; -- FG
-- 字符串连接函数(可以对不同类型的参数进行连接,可以是char,varchar,nchar,nverchar2,clob,blob)
select concat('ABC','EFG'),'ABC'||'EFG' from dual; -- ABCDEFG ABCDEFG
-- 字符串的搜索
select instr('this is a ceshi','ceshi'),instr('this is a ceshi','s') from dual;
-- 转大写
select upper('abc') from dual;
-- 转小写
select lower('ABC') from dual;
-- 首字母转大写
select initcap('abcdefg') from dual;
-- 替换字符串函数(参数1: 源字符串,参数2:查找字符串,参数3:要替换字符串)
select replace('abcdefg','abc','BOY') from dual;
--删除字符串的前缀后缀字符,默认删除空格(首尾空格)
select trim(TRAILING 't' FROM 'test'),trim(' t es t ') from dual; --删除后缀
select trim(BOTH 't' FROM 'test') from dual;--都删除
select trim(LEADING 't' FROM 'test') from dual; -- 删除前缀

/*
日期函数
*/
-- 获取系统时间
select to_char(sysdate,'YYYYMMDD') from dual;
select systimestamp from dual;
-- 获取数据库时区
select dbtimezone from dual;
-- 给日期加上指定月份
select to_char(add_months(to_date('2021-01-02','YYYY-MM-DD'),1),'YYYY-MM-DD') from dual; -- 2020-02-02
-- 获取当前会话的时区
select sessiontimezone from dual;
-- 返回指定月份的最后一天
select last_day(to_date('2021-02-01','YYYY-MM-DD')) from dual;
-- 返回指定日期后一周的日期函数(指定星期几就返回下周的星期几日期)
select sysdate, next_day(to_date('2020-11-18','YYYY-MM-DD'),'星期二') from dual;
-- 提取指定的日期部分
select extract(year from sysdate) from dual;
select extract(month from sysdate) from dual;
select extract(day from sysdate) from dual;
select extract(second from timestamp '2021-01-03 11:28:38'),-- 38
       extract(minute from timestamp '2021-01-03 11:28:38')-- 28
  from dual;
-- 获取两个日期之间隔了几个月
select months_between(to_date('2021-01-28','YYYY-MM-DD'),to_date('2020-12-28','YYYY-MM-DD')) from dual; -- 1

/*
转换函数
*/
-- 字符串转换为ASCII类型字符串函数
select asciistr('测试字符串') from dual;--\6D4B\8BD5\5B57\7B26\4E32
-- 二进制转换十进制参数就就是二进制数,中间使用逗号隔开
select bin_to_num(1), bin_to_num(1, 0, 0), bin_to_num(1, 1, 1) from dual; -- 1 4 7
-- 数据类型转换函数
select cast('123' as integer) from dual; -- 字符转int类型
select cast(123 as varchar2(32)) from dual; -- number类型转字符串
select cast(sysdate as varchar2(12)) from dual;-- 日期转换成字符串类型
-- ROWID转换函数,每一行数据都有rowid长度固定位18位,并且rowid在数据库表中唯一
select rowidtochar(rowid) from dual; -- rowid转换成字符串类型
select chartorowid('t00000000000000002') from dual; -- char类型转换成rowid类型
select rowidtonchar(rowid) from dual; -- rowid转换成nchar 
select rowid from dual;
-- 字符集转换函数 参数1:源字符串,参数2:转换后的字符集,参数3: 原字符集
select convert('test string','us7ascii','zhs16gbk') from dual;
-- 数值转换成字符串函数 to_cahr()
select to_char(sysdate,'YYYYMMDD') from dual;
select to_char(16.23,'99.9999') from dual; -- 16.2300 可以看出是结果的小数位数和后面9的个数有关,且9 的个数不能原数据的少
-- 字符转成日期
select to_date('2021-01-03','YYYY-MM-DD') from dual;
-- 字符串转数字number函数
select to_number('23.56','99.99') from dual; 

/*
NULL空值函数,空值函数用来处理数据为空的函数
*/
-- 返回列表中不为空的值,如果都为空就返回null
select coalesce(null,null,null,56) from dual;-- 56
select coalesce(null,null,null) from dual; -- 空白字符串
-- 排除指定条件函数
select * from study_kay.product p where lnnvl(p.p_price>=25) -- 此条件就是查询价格小于25的数据
-- null替换函数,可以将null值替换为其他值
-- 两个函数都是对null值进行操作的函数,不同的第一个只能进行替换,第二个函数则是当参数一为null时返回参数三,当参数一不为null时返回参数二
select p.p_name, nvl(p.p_no,'0'),nvl(p.p_price,55) from study_kay.product p;
select p.p_name, nvl2(p.p_no,'0','t0'),nvl2(p.p_price,55,66) from study_kay.product p; 

/*
集合函数
*/
-- 求平均值函数avg(): 求指定列的平均值
select avg(p.p_price) from study_kay.product p;
-- 求记录数
select count(p.p_id) from study_kay.product p;
-- 求执结果集中的最大值/最小值
select max(p.p_price) from study_kay.product p;
select min(p.p_price) from study_kay.product p;
-- 求和函数
select sum(p.p_price) from study_kay.product p;

/*
其他函数
*/
-- 返回登录名函数
select user from dual; -- study_kay
-- 获取当前会话对应的用户名
select sys_context('userenv','session_user') session_user from dual;
-- 表达式匹配函数decode函数:decode(条件1=值1,返回值1,值2,返回值2,值3,......),相当于java中的if else语句
select decode(3-5,1,1,2,2,3,3,-2,-2) from dual;