欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Oracle常用单行函数(原创)

程序员文章站 2022-03-24 22:40:35
前言: 想把单行函数进行一个比较全面的总结,并分享给有需要的人,有不明之处还请多多指教。 SQL函数:Oracle的内置函数,包括了单行函数和多行函数,本文重点讲解单行函数。单行函数又可以分为许多类,本人将常用的分为5大类: 字符函数(7个),数值函数(7个),日期函数(7个),转换函数(3个),通 ......

 

 前言:

想把单行函数进行一个比较全面的总结,并分享给有需要的人,有不明之处还请多多指教。

SQL函数:Oracle的内置函数,包括了单行函数和多行函数,本文重点讲解单行函数。单行函数又可以分为许多类,本人将常用的分为5大类:

字符函数(7个),数值函数(7个),日期函数(7个),转换函数(3个),通用函数(9个),下面开始分析。

一、字符函数:

1.concat函数:用来拼接2个字符串,Expression:concat(str1,str2);

Example:

select concat('milktea','leaf') from dual;

 结果如图:

Oracle常用单行函数(原创)

 注意:concat函数不能处理2个以上的字符串拼接,否则会报参数个数无效的错误。

select concat('milktea','leaf','third') from dual;

Oracle常用单行函数(原创)

 

2、substr函数:用于截取字符串,从指定位置index截取指定长度length的字符串。Expression:substr(str,index,length);

Example:

select substr('milktealeaf',5,3) from dual;

结果如图:

Oracle常用单行函数(原创)

注意:此处字符串的下标从1开始,index的值是几就是第几个。

另外还有一个substrb函数,和substr函数作用相同,但2者稍有差别:substr按字符取,而substrb按字节取。结论显而易见:

在取英文字母时,2者相同,但在取汉字时,一个汉字2个字节,如果是奇数个字节则自动舍弃最后一位,示例如下:

select substr('milktealeaf',2,5) from dual;

Oracle常用单行函数(原创)

select substrb('milktealeaf',2,5) from dual;

Oracle常用单行函数(原创)

select substr('李先森陈小姐你们好呀',2,5) from dual;

Oracle常用单行函数(原创)

select substrb('李先森陈小姐你们好呀',2,5) from dual;

Oracle常用单行函数(原创)

 

 3、Instr函数:用于获取一个字符串中子串str_son在字符串str_father中从index下标查找第times次出现的位置,返回一个下标

Expression:(str_father,str_son,index,times)

 Example:

select instr('milkteatealeaftea','tea',3,2) from dual;

Oracle常用单行函数(原创)

注:如图所示。第二个tea在下标为8处。

另外,也有一个instrb函数,同substr和substrb函数一样,instr用于字符,instrb用于字节,示例如下:

select instr('李先森陈女士李先森李先森陈女士','李先森',3,2) from dual;

Oracle常用单行函数(原创)

select instrb('李先森陈女士李先森李先森陈女士','李先森',3,2) from dual;

 

Oracle常用单行函数(原创)

4、length函数:返回一个字符串的长度 Expression:length(str);

Example:

select length('milktea') from dual

Oracle常用单行函数(原创)

5、lpad函数:用于对字符串左侧进行填充的函数,l即为left,Expression:lpad(str1,length,str2)其中str1为被填充的字符串,length为填充以后的长度,str2为要填充的字符。

Example:

select lpad('leaf',9,'%*') from dual;

Oracle常用单行函数(原创)

注:从左侧开始填充,只要长度达到立即停止,不管要填充的字符串有没有填充完整。有了左填充,必然有右填充rpad,r即为right,从字符串右侧进行填充。示例如下:

select rpad('leaf',9,'%*') from dual;

Oracle常用单行函数(原创)

6、replace函数:顾名思义,替换函数,将字符串中的子串用其他字符进行替换。Expression:replace(str_father,str_son,str_replace);

Example:

select replace('milkteathird','third','leaf') from dual;

Oracle常用单行函数(原创)

7、ltrim函数:去掉字符串左侧的空白。 Expression:ltrim(str)

Example:

select ltrim('  milktea  leaf  ') from dual;

Oracle常用单行函数(原创)

可以看到左侧空白已经去除,那么同样的有rtrim函数,去掉右侧空白。去空白,那么当然左右2侧都去也可以,trim函数。示例如下:

select rtrim('  milktea  leaf  ') from dual;

Oracle常用单行函数(原创)

select trim('  milktea  leaf  ') from dual;

Oracle常用单行函数(原创)

 

二、数值函数:

1、round函数:用于对一个数取四舍五入后的结果。Expression(number,m)    number为数字,m为要四舍五入的位数,必须为整数。

Example:

select round(13.14,1) from dual;

Oracle常用单行函数(原创)

select round(13.14,-1) from dual;

Oracle常用单行函数(原创)

可以看出,当位数为正数时,保存小数点后面的位数,负数时直接取整数部分的四舍五入。

2、trunc函数:直接进行截取数字,不进行四舍五入。Expression: trunc(number,m)number为截取的数字,m为截取的位数。

Example:

select trunc(15.15,1) from dual;

Oracle常用单行函数(原创)

select trunc(15.15,-1) from dual;

Oracle常用单行函数(原创)

并没有进行四舍五入,直接截取,毫不留情。

3、mod函数:对2个数进行取模运算,如果除数为0 ,则返回被除数。Expression:mod(number1,number2)

Example:

select mod(5,2) from dual;

Oracle常用单行函数(原创)

select mod(3,0) from dual;

Oracle常用单行函数(原创)

4、ceil函数:用于返回大于等于当前数的最小整数 Expression:ceil(number)

Example:

select ceil(13.14) from dual;

Oracle常用单行函数(原创)

5、floor函数:和ceil函数相反,用于返回小于等于当前数的最大整数 Expression:floor(number)

Example:

select floor(13.14) from dual;

Oracle常用单行函数(原创)

6、abs函数:返回当前数的绝对值:Expression:abs(number)

Example:

select abs(-1314) from dual;

Oracle常用单行函数(原创)

7、cos函数:返回当前数的余弦值  Expression:cos(number)

Example:

 

select cos(0) from dual;

Oracle常用单行函数(原创)

三、日期函数:

1、sysdate函数:返回系统当前日期 Expression :sysdate

Example:

select sysdate from dual;

Oracle常用单行函数(原创)

2、round函数:对日期取四舍五入的结果,如果精度是日的话以每周周四为标准,精度是月以每月16日为标准,精度是年以每年7月1日为标准。   分别四舍五入到最近的周日,每月1号,以及每年1月1日,  Expression:round(date,'format')

Example:

select round(sysdate,'day') from dual;

Oracle常用单行函数(原创)

select round(sysdate,'month') from dual;

Oracle常用单行函数(原创)

select round(sysdate,'year') from dual;

Oracle常用单行函数(原创)

Oracle常用单行函数(原创)

可以看到现在的时间过了周三,取最近的周日8月5日,按月份还没过16日,取每月1日,按年份已经过了7月1日,取下一年的1月1日。

3、trunc函数:截取日期,精度为日,取当前星期的第一天(按第一天是周日算),精度为月,按每月第一日算,精度为年,按每年1月1日算。Expression:trunc(date,'format')

Example:

select trunc(sysdate,'day') from dual;

Oracle常用单行函数(原创)

select trunc(sysdate,'month') from dual;

Oracle常用单行函数(原创)

select trunc(sysdate,'year') from dual;

Oracle常用单行函数(原创)

今天周六,取上周日7月29,按月份取本月1日,按年份取今年1月1日。

4、add_months函数:在日期的基础上加减整月后的日期,Expression:add_months(date,number)其中date为日期,number为整数。

Example:

select add_months(sysdate,3) from dual;

Oracle常用单行函数(原创)

select add_months(sysdate,-3) from dual;

Oracle常用单行函数(原创)

5、months_between函数:返回2个日期之间的月份差数,参数前者大于后者返回正数,前者小于后者返回负数。Expression:months_between(date1,date2);

Example:

select months_between(to_date('2018-8-4','YYYY-MM-DD'),to_date('1949-10-1','YYYY-MM-DD')) from dual;

Oracle常用单行函数(原创)

6、next_day函数:用于返回特定日期后的特定某一天(比如工作日)Expression:next_day(date,str)其中str对应特定某一天,必须与日期语言匹配,如果语言是America,那么周一为Monday,语言是简体中文,对应星期一。

Example:

select next_day(sysdate,'星期一') from dual;

Oracle常用单行函数(原创)

7、last_day函数:用于取特定日期的当月最后一天。Expression:last_day(date)

Example:

select last_day(sysdate) from dual;

Oracle常用单行函数(原创)

四、转换函数:

1、to_char函数:分为2类

(1)日期转字符:Expression: to_char(date,format,nlsparams)其中nlsparams指定日期的显示语言,格式为:'NLS_DATE_LANGUAGE=LANGUAGE',也可以不要。

Example:

select to_char(sysdate,'DD-MON-RR','NLS_DATE_LANGUAGE=AMERICAN') from dual;

Oracle常用单行函数(原创)

select to_char(sysdate,'YYYY"年"MM"月"DD"日"') from dual;

Oracle常用单行函数(原创)

注意:如果格式中带有字符时,须将字符用双引号引起来。

(2)数值转字符:Expression:    to_char(number,format)

此处格式常用有:1) 9:显示数字,忽略前导0;2)0:显示数字,位数不足用0补齐;3).:小数点; 4),:逗号 5)$:显示美元符号; 6)L:本地货币符号

Example:

select to_char(1314520,'L99,999,99.99') from dual;

Oracle常用单行函数(原创)

2、to_date函数:用于将字符串转化为日期类型 Expression:  to_date(str,format,nlsparams)其中nlsparams可不要

Example:

select to_date('1999-02-17','YYYY-MM-DD') from dual;

Oracle常用单行函数(原创)

3、to_number函数:用于将含有数字的字符串转化为数值类型:Expression:   to_number(str,format)

Example:

select to_number('$1314520','$999999999.99') from dual;

Oracle常用单行函数(原创)

注意:此处format的作用限制了数的范围,当format里面的格式如果比字符串中的数字小的话,会报错,示例如下:

select to_number('$1314520','$99999.99') from dual;

 

Oracle常用单行函数(原创)

五、通用函数:

下面实例多用到Oracle中scott用户下的emp表,数据如下:

Oracle常用单行函数(原创)

 

1、nvl函数:解决值为null的问题,当第一个参数为空时,返回第二个参数的值 Expression: nvl(expr1,expr2)

Example:

select nvl(comm,000) from emp;

Oracle常用单行函数(原创)

注意表达式2中值的类型须和原来字段的类型相同。

2、nvl2函数:如果参数列表中,第一个表达式的值不为空,则返回第二个表达式的值,如果为空,则返回第三个表达式的值。Expression: nvl2(expr1,expr2,expr3)

Example:

select nvl2(comm,comm,000) from emp;

Oracle常用单行函数(原创)

3、nullif函数:用于判断2个表达式的值是否相等,相等返回null,不等返回第一个表达式的值。Expression: nullif(expr1,expr2);

Example:

select nullif(job,'MANAGER') from emp;

Oracle常用单行函数(原创)

4、coalesce函数:用于返回表达式中第一个not null的结果    Expression:coalesce(expr1,expr2,expr2,expr...)

Example:

select coalesce(comm,null,0) from emp;

Oracle常用单行函数(原创)

5、case表达式:根据取值不同返回不同的结果。 Expression:

case expr
when value1 return result1
when value2 return result2
when value3 return result3
when ......
else result
end 

Example:

select ename,
case job
when 'CLERK' then '办事员'
when 'SALESMAN' then '销售'
when 'MANAGER' then '经理'
when 'ANALYST' then '分析员'
else '总裁'
end
from emp

Oracle常用单行函数(原创)

6、decode函数:根据取值不同返回不同的结果。和case表达式的作用相同,Expression: decode(expr,'value1','result1','value2','result2',...,result)

当expr的值为value1是返回结果result1,值为value2时返回结果result2,如果都没有就返回默认值result;

Example:

select ename,decode(job,'CLERK','办事员','SALESMAN','销售','MANAGER','经理','ANALYST','分析员','总裁')from emp;

Oracle常用单行函数(原创)

7、sign函数:判断当前数字的符号,大于0返回1,等于0返回0,小于0返回-1   Expression:  sign(number)

Example:

select sign(13),sign(0),sign(-14) from dual;

Oracle常用单行函数(原创)

8、ascii函数:用于返回当前字符对应的ascii码表中的位置   Expression:   ascii(str)

Example:

select ascii('a') from dual;

Oracle常用单行函数(原创)

9、chr函数:和ascii函数刚好相反,返回ascii码表中的字符   Expression:   chr(number)

Example:

select chr(97) from dual;

Oracle常用单行函数(原创)

 

 

到此为止,oracle的单行函数已全部总结完毕,有需要的童鞋可以进来看看,后续还会总结其他,等待up主更新~

 2018-08-04  17:20:42