.Net程序员学用Oracle系列(10)：系统函数（中）
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
版权声明：本文为博客园博主 韩宗泽 原创，作者保留署名权！欢迎通过转载、演绎或其它传播方式来使用本文，但必须在明显位置给出作者署名和本文链接！本人初写博客，水平有限，若有不当之处，敬请批评指正，谢谢！
