.Net程序员学用Oracle系列(10):系统函数(中)

时间:2022-07-29 17:35:02

1、四大转换函数

Oracle 中的转换函数共计超过 20 个,但一大半都是做字节转换、日期(包括时间和时区)转换、LOB 类型转换、ROWID 类型转换等非常见转换的,光日期转换函数就占了 1/3。剩下几个函数当中有 4 个是比较实用也比较常用的,也就是三大“TO 函数”及 CAST 函数。函数名及语法如下:

序号 函数名 语法原型 常用
1 TO_CHAR TO_CHAR( value [, format_mask] [, nls_language] )
2 TO_NUMBER TO_NUMBER( string1 [, format_mask] [, nls_language] )
3 TO_DATE TO_DATE( string1 [, format_mask] [, nls_language] )
4 CAST CAST( { expr | ( subquery ) | MULTISET ( subquery ) } AS type_name ) ×

1.1、TO_CHAR

TO_CHAR:即可作为数字字符函数也可作为日期字符函数,换句话说 value 可以是数字也可以是日期,无论何种情况,format 参数都有很多格式可选,nls language 一般不必考虑。

  • 用途一:TO_CHAR 作为数字字符函数时,format 参数有数几十种格式可选,这里主要介绍常见和常用的格式。

format 参数中的 TM:使用 TM 格式与不指定任何格式的效果一样。TM 格式有 TM9 和 TME 两种,默认是 TM9,当数字长度超过 64 位时候,TM9 的输出等同于 TME 的输出。示例:

SELECT TO_CHAR(3888) res FROM DUAL;       -- res:'3888'
SELECT TO_CHAR(3888,'TM9') res FROM DUAL; -- res:'3888'
SELECT TO_CHAR(8844.43) res FROM DUAL; -- res:'8844.43'
SELECT TO_CHAR(8844.43,'TM9') res FROM DUAL; -- res:'8844.43'

format 参数中的点号:出现在小数点对应的位置,能且只能出现一次。示例:

SELECT TO_CHAR(8844.43,'9999.99') res FROM DUAL; -- res:' 8844.43'
SELECT TO_CHAR(8844.43,'9999.9') res FROM DUAL; -- res:' 8844.4'

format 参数中的逗号:一般以千分位出现,如果需要也可以当作百分位、十分位,总之可出现 0 或多次。示例:

SELECT TO_CHAR(3888,'9,999') res FROM DUAL;   -- res:' 3,888'
SELECT TO_CHAR(3888,'9,9,9,9') res FROM DUAL; -- res:' 3,8,8,8'

format 参数中的 0:数字占位符,在 0 出现的位置,如果有数字则返回数字,如果没有数字则返回 0。示例:

SELECT TO_CHAR(3888,'00000') res FROM DUAL;  -- res:' 03888'
SELECT TO_CHAR(3888,'0000.0') res FROM DUAL; -- res:' 3888.0'

format 参数中的 9:数字占位符,当 9 出现在整数位时,如果有数字则返回数字,如果没有数字则返回空格,当 9 出现在小数位时,与 0 的作用相同。只要 format 中整数部分的 0 和 9 的总数没有 value 整数部分位数多,就会返回 #。示例:

SELECT TO_CHAR(3888,'99999') res FROM DUAL;  -- res:'  3888'
SELECT TO_CHAR(3888,'9999.9') res FROM DUAL; -- res:' 3888.0'
SELECT TO_CHAR(3888,'000') res FROM DUAL; -- res:'####'
SELECT TO_CHAR(3888,'999') res FROM DUAL; -- res:'####'

format 参数中的 $:美元符号,可出现在格式中任何位置,但最多只能出现一次,会在数字的返回值前加一个 $ 符号。示例:

SELECT TO_CHAR(3888,'$0000') res FROM DUAL; -- res:' $3888'

format 参数中的 B:表示空格,可出现在格式中任何位置,但最多只能出现一次,会在正数的返回值前加一个空格。本人感觉这个格式没有任何实际用处,但当 value 为正数时,几乎所有格式的返回值前面都会有一个空格,不知道是不是 Oracle 内部默认已经应用了 B 格式。示例:

SELECT TO_CHAR(3888,'B0000') res FROM DUAL;  -- res:' 3888'
SELECT TO_CHAR(-3888,'B0000') res FROM DUAL; -- res:'-3888'

format 参数中的 S:数字符号,可出现在格式开头或末尾,至多出现一次,会在 S 出现的位置给正数加一个 +,给负数加一个 -。示例:

SELECT TO_CHAR(3888,'S0000') res FROM DUAL;  -- res:'+3888'
SELECT TO_CHAR(3888,'0000S') res FROM DUAL; -- res:'3888+'
SELECT TO_CHAR(-3888,'S0000') res FROM DUAL; -- res:'-3888'
SELECT TO_CHAR(-3888,'0000S') res FROM DUAL; -- res:'3888-'

format 参数中的 X:表示将 value 转换为 16 进制,且 value 必须是大于或等于 0 的整数。X 前面只能和 0 或 FM 组合使用。示例:

SELECT TO_CHAR(100,'XX') res FROM DUAL; -- res:' 64'

format 参数中的 FM:在格式前面再加上 FM,表示去掉返回值前面的空格。示例:

SELECT TO_CHAR(3888,'9999') res FROM DUAL;   -- res:' 3888'
SELECT TO_CHAR(3888,'FM9999') res FROM DUAL; -- res:'3888'

纯小数转换为字符串,如果能确定是纯小数,整数部分应该用 0 而不是 9,否则转换后会把小数点前面的 0 去掉,示例:

SELECT TO_CHAR(0.5,'FM9.9') res FROM DUAL; -- res:'.5'
SELECT TO_CHAR(0.5,'FM0.9') res FROM DUAL; -- res:'0.5'
  • 用途二:TO_CHAR 作为日期字符函数是,format 参数的可选格式比数值的更多,因此这里也主要介绍常见及常用的格式。

format 参数中的分隔符:除常见时间分隔符 -、:、/、,、.、; 以外,还可以使用自定义字符,如汉字,但需要用双引号包裹起来。示例:

SELECT TO_CHAR(fn_now,'yyyy-mm-dd') res FROM DUAL;            -- res:'2017-01-10'
SELECT TO_CHAR(fn_now,'yyyy-mm-dd hh24:mi') res FROM DUAL; -- res:'2017-01-10 19:21'
SELECT TO_CHAR(fn_now,'yyyy/mm,dd hh24.mi;ss') res FROM DUAL; -- res:'2017/01,10 19.21;30'
SELECT TO_CHAR(fn_now,'yyyy"年"mm"月"dd"日"') res FROM DUAL; -- res:'2017年01月10日'

format 参数中的 Y:yyyy 表示 4 位的年份;yyy 表示年份的后三位;yy 表示年份的后两位;y 表示年份的后一位。示例:

SELECT TO_CHAR(fn_now,'yyyy') res FROM DUAL; -- res:'2017'
SELECT TO_CHAR(fn_now,'yyy') res FROM DUAL; -- res:'017'
SELECT TO_CHAR(fn_now,'yy') res FROM DUAL; -- res:'17'
SELECT TO_CHAR(fn_now,'y') res FROM DUAL; -- res:'7'

format 参数中的 M:mm 表示 2 位的月份(01~12);mi 表示分钟(0~59);month 表示月的名称;mon 月的前三个字母缩写。示例:

SELECT TO_CHAR(fn_now,'mm') res FROM DUAL;    -- res:'01'
SELECT TO_CHAR(fn_now,'mi') res FROM DUAL; -- res:'21'
SELECT TO_CHAR(fn_now,'month') res FROM DUAL; -- res:'january '/'1月 '
SELECT TO_CHAR(fn_now,'mon') res FROM DUAL; -- res:'jan'/'1月 '

format 参数中的 D:单个 d 表示一周中第几天,返回 1~7;day 表示一周中的某天,返回星期几,返回值与 nls 有关;dy 表示周前三个字母缩写;dd 表示月份中的某天,返回 1~31;ddd 表示一年中的某天,返回 1~366。示例:

SELECT TO_CHAR(fn_now,'d') res FROM DUAL;   -- res:'2',在西方周日每周的第一天
SELECT TO_CHAR(fn_now,'day') res FROM DUAL; -- res:'tuesday '/'星期二'
SELECT TO_CHAR(fn_now,'dy') res FROM DUAL; -- res:'tue'/'星期二'
SELECT TO_CHAR(fn_now,'dd') res FROM DUAL; -- res:'10'
SELECT TO_CHAR(fn_now,'ddd') res FROM DUAL; -- res:'010'

format 参数中的 H:hh24 表示 24 小时制的小时数;hh12 表示 12 小时制的小时数;hh 是 hh12 的简写形式。示例示例

SELECT TO_CHAR(fn_now,'hh24') res FROM DUAL; -- res:'19'
SELECT TO_CHAR(fn_now,'hh12') res FROM DUAL; -- res:'07'
SELECT TO_CHAR(fn_now,'hh') res FROM DUAL; -- res:'07'

format 参数中的 S:ss 表示秒(0~59);sssss 表示一天从午夜开始累积的秒数(0~86399)。示例:

SELECT TO_CHAR(fn_now,'ss') res FROM DUAL;    -- res:'30'
SELECT TO_CHAR(fn_now,'sssss') res FROM DUAL; -- res:'69690'

format 参数其它的一些不常用格式,都比较简单,示例:

SELECT TO_CHAR(fn_now,'cc') res FROM DUAL; -- res:'21',世纪
SELECT TO_CHAR(fn_now,'q') res FROM DUAL; -- res:'1',年的季度
SELECT TO_CHAR(fn_now,'iw') res FROM DUAL; -- res:'02',年的周数(ISO 标准)
SELECT TO_CHAR(fn_now,'ww') res FROM DUAL; -- res:'02',年的周数
SELECT TO_CHAR(fn_now,'w') res FROM DUAL; -- res:'2',月的周数
SELECT TO_CHAR(fn_now,'am') res FROM DUAL; -- res:'pm',等同于 PM、A.M.和P.M.
SELECT TO_CHAR(fn_now,'ts') res FROM DUAL; -- res:'PM 7:21:30',短日期
SELECT TO_CHAR(fn_now,'rm') res FROM DUAL; -- res:'i ',罗马数字表示的月份

1.2、TO_NUMBER

TO_NUMBER:主要用途就是把字符串形式的数字转换为数字,参数格式与 TO_CHAR 作为数字字符函数的参数格式基本相同,nls 一般也不用考虑。示例:

SELECT TO_NUMBER('8844.43','9999.99') res FROM DUAL; -- res:'8844.43'
SELECT TO_NUMBER('8844.43') res FROM DUAL; -- res:'8844.43'
SELECT TO_NUMBER('3888','9999') res FROM DUAL; -- res:'3888'
SELECT TO_NUMBER('3888') res FROM DUAL; -- res:'3888'

三大 TO 函数 中,TO_NUMBER 用的相对较少,且格式要求非常严格,即便是正确的格式,哪怕是格式长度小于实际数值长度 invalid number,遇到非数值就更加会出错了。通过上面 4 个示例也很容易发现——貌似指不指定格式都没啥区别。但如果是历史数据掺杂了少量字符,也可以通过替换非数字字符的方式来进一步处理,示例:

SELECT TO_NUMBER(REGEXP_REPLACE('8844.43m','[^0-9.]','')) res FROM DUAL; -- res:8844.43

1.3、TO_DATE

TO_DATE:主要用途就是把字符串形式的日期转换为日期,参数格式 TO_CHAR 做为日期字符函数的参数格式基本相同,nls 一般也不用考虑。示例:

SELECT TO_DATE('2017/01/10','yyyy-mm-dd') res FROM DUAL; -- res:'2017-01-10'
SELECT TO_DATE('20170110','yyyymmdd') res FROM DUAL; -- res:'2017-01-10'
SELECT TO_DATE('2017-01','yyyy-mm') res FROM DUAL; -- res:'2017-01-01'
SELECT TO_DATE('201701','yyyymm') res FROM DUAL; -- res:'2017-01-01'

如果要转换的日期字符串长度不一致,常会遇到这个错误:ORA-01830: data format picture ends before converting entire input string,即“日期格式图片在转换整个输入字符串之前结束”,以下三条语句都会报这个错:

SELECT TO_DATE('2017-01-10 23:15:12','yyyy-mm-dd hh24:mi') FROM DUAL;
SELECT TO_DATE('2017-01-10 23:15','yyyy-mm-dd hh24') FROM DUAL;
SELECT TO_DATE('2017-01-10 23','yyyy-mm-dd') FROM DUAL;

我分析应该是由于实际日期比格式化串长,导致了类似“溢出”的问题。

1.4、CAST

CAST:简单来说就是把一种数据类型转换为另一种数据类型,参数包含原值和目标转换类型两部分,其中原值可以表达式也可以是子查询,还可以是个集合。示例:

SELECT CAST(3888 AS INT) res FROM DUAL;              -- res: 3888
SELECT CAST(0.5 AS INT) res FROM DUAL; -- res: 1
SELECT CAST(0.5 AS NUMBER) res FROM DUAL; -- res: 0.5
SELECT CAST(8844.43 AS NUMBER(6,1)) res FROM DUAL; -- res: 8844.4
SELECT CAST(fn_today AS VARCHAR2(10)) res FROM DUAL; -- res: '2017-01-10'

2、两大近似值函数

有两个函数很有意思,而且经常会用到,但对于没有系统学过 PLSQL 的人或 PLSQL 写的少的人来说,也许对这两个函数并没有太多“好感”,因为总是把名字或类型或格式搞混了。本人根据它们的用途,给它们取了一个功能类别名称,称之为近似值函数。函数名及语法如下:

序号 函数名 语法原型 常用
1 ROUND ROUND( number [, decimal_places] )
1 ROUND ROUND( date [, format] )
2 TRUNC TRUNC( number [, decimal_places] ) ×
2 TRUNC TRUNC( date [, format ] )

2.1、ROUND

处理数字时:返回指定数字四舍五入后的值。两个参数都必须是数字或数字表达式,第二个参数为正数则将数字四舍五入为指定位小数,为负数则将数字四舍五入为小数点向左指定位整数,为小数则先截取数字的整数部分再做四舍五入,第二个参数值默认为 0,表示截取 0 位小数,即四舍五入到个位。

SELECT ROUND(555.555,2) res FROM DUAL;    -- res:555.56
SELECT ROUND(555.555,4) res FROM DUAL; -- res:555.555
SELECT ROUND(555.555,-1) res FROM DUAL; -- res:560
SELECT ROUND(555.555,-1.5) res FROM DUAL; -- res:560
SELECT ROUND(555.555,1.5) res FROM DUAL; -- res:555.6
SELECT ROUND(555.555,0) res FROM DUAL; -- res:556
SELECT ROUND(555.555) res FROM DUAL; -- res:556

处理日期时:返回日期按指定区间四舍五入后得到期间的开始日期。区间的取值范围比较广,也可以不指定区间,不指定的效果相当于指定为 'dd',函数规则较多,示例难以穷尽,只介绍一些本人比较熟悉的。示例:

SELECT ROUND(fn_now) res FROM DUAL;         -- res:2017-01-11,对天四舍五入,精确到天
SELECT ROUND(fn_now,'dd') res FROM DUAL; -- res:2017-01-11,同上
SELECT ROUND(fn_now,'mm') res FROM DUAL; -- res:2017-01-01,对月四舍五入,取当月第一天
SELECT ROUND(fn_now,'month') res FROM DUAL; -- res:2017-01-01,同上
SELECT ROUND(fn_now,'yyyy') res FROM DUAL; -- res:2017-01-01,对年四舍五入,取当年第一天
SELECT ROUND(fn_now,'year') res FROM DUAL; -- res:2017-01-01,同上
SELECT ROUND(fn_now,'day') res FROM DUAL; -- res:2017-01-08,对周四舍五入,取当周周日
SELECT ROUND(fn_now,'d') res FROM DUAL; -- res:2017-01-08,同上
SELECT ROUND(fn_now,'q') res FROM DUAL; -- res:2017-01-01,对季度四舍五入,取当季度第一天
SELECT ROUND(fn_now,'hh') res FROM DUAL; -- res:2017-01-10 19:00:00,对小时四舍五入

2.2、TRUNC

处理数字时:返回数字按指定精度截取后的值。TRUNC 函数用法与 ROUND 函数用法相同,不同的是返回值可能不同,ROUND 函数按指定精度四舍五入,TRUNC 函数按指定精度直接截取。示例:

SELECT TRUNC(555.555,2) res FROM DUAL;    -- res:555.55
SELECT TRUNC(555.555,4) res FROM DUAL; -- res:555.555
SELECT TRUNC(555.555,-1) res FROM DUAL; -- res:550
SELECT TRUNC(555.555,-1.5) res FROM DUAL; -- res:550
SELECT TRUNC(555.555,1.5) res FROM DUAL; -- res:555.5
SELECT TRUNC(555.555,0) res FROM DUAL; -- res:555
SELECT TRUNC(555.555) res FROM DUAL; -- res:555

处理日期时:返回日期在指定的当前区间的开始日期。与 ROUND 用法相似,且当区间取值为 'mm'|'month'|'hh' 时,返回值与 ROUND 相同。示例:

SELECT TRUNC(fn_now) res FROM DUAL;         -- res:2017-01-10,舍去当天的时间部分
SELECT TRUNC(fn_now,'dd') res FROM DUAL; -- res:2017-01-10,同上
SELECT TRUNC(fn_now,'mm') res FROM DUAL; -- res:2017-01-01,本月第一天
SELECT TRUNC(fn_now,'month') res FROM DUAL; -- res:2017-01-01,同上
SELECT TRUNC(fn_now,'yyyy') res FROM DUAL; -- res:2017-01-01,本年第一天
SELECT TRUNC(fn_now,'year') res FROM DUAL; -- res:2017-01-01,同上
SELECT TRUNC(fn_now,'day') res FROM DUAL; -- res:2017-01-04,本周日,含当天
SELECT TRUNC(fn_now,'d') res FROM DUAL; -- res:2017-01-04,同上
SELECT TRUNC(fn_now,'q') res FROM DUAL; -- res:2017-01-01,本季度第一天
SELECT TRUNC(fn_now,'hh') res FROM DUAL; -- res:2017-01-10 19:00:00,舍去小时之后的时间

综合示例:

SELECT ADD_MONTHS(TRUNC(fn_now,'year'),12)-1 res FROM DUAL;       -- res: 2017-12-31,计算本年最后一天
SELECT CAST(TO_CHAR(LAST_DAY(fn_now),'dd') AS INT) res FROM DUAL; -- res: 31,计算本月总天数
SELECT (fn_now-TRUNC(fn_now))*24*60*60 res FROM DUAL; -- res: 69690,计算当天到目前为止已过去的秒数
SELECT (TRUNC(fn_now+1)-fn_now)*24*60*60 res FROM DUAL; -- res: 16710,计算当天到目前为止剩下的秒数

3、正则函数

3.1、正则函数简介

Oracle 对正则表达式的支持比较晚,直到 10g 才提供了 4 个正则函数,11g 中又新增了一个,由于本人使用的 10g,所以本节只简要介绍前 4 个。函数名及语法如下:

序号 函数名 语法原型 支持
1 REGEXP_INSTR REGEXP_INSTR( string, pattern [, start_position [, th_appearance [, return_option [, match_parameter [, sub_expression ] ] ] ] ] ) 10g
2 REGEXP_REPLACE REGEXP_REPLACE( string, pattern [, replacement_string [, start_position [, th_appearance [, match_parameter ] ] ] ] ) 10g
3 REGEXP_SUBSTR REGEXP_SUBSTR( string, pattern [, start_position [, th_appearance [, match_parameter [, sub_expression ] ] ] ] ) 10g
4 REGEXP_LIKE REGEXP_LIKE ( expression, pattern [, match_parameter ] ) 10g
5 REGEXP_COUNT REGEXP_COUNT( string, pattern [, start_position [, atch_parameter ] ] ) 11g

3.2、语法说明及案例

REGEXP_INSTR:与 INSTR 函数类似,它返回一个字符串中匹配一个正则表达式的第一个子串的开始位置。另外还可以指定子串出现的次数;开始搜索的位置;是返回匹配的位置还是返回匹配之后字符的位置。示例:

SELECT REGEXP_INSTR('I have a dream.','a',1,3) res FROM DUAL; -- res:13,匹配第3个 'a' 的位置
SELECT REGEXP_INSTR('I have a dream.','a') res FROM DUAL; -- res:4,匹配第1个 'a' 的位置
SELECT REGEXP_INSTR('I have a dream.','A') res FROM DUAL; -- res:0,匹配第1个 'A' 的位置
SELECT REGEXP_INSTR('I have a dream.','A',1,1,0,'i') res FROM DUAL; -- res:4,匹配第1个 'A' 的位置,并且忽略大小写

REGEXP_REPLACE:与 REPLACE 函数类似,它返回匹配一个正则表达式的子串。虽然结合使用SUBSTR 和REGEXP_INSTR 及LENGTH 也可以实现这一功能,但是使用这个函数却更为简单。示例:

SELECT REGEXP_REPLACE('Obama is the president of the USA.','Obama','Trump') res FROM DUAL; -- res:Trump is the president of the USA.
SELECT REGEXP_REPLACE('Annual income is 99,800 $.','\d','#') res FROM DUAL; -- res:Annual income is ##,### $.
SELECT REGEXP_REPLACE(' a b c ','() ','\1') res FROM DUAL; -- res:abc
SELECT REGEXP_REPLACE(' a b c ','() ','\1_') res FROM DUAL; -- res:_a_b_c_
SELECT REGEXP_REPLACE(' a b c ',' ','_') res FROM DUAL; -- res:_a_b_c_

REGEXP_SUBSTR:与 SUBSTR 函数类似,它返回初始参数被匹配子串替换之后的结果。

SELECT REGEXP_SUBSTR('In that distant place.','(\S*)') res FROM DUAL;          -- res:'In'
SELECT REGEXP_SUBSTR('In that distant place.','(\S*)(\s*)') res FROM DUAL; -- res:'In '
SELECT REGEXP_SUBSTR('In that distant place.','(\S*)(\s*)',1,2) res FROM DUAL; -- res:'that '
SELECT REGEXP_SUBSTR('Programmer','A|B|P') res FROM DUAL; -- res:'P'
SELECT REGEXP_SUBSTR('Programmer','A|B|P',1,1,'i') res FROM DUAL; -- res:'P'
SELECT REGEXP_SUBSTR('Programmer','A|B|P',1,2,'i') res FROM DUAL; -- res:'a'
SELECT REGEXP_SUBSTR('There are 10 good girls.','\d') res FROM DUAL; -- res:1
SELECT REGEXP_SUBSTR('There are 10 good girls.','\d+') res FROM DUAL; -- res:10

REGEXP_LIKE:与 LIKE 操作符相似,如果第一个参数匹配正则表达式它就解析为 TRUE。示例:

SELECT t.enum_name FROM demo.t_field_enum t WHERE REGEXP_LIKE(t.enum_name, '(研发)');
--------------------------------------
研发一部 010101
研发二部 010102
研发三部 010103

4、总结

本文主要介绍了常见的四大转换函数、两大近似值函数以及正则函数,其中转换函数特别常用,这三类函数有个共同点——参数规则繁多,难以记忆,容易出错,所以本人觉得非常有必要把它们单独拿出来总结一下。

下面是一个有点意思的特殊案例:

SELECT
TO_CHAR(TO_DATE('8848','J'),'Jsp') res, -- res: Eight Thousand Eight Hundred Forty-Eight
TO_CHAR(TO_DATE('8848','J'),'jsp') res, -- res: eight thousand eight hundred forty-eight
TO_CHAR(TO_DATE('8848','J'),'JSP') res -- res: EIGHT THOUSAND EIGHT HUNDRED FORTY-EIGHT
FROM DUAL;

本文链接http://www.cnblogs.com/hanzongze/p/oracle-systemfunction-2.html

版权声明:本文为博客园博主 韩宗泽 原创,作者保留署名权!欢迎通过转载、演绎或其它传播方式来使用本文,但必须在明显位置给出作者署名和本文链接!本人初写博客,水平有限,若有不当之处,敬请批评指正,谢谢!