Oracle常用单行函数（原创）

 

 前言：

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

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

字符函数（7个），数值函数（7个），日期函数（7个），转换函数（3个），通用函数（9个），下面开始分析。

一、字符函数：

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

Example:

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

 结果如图：

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

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

 

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

Example:

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

结果如图：

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

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

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

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

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

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

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

 

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

Expression:(str_father,str_son,index,times)

 Example:

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

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

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

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

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

 

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

Example:

select length('milktea') from dual

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

Example:

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

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

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

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

Example:

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

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

Example:

select ltrim('  milktea  leaf  ') from dual;

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

select rtrim('  milktea  leaf  ') from dual;

select trim('  milktea  leaf  ') from dual;

 

二、数值函数：

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

Example：

select round(13.14,1) from dual;

select round(13.14,-1) from dual;

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

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

Example：

select trunc(15.15,1) from dual;

select trunc(15.15,-1) from dual;

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

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

Example:

select mod(5,2) from dual;

select mod(3,0) from dual;

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

Example:

select ceil(13.14) from dual;

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

Example:

select floor(13.14) from dual;

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

Example:

select abs(-1314) from dual;

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

Example:

 

select cos(0) from dual;

三、日期函数：

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

Example:

select sysdate from dual;

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

Example:

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

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

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

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

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

Example:

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

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

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

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

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

Example:

select add_months(sysdate,3) from dual;

select add_months(sysdate,-3) from dual;

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;

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

Example:

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

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

Example:

select last_day(sysdate) from dual;

四、转换函数：

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;

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

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

（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;

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

Example:

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

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

Example:

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

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

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

 

五、通用函数：

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

 

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

Example:

select nvl(comm,000) from emp;

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

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

Example:

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

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

Example:

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

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

Example:

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

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

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;

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

Example:

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

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

Example:

select ascii('a') from dual;

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

Example:

select chr(97) from dual;

 

 

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

 2018-08-04  17:20:42