Oracle基础——函数

WHY？

    有时候我们需要得到对数据库记录进一步处理的数据，比如：我们需要对数据汇总，得到当前时间等，这些功能可以反复调用，我们跟java里一样：反复调用的代码就定义一个函数。Oracle数据库给我们预先提供了许多函数

函数的分类

按照处理的数据类型及功能分为：

字符型：

日期型：

数值类型：

转换函数：三种数据类型的转换函数

增强型查找工具：正则表达式

按输入参数和输出参数个数分：

单行函数：一行输入对应一行输出

聚集函数：多行输入对应一行输出

字符函数

--字符函数
--1 属性
--1.1 字符串的长度：字符，字节
select length('咚'),lengthb('冬') from dual;--1 3
--2 大小写
--2.1 句子中每个单词首字母大写
select initcap('i love bei jing') from dual;--I Love Bei Jing
--2.2 所有字母大小写
select upper('i love bj'),lower('I  LOVE BJ') from dual;--I LOVE BJ,i love bj
--3 字符填充与删除
--3.1删除
select ltrim('  abs '),ltrim('#asd#','#'),rtrim(' asd '),rtrim('#asd#','#') from dual;--abs ,asd#, asd,#asd
select trim('#' from '#sd#') from dual;--sd
--3.2填充字符
select lpad('asd',5),lpad('asd',10,'#')from dual;--  asd,#######asd
select rpad('asd',5),rpad('asd',10,'#')from dual;--asd  ,asd#######
--4 子串
--4.1 获取子串
select substr('asdfghj',3) from dual;--dfghj 获取第3个字符及以后的所有字符
select substr('asdfghj',3,4) from dual;--dfgh 获取第3个字符及以后3个，共4个
--4.2 字符串连接函数
select concat('咚咚','馬') from dual;--咚咚馬
select '咚咚'||'馬' from dual;--咚咚馬
--4.3 子串的查找
--参数1：要查找的源字符串；参数2：要在源字符串中查找的字符串；
--参数3:3 表示从左往右第3个字符开始查找，若为-3则表示从右往左第3个字符开始查找，默认为1；
--参数4:表示查找的字符串出现的次数
--位置索引号从1开始，返回值为查找到的位置号
select instr('dongdongmaon','on',3,1) from dual;--6
--4.4 用子串修改
select replace('dongdongma','d','b') from dual;--bongbongma
--4.5 发音类似词
select * from dual where soundex(last_name)
         = soundex('smythe')
--6 空值处理函数
--6.1 用指定字符串代替
select nvl(province,100000) from table;--若province为null则用100000代替
--数值函数
-- 1 正负数相关
select abs(-12) from dual;--12 绝对值
select sign(15),sign(-12),sign(0) from dual;--正负号：正数 1，负数 -1，零 0
--2 舍入
--2.1 四舍五入
--默认从小数点后一位四舍五入，只留整数；-1表示从小数点前边第一位四舍五入，0补；1表示从小数点后第二位四舍五入
select round(13.1415),round(13.1415,0),round(15.1415,-1),round(13.9615,1) from dual;--13,13 20,14
--2.2 截断
select trunc(13.14),trunc(13.14,0),trunc(13.14,-1),trunc(13.14,1) from dual;--13,13,10,13.1
--2.3 向上取整
select ceil(12.23),ceil(0.12) from dual;--13,1
--2.4 向下取整
select floor(0.12),floor(12.56) from dual;--0,12
--3 幂
--3.1 平方
select sqrt(25) from dual;--5
--3.2 任意次幂
select power(2,3),power(2,0.5) from dual;--8,1.414
--3.3 e的指数
select exp(4) from dual;--54.599
--4 三角函数
--4.1 正弦：弧度
select sin(30*3.14/180),cos(30*3.14/180),tan(30*3.14/180) from dual;--0.5,0.87,0.58
--5 对数
--5.1 自然对数
select ln(2.718) from dual;--1
--5.2 任意对数
select log(2,8) from dual; --3
--6 统计
--6.1 最大值，最小值
select max(param1),min(param1) from table;
--6.2 求平均数，方差，标准方差
select avg(param1),variance(param1),stddev(param1)from table;
--6.3 计算记录个数
select count(*) from table;--包括空值
select count(all param1) from table;--不包括空值
select count(distinct param1) from table;--不包括空值，重复值只取一次

--decode用法

decode(表达式，条件1，结果1，条件2，结果2，...)

例如：

假如要对一个企业的员工进行工资调整，对于3000块以下的提高10％，3000块以上的提高5％，那么如果用我们一般的做法就是对工资进行判断，如果大于3000，就加5％，而低于3000的就加10％。不过现在有了这个decode函数，那么就可以简单的用下面的语句：

select decode(sign(salary-3000),1,salary*1.1,-1,salary*1.05) from employee

--日期函数
--1 系统当前时间
select sysdate from dual;
--2 返回当前时间：带时区，带分秒
select systimestamp from dual;
select systimestamp at time zone 'utc' from dual;
--3 时间成分提取
--3.1 年、月、日
select extract(year from date '1998-01-07') "year",
extract(month from date '1998-01-07') "month",
extract(day from date '1998-01-07') "day" from dual;--1998  1    7
--3.2 时分秒提取
select extract(hour from systimestamp)"hour",
extract(minute from systimestamp)"minute",
extract(second from systimestamp)"second"  from dual;
--4 时间差计算
select to_date('2017/3/31 17:01:01','yyyy/mm/dd hh24:mi:ss')-to_date('2016/3/31 17:01:01','yyyy/mm/dd hh24:mi:ss') from dual;

--转换函数
--接收日期
select to_char(sysdate,'yyyy/mm/dd') from dual;--2017//03/31 到日
select to_char(sysdate, 'yyyy/mm/dd hh24:mi:ss') from dual;--2017/03/31 17:13:07 到秒
select to_char(sysdate, 'day') from dual;--星期五
select to_char(sysdate,'dl') from dual; --2017年3月31日 星期五
select to_char(systimestamp,'ss.ff3') from dual;--到毫秒
select to_char(sysdate,'iw') from dual;--本年第几周 13
select to_char(sysdate,'q')from dual;--本年第几季度
--接收数字
select to_char(1123.3,'fm9990.0099') from dual;--1123.30

正则表达式

--正则表达式
--1 正则表达式子串
--返回以逗号开始和结束，中间不包含逗号的子串，返回该子串
select regexp_substr('500 Oracle Parkway,Redwood Shores , CA',',[^,]+,')"regexpr_sunstr" from dual;--,Redwood Shores ,
--2 正则表达式查找
--从第一个字符查找不包含空格的字符串，返回第6个匹配的字符串的第一个字母的位置
select regexp_instr('500 Oracle Parkway, Redwood Shores, MDZZ','[^ ]+',1,6) "REGEXP_INSTR" from dual; --37
--3 count语句增强版
--找出以s开头后跟三个字母的子串，返回匹配的个数
select regexp_count('But,soft! What light through yonder window softly breaks?','s[[:alpha:]]{3}')as result from dual;--2
--4 正则表达式替换字符
--下面代码查找模式为xxx.xxx.xxxx的号码，函数把它格式化为模式(xxx )xxx-xxxx.
select regexp_replace('010.227.8888',
'([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})',
'(\1) \2-\3') "REGEXP_REPLACE" from dual;--(010) 227-8888

其它：

NVL函数

NVL(exps,0)  ：如果exps为null则返回0.