Oracle数据库开发利器之函数

函数的作用:
方便数据的统计
处理查询结果

函数的分类：数值函数、字符函数、日期函数、转换函数

数值函数

四舍五入
取整函数
常用计算
三角函数

四舍五入：

• ROUND(n[,M])
• 省略m : 0
• m>0:小数点后m位

• m<0:小数点前m位
  n表示要进行四五入的值
  m表示保留小数点后几位或前几位

  select round(23.4),round(23.45,1),round(23.45,-1) from dual;

取整函数：

• double floor(double x);
• double ceil(double x);
• 使用floor函数。floor(x)返回的是小于或等于x的最大整数。
  如：floor(10.5) == 10 floor(-10.5) == -11
• 使用ceil函数。ceil(x)返回的是大于x的最小整数。
  如：ceil(10.5) == 11 ceil(-10.5) ==-10
  floor()是向负无穷大舍入，floor(-10.5) == -11；
  ceil()是向正无穷大舍入，ceil(-10.5) == -10

常用计算：

• ABS(n)
• MOD(m,n)//m除以n的余数，如果m和n中有一个值为null值，则结果返回null值
• POWER(m,n)//表示返回m的n次幂,2³用POWER(2,3)表示

• SQRT(n)//16的平方根表示为sqrt（16），结果是4

  select ABS(-7) from dual; //返回绝对值，返回7
  select mod(5,3) from dual; //返回2
  select power(2,3) from dual;//返回8
  select sqrt(16) from dual;//返回4

三角函数：

• SIN(x)
  【功能】返回一个数字的正弦值
  【示例】select sin(1.57079) from dual;
  返回： 1

• SIGH(x)
  【功能】返回双曲正弦的值
  【示例】select sin(20),sinh(20) from dual;
  返回：0.91294525, 242582598

• COS(x)
  【功能】返回一个给定数字的余弦
  【示例】select cos(-3.1415927) from dual;
  返回： -1

• COSH(x)
  【功能】返回一个数字反余弦值
  【示例】select cosh(20) from dual;
  返回：242582598

• TAN
  【功能返回数字的正切值
  【示例】select tan(20),tan(10) from dual;
  返回：2.2371609 ,0.64836083

• TANH
  【功能返回数字n的双曲正切值
  【示例】select tanh(20),tan(20) from dual;
  返回：1 ,2.2371609

• ASIN(x)
  【功能】给出反正弦的值
  【示例】select asin(0.5) from dual;
  返回：0.52359878

• ACOS(x)
  【功能】给出反余弦的值
  【示例】select acos(-1) from dual;
  返回：3.1415927

• ATAN(x)
  【功能】返回一个数字的反正切值
  【示例】 select atan(1) from dual;
  返回：0.78539816

字符函数

大小写转换函数
获取子字符串函数
获取字符串长度函数
字符串连接函数
去除子串函数
替换函数

大小写转换函数：

• UPPER(char)//返回所有大写
• LOWER(char)//返回所有小写
• INITCAP(char)//返回首字母大写，其余都小写
• 大小写转换函数的用途：注册用户名

select upper(‘aBde’),lower(‘ASSsSS’),initcap(‘aBbB’) from dual;

获取子字符串函数：

• SUBSTR(char,[m[,n]])
• char代表源字符串，m代表取子串的开始位置，n代表截取子串的位数
• 当n省略时代表从m的位置截取到字串末尾
• m为0，代表从字符串的首字母开始截取
• m为负数，表示从字符串的尾部开始截取
  select substr(‘abcd’,2,3) from dual; //返回bcd

获取字符串长度函数：

• LENGTH(char)
• 空格也算一个字符
  select LENGTH(‘abcd’) from tmp;//返回4

字符串连接函数：

• CONCAT(char1,char2)
• 与||操作符的作用一样
  select concat(‘ab’,’cd’) from dual; //返回abcd
  select ‘ab’||’cd’ from dual;//返回abcd

去除子串函数

• TRIM(c2 FROM c1)
• 该函数表示从字符串c1中去除字符c2

• select trim(‘d’ from ‘abcd’) from dual;//返回abc

• LTRIM(c1[,c2])

• 从左边开始获取
• 也可以去除字符串前面的空格，格式为LTRIM(n)

• select ltrim(‘abcde’,’a’) from dual;//返回bcde

• RTRIM(c1[,c2])

• 从右边开始获取
• 也可以去除字符串后面的空格

• select rtrim(‘abcde’,’e’) from dual;//返回abcd

• TRIM(c1)

• 去除字符串前后的空格

替换函数：

• REPLACE(char,s_string[,r_string])
• 省略r_string用空格替换
  select replace(‘abcde’,’a’,’A’) from dual;//返回Abcde
  select replace(‘abcde’,’abcd’,’A’) from dual;//返回Ae

日期函数

系统时间
日期操作

系统时间：

• SYSDATE
• 默认格式：DD-MON-RR 日-月-年

日期操作：

• ADD_MONTHS(date,i)
• 该函数的作用是返回指定日期上添加的月份
• 如果i是小数，则截取整数部分

• 如果i是负数，相当于为原日期减去月份
  select add_months(sysdate,3),add_months(sysdate,-1) from dual;

• NEXT_DAY(date,char)

• 如果char的值是‘星期一’，则返回date指定日期的下周一是哪天
  select next_day(sysdate,’星期一’) from dual;

• LAST_DAY(date)

• 返回日期所在月的最后一天
  select last_day(sysdate) from dual;

• MONTHS_BETWEEN(date1,date2)

• 表示两个日期之间相隔的月份
  select months_between(‘20-5月-15’,’10-3月-15’) from dual;

• EXTRACT(date FROM datetime)

• //语法如下：
  EXTRACT (
  { YEAR | MONTH | DAY | HOUR | MINUTE | SECOND }
  | { TIMEZONE_HOUR | TIMEZONE_MINUTE }
  | { TIMEZONE_REGION | TIMEZONE_ABBR }
  FROM { date_value | interval_value } )
• 用于从一个date或者interval类型中截取到特定的部分

select EXTRACT(year from sysdate),EXTRACT(month from sysdate),EXTRACT(day from sysdate) from dual;//返回年月日

selectEXTRACT(HOURfromtimestamp’2015101717:55:13’),EXTRACT(minute from timestamp ‘2015-10-17 17:55:13’),EXTRACT(second from timestamp ‘2015-10-17 17:55:13’) from dual;//返回时分秒

转换函数

日期转换成字符的函数
字符转换成日期的函数
数字转换成字符的函数
字符转换成数字的函数

日期转换成字符的函数 ：

• TO_CHAR(date[,fmt[,params]])
• 参数说明：date,将要转换的日期；fmt：转换的格式；params：日期的语言
• 默认格式：DD-MON-RR
• fmt的格式可以如下定义：

转换的格式	其含义
YY YYYY YEAR	两位数的年；四位数的年；英文表示的年
MM MONTHS	月；英文表示的月
DD DAY	天；中文表示的星期几
HH24 HH12	24小时制；12小时制
MI SS	分；秒

select to_char(sysdate, ‘YYYY-MM-DD HH24:MI:SS’) from dual;

字符转换成日期的函数 ：

• TO_DATE(char[,fmt[,params]])
• params：用于指定日期的语言
• 注意：to_date()按照系统默认格式显示日期
  select to_date(‘2015-05-22’,’YYYY-MM-DD’) from dual;

数字转换成字符的函数 ：

• TO_CHAR(number[,fmt])
• 9：显示数字并忽略前面的0
• 0：显示数字，位数不足，用0补齐
• . 或 D：显示小数点
• , 或 G：显示千位符
• $：美元符号
• S：加正负号(前后都可以)
  select to_char(123456.789, ‘S$99,9999.999’) from dual;//返回+12,3456.789

字符转换成数字的函数：

• TO_NUMBER(char[,fmt])
• fmt是转换的格式，可以省略
  select to_number(‘$1,000','$9999’) from dual;//返回1000

在查询中使用函数

在查询中使用字符函数
在查询中使用数值函数
在查询中使用日期函数

在查询中使用字符函数

• 在员工信息表查询出员工的生日
  select substr(a.cardid,7,8) from ttuser a

• 将部门号01全部替换成”信息技术”
  select replace(detno,’01’,’信息技术’) from ttuser

在查询中使用数值函数

• 将员工信息表中的年龄字段与10取余数
  select mod(a.age,10) from ttuser a

在查询中使用日期函数

• 取得员工入职的年份
  select EXTRACT(year from regdate) from ttuser

• 查询出5月份入职的员工信息
  select * from ttuser where extract(month from regdate) = 5 ;

总结 ：

函数名称	语法（关键字）
数值函数	四舍五入：ROUND(n[,m]) 取整：FLOOR(n) 绝对值：ABS(n) 取余数：MOD(m,n) 平方根：SORT(n) 三角函数：SIN(n)、ASIN(n)等
字符函数	大小写转换：UPPER(char)、LOWER(char) 字符串连接：CONCAT(char1,char2) 获取子字符串：SUBSTR(char,[m,[,n]]) 获取字符串长度：LENGTH(char)
日期函数	获取当前时间函数SYSDATE ADD_MONTHS(date,i) LAST_DAY(date) MONTHS_BETWEEN(date1,date2) EXTRACT(date FROM datetime)
转换函数	TO_CHAR(date[,fmt[,params]]) TO_CHAR(number[,fmt])