.Net程序员学用Oracle系列(9)：系统函数（上）
Oracle 中系统函数特别多，有好几百个，其中大部分函数对开发者而言，似乎永远都用不到，本文将要介绍 Oracle 中常见的数据类型函数。
1、字符函数
1.1、字符函数简介
据不完全统计，Oracle 中的字符函数总计约 40 来个，主要功能包括：编解码、字符集转换、类型转换、检索、替换、连接、填充、统计等。可谓是功能强大、数量繁多，但大部分都不常用。本节只简要介绍我本人认为比较实用的 23 个字符函数。函数名及语法如下：
| 序号 | 函数名 | 语法原型 | 常用 |
|---|---|---|---|
| 1 | ASCII | ASCII( single_character ) | × |
| 2 | ASCIISTR | ASCIISTR( string ) | × |
| 3 | CHR | CHR( number_code ) | × |
| 4 | CONCAT | CONCAT( string1, string2 ) | × |
| 5 | Concat with || | string1 || string2 [|| string_n ] | √ |
| 6 | CONVERT | CONVERT( string1, char_set_to [, char_set_from] ) | × |
| 7 | DUMP | DUMP( expression [, return_format] [, start_position] [, length] ) | × |
| 8 | INITCAP | INITCAP( string1 ) | × |
| 9 | INSTR | INSTR( string, substring [, start_position [, th_appearance ] ] ) | √ |
| 10 | LENGTH | LENGTH( string1 ) | √ |
| 11 | LENGTHB | LENGTHB( string1 ) | × |
| 12 | LOWER | LOWER( string1 ) | √ |
| 13 | LPAD | LPAD( string1, padded_length [, pad_string] ) | × |
| 14 | LTRIM | LTRIM( string1 [, trim_string] ) | √ |
| 15 | NCHR | NCHR(number_code) | × |
| 16 | REPLACE | REPLACE( string1, string_to_replace [, replacement_string] ) | √ |
| 17 | RPAD | RPAD( string1, padded_length [, pad_string] ) | × |
| 18 | RTRIM | RTRIM( string1 [, trim_string ] ) | × |
| 19 | SUBSTR | SUBSTR( string, start_position [, length ] ) | √ |
| 20 | TRANSLATE | TRANSLATE( string1, string_to_replace, replacement_string ) | × |
| 21 | TRIM | TRIM( [ [ LEADING | TRAILING | BOTH ] trim_character FROM ] string1 ) | √ |
| 22 | UPPER | UPPER( string1 ) | √ |
| 23 | VSIZE | VSIZE( expression ) | × |
| 24 | TO_SINGLE_BYTE | TO_SINGLE_BYTE( string1 ) | × |
| 25 | TO_MULTI_BYTE | TO_MULTI_BYTE( string1 ) | × |
1.2、语法说明及案例
ASCII：返回指定字符串中第一个字符的 ASCII 码。示例：
SELECT ASCII('A') res FROM DUAL; -- res：65
SELECT ASCII('ABC') res FROM DUAL; -- res：65
ASCIISTR：返回指定字符串的 ASCII 码形式的字符串，非 ASCII 字符会被转化成 \xxxx 的形式（有网友示范了根据这个规律来识别中文字符，应该说这是个很好的思路，但不够严谨）。示例：
SELECT ASCIISTR('Oracle 数据库') res FROM DUAL; -- res：'Oracle \6570\636E\5E93'
CHR & NCHR：返回指定 ASCII 码所代表的字符。CHR( number_code USING NCHAR_CS ) 与 NCHR 的转换效果相同。示例：
SELECT CHR(65) res FROM DUAL; -- res：'A'
SELECT CHR(30000) res FROM DUAL; -- res：'u0'，注意与下两行做对比
SELECT NCHR(30000) res FROM DUAL; -- res：'田'
SELECT CHR(30000 USING NCHAR_CS) res FROM DUAL; -- res：'田'
CONCAT & ||：将多个字符串连接成一个新的字符串并返回。由于 CONCAT 一次只能连接两个字符串，当要连接多个字符串时，就需要嵌套，既繁琐又不易阅读。事实上稍有经验的开发人员从不用 CONCAT 函数，因为实际开发中经常需要连接多个字符串，这类需求用 || 来实现效果要好得多。示例：
SELECT CONCAT('A','B') res FROM DUAL; -- res：'AB'
SELECT CONCAT('A',CONCAT('B','C')) res FROM DUAL; -- res：'ABC'
SELECT 'A'||'B'||'C' res FROM DUAL; -- res：'ABC'
CONVERT：将指定字符串从一种字符集转换为另一种字符集，并返回转换后的字符串。可用的字符集有：US7ASCII、WE8DEC、WE8HP、F7DEC、WE8EBCDIC500、WE8PC850、WE8ISO8859P1。这也许是 Oracle 中最坑的函数，因为一般编程语言或数据库中，叫这个名字的函数往往都是用来转换数据类型的，结果在 Oracle 中却是用来转换字符集的。示例：
SELECT CONVERT('Oracle','WE8HP','F7DEC') res FROM DUAL; -- res：Oracle
DUMP：将指定字符串按指定进制返回。示例：
SELECT DUMP('Tech') res FROM DUAL; -- res：'Typ=96 Len=4: 84,101,99,104'
SELECT DUMP('Tech',10) res FROM DUAL; -- res：'Typ=96 Len=4: 84,101,99,104'
SELECT DUMP('Tech',16) res FROM DUAL; -- res：'Typ=96 Len=4: 54,65,63,68'
SELECT DUMP('Tech',1016) res FROM DUAL; -- res：'Typ=96 Len=4 CharacterSet=ZHS16GBK: 54,65,63,68'
SELECT DUMP('Tech',1017) res FROM DUAL; -- res：'Typ=96 Len=4 CharacterSet=ZHS16GBK: T,e,c,h'
INITCAP：返回指定字符串的帕斯卡命名（即每个单词首字母都大写）副本。示例：
SELECT INITCAP('asp.net core') res FROM DUAL; -- res：'Asp.Net Core'
INSTR：返回第二个字符串在第一个字符串中出现的位置，可通过起始位置和长度来限定查找范围。示例：
SELECT INSTR('Database','a',1,1) res FROM DUAL; -- res：2，第 1 次出现 a 的位置是 2
SELECT INSTR('Database','a',1,2) res FROM DUAL; -- res：4，第 2 次出现 a 的位置是 4
SELECT INSTR('Database','a',1,3) res FROM DUAL; -- res：6，第 3 次出现 a 的位置是 6
SELECT INSTR('Database','a',1,4) res FROM DUAL; -- res：0，第 4 次 a 没出现，所以是 0
SELECT INSTR('Database','a',3) res FROM DUAL; -- res：4，从第 3 个字母开始，第 1 次出现 a 的位置是 4
SELECT INSTR('Database','a',5) res FROM DUAL; -- res：6，从第 5 个字母开始，第 1 次出现 a 的位置是 6
SELECT INSTR('Database','a') res FROM DUAL; -- res：2，第 1 个字母开始，第 1 次出现 a 的位置是 2
SELECT INSTR('Database','A') res FROM DUAL; -- res：0，注意与上一行结果对比
LENGTH：返回指定字符串的长度。示例：
SELECT LENGTH(NULL) res FROM DUAL; -- res：NULL
SELECT LENGTH('') res FROM DUAL; -- res：NULL
SELECT LENGTH(' ') res FROM DUAL; -- res：1
SELECT LENGTH('Asp.Net Core') res FROM DUAL; -- res：12
SELECT LENGTH('数据库') res FROM DUAL; -- res：3
LENGTHB & VSIZE：返回指定字符串的字节数。经测试发现这两个函数的用法一样，结果也一样，示例：
SELECT LENGTHB(NULL) res FROM DUAL; -- res：NULL
SELECT LENGTHB('') res FROM DUAL; -- res：NULL
SELECT LENGTHB(' ') res FROM DUAL; -- res：1
SELECT LENGTHB('Asp.Net Core') res FROM DUAL; -- res：12
SELECT LENGTHB('数据库') res FROM DUAL; -- res：6
LOWER & UPPER：返回指定字符串的小写或大写副本，示例：
SELECT LOWER('Data Base') res FROM DUAL; -- res：'data base'
SELECT UPPER('Data Base') res FROM DUAL; -- res：'DATA BASE'
LPAD & RPAD：如果不使用 CHAR/NCHAR 类型，也不大可能会用到这两个函数。它们的作用就是通过填充或截取，使得指定字符串达到指定长度，可以指定要用来填充的字符。示例：
SELECT LPAD('ABC',2) res FROM DUAL; -- res：'AB'
SELECT LPAD('ABC',4) res FROM DUAL; -- res：' ABC'
SELECT LPAD('ABC',4,'$') res FROM DUAL; -- res：'$ABC'
LTRIM & RTRIM & TRIM：返回指定字符串去掉左边、右边或两边空格后的字符串。示例：
SELECT LTRIM(' A ') res FROM DUAL; -- res：'A '
SELECT RTRIM(' A ') res FROM DUAL; -- res：' A'
SELECT TRIM(' A ') res FROM DUAL; -- res：'A'
TRIM(leading|trailing|both substr from srcstr)：返回将 srcstr 字符串中的指定的 substr 字符去除后的字符串，其中 substr 只能是一个字符，否则直接语法报错，leading|trailing|both 分别指定将 srcstr 字符串中前面|后面|前后所有的 substr 字符去除。示例：
SELECT TRIM(leading 'a' FROM 'aaAaAaa') res FROM DUAL; -- res：'AaAaa'
SELECT TRIM(trailing 'a' FROM 'aaAaAaa') res FROM DUAL; -- res：'aaAaA'
SELECT TRIM(both 'a' FROM 'aaAaAaa') res FROM DUAL; -- res：'AaA'
LTRIM(str1,str2)、RTRIM(str1,str2)：都将从 str1 字符串中去找 str2 中包含的字符，如果找到了就去除，如果出现多次都会被去除，但前提是 str2 字符串中第一个字符与 str1 字符串中第一个字符相同（rtrim 函数是与 str1 字符串中最后一个字符相同），返回最后剩下的字符串。示例：
SELECT LTRIM('ABABA','A') res FROM DUAL; -- res：'BABA'
SELECT LTRIM('ABABA','B') res FROM DUAL; -- res：'ABABA'
SELECT LTRIM('ABABA','AB') res FROM DUAL; -- res：NULL
SELECT LTRIM('ABABA','BA') res FROM DUAL; -- res：NULL
SELECT LTRIM('ABABA','AC') res FROM DUAL; -- res：'BABA'
SELECT LTRIM('ABABA','BC') res FROM DUAL; -- res：'ABABA'
REPLACE：返回指定字符串被替换后的结果字符串。如果不指定替换字符串，将直接把原字符串中要替换的字符串删除。示例：
SELECT REPLACE('ABCABC','B','D') res FROM DUAL; -- res：'ADCADC'
SELECT REPLACE('ABCABC','B') res FROM DUAL; -- res：'ACAC'
SELECT REPLACE('ABCABC','b') res FROM DUAL; -- res：'ABCABC'（注意跟上一行结果做对比）
SUBSTR：返回从指定字符串中指定位置开始往后指定长度的字符串。如果不指定长度就会截取到字符串末尾为止；开始截取的位置必须指定，可以指定负数，负数时就会从后往前截取，指定 0 的效果与指定 1 的效果是一样的；如果起始位置值的绝对值大于字符串长度，返回值是 NULL。示例：
SELECT SUBSTR('ABCD',1,2) res FROM DUAL; -- res：'AB'
SELECT SUBSTR('ABCD',2,2) res FROM DUAL; -- res：'BC'
SELECT SUBSTR('ABCD',2) res FROM DUAL; -- res：'BCD'
SELECT SUBSTR('ABCD',-2) res FROM DUAL; -- res：'CD'
SELECT SUBSTR('ABCD',0) res FROM DUAL; -- res：'ABCD'
SELECT SUBSTR('ABCD',1) res FROM DUAL; -- res：'ABCD'
SELECT SUBSTR('ABCD',5) res FROM DUAL; -- res：空
SELECT SUBSTR('ABCD',-5) res FROM DUAL; -- res：空
TRANSLATE：返回指定字符串被替换后的结果字符串。示例：
SELECT TRANSLATE('1tech23', '123', '456') res FROM DUAL; -- res：4tech56
SELECT TRANSLATE('222tech', '2ec', '3it') res FROM DUAL; -- res：333tith
TO_MULTI_BYTE、TO_SINGLE_BYTE：返回指定字符串的全角或半角副本。示例：
SELECT TO_MULTI_BYTE('oracle') res FROM DUAL; -- res: ｏｒａｃｌｅ
SELECT TO_SINGLE_BYTE('ｏｒａｃｌｅ') res FROM DUAL; -- res: oracle
2、数字函数
2.1、数字函数简介
据不完全统计，Oracle 中的数字函数总计约 40 来个，包含大量三角函数、指数函数、对数函数、进制转换函数、类型转换函数、近似值函数、聚合函数等数学函数。可谓是种类繁多，但大部分都不常用，其中类型转换函数、近似值函数和聚合函数将在后续章节中介绍，本节只简要介绍除此之外，我本人认为比较实用的 8 个数字函数。函数名及语法如下：
| 序号 | 函数名 | 语法原型 | 常用 |
|---|---|---|---|
| 1 | ABS | ABS( number ) | √ |
| 2 | CEIL | CEIL( number ) | √ |
| 3 | FLOOR | FLOOR( number ) | √ |
| 4 | GREATEST | GREATEST( expr1 [, expr2, ... expr_n] ) | × |
| 5 | LEAST | LEAST( expr1 [, expr2, ... expr_n] ) | × |
| 6 | MOD | MOD( m, n ) | × |
| 7 | POWER | POWER( m, n ) | × |
| 8 | SIGN | SIGN( number ) | × |
2.2、语法说明及案例
ABS：返回指定数字的绝对值。示例：
SELECT ABS(3888) res FROM DUAL; -- res：3888
SELECT ABS(-3888) res FROM DUAL; -- res：3888
SELECT ABS(23.65) res FROM DUAL; -- res：23.65
SELECT ABS(23.65 * -1) res FROM DUAL; -- res：23.65
CEIL：返回大于或等于指定数字的最大整数。示例：
SELECT CEIL(1.4) res FROM DUAL; -- res：2
SELECT CEIL(1.5) res FROM DUAL; -- res：2
SELECT CEIL(-1.5) res FROM DUAL; -- res：-1
FLOOR：返回小于或等于指定数字的最小整数。示例：
SELECT FLOOR(1.4) res FROM DUAL; -- res：1
SELECT FLOOR(1.5) res FROM DUAL; -- res：1
SELECT FLOOR(-1.5) res FROM DUAL; -- res：-2
GREATEST：返回参数列表中值最大的一项。参数既可以为数字，也可以是字符串，如果参数列表数据类型不一致会自动转型。示例：
SELECT GREATEST(2, 5, 12, 3) res FROM DUAL; -- res：12
SELECT GREATEST('2', '5', '12', '3') res FROM DUAL; -- res：'5'
SELECT GREATEST('apples', 'oranges', 'bananas') res FROM DUAL; -- res：'oranges'
SELECT GREATEST('apples', 'applis', 'applas') res FROM DUAL; -- res：'applis'
LEAST：返回参数列表中值最小的一项。与 GREATEST 用法相同，结果相反。示例：
SELECT LEAST(2, 5, 12, 3) res FROM DUAL; -- res：2
SELECT LEAST('2', '5', '12', '3') res FROM DUAL; -- res：'12'
SELECT LEAST('apples', 'oranges', 'bananas') res FROM DUAL; -- res：'apples'
SELECT LEAST('apples', 'applis', 'applas') res FROM DUAL; -- res：'applas'
MOD：返回第一个参数除以第二个参数后的余数。示例：
SELECT MOD(15,4) res FROM DUAL; -- res：3
SELECT MOD(15,0) res FROM DUAL; -- res：15
SELECT MOD(11.6, 2) res FROM DUAL; -- res：1.6
SELECT MOD(11.6, 2.1) res FROM DUAL; -- res：1.1
SELECT MOD(-15, 4) res FROM DUAL; -- res：-3
SELECT MOD(-15, 0) res FROM DUAL; -- res：-15
POWER：返回第一个参数的第二个参数次方。示例：
SELECT POWER(5, 3) res FROM DUAL; -- res：125
SELECT POWER(-5, 3) res FROM DUAL; -- res：-125
SELECT POWER(6.2, 3) res FROM DUAL; -- res：238.328
SELECT POWER(6.2, 3.5) res FROM DUAL; -- res：593.431934277892
SIGN：如果指定数字等于 0，则返回 0，如果大于 0，则返回 1，如果小于 0，则返回 -1。示例：
SELECT SIGN(0) res FROM DUAL; -- res：0
SELECT SIGN(5) res FROM DUAL; -- res：1
SELECT SIGN(-5) res FROM DUAL; -- res：-1
3、日期函数
3.1、日期函数简介
据不完全统计，Oracle 中的日期函数总计约 20 个，主要是对日期的年、月、日、小时、分、秒等部分的操作和运算，还有取系统时间、会话时区等函数。对于普通应用而言，其中一大部分函数都不太常用，日期转型函数将在下一篇文章中介绍，本节只简要介绍我本人认为比较实用的 6 个日期函数。函数名及语法如下：
| 序号 | 函数名 | 语法原型 | 常用 |
|---|---|---|---|
| 1 | ADD_MONTHS | ADD_MONTHS( date1, number_months ) | √ |
| 2 | LAST_DAY | LAST_DAY( date ) | √ |
| 3 | MONTHS_BETWEEN | MONTHS_BETWEEN( date1, date2 ) | × |
| 4 | NEXT_DAY | NEXT_DAY( date, weekday ) | × |
| 5 | SYSDATE | SYSDATE | √ |
| 6 | SYSTIMESTAMP | SYSTIMESTAMP | × |
3.2、语法说明及案例
ADD_MONTHS：返回在指定日期基础上再加指定个月后的日期。由于增加一个月往往不像增加 28~31 天那么简单，为了便于增加月，Oracle 专门实现了这样一个函数。示例：
SELECT ADD_MONTHS(fn_now,1) res FROM DUAL; -- res：2017-02-10 19:21:30
SELECT ADD_MONTHS(fn_now,-1) res FROM DUAL; -- res：2016-12-10 19:21:30
LAST_DAY：返回指定日期所在月份最后一天的日期，如果是长日期，返回日期的时间部分就是指定日期的时间部分。示例：
SELECT LAST_DAY(fn_today) res FROM DUAL; -- res：2017-01-31
SELECT LAST_DAY(fn_now) res FROM DUAL; -- res：2017-01-31 19:21:30
MONTHS_BETWEEN：返回第一个日期参数减去第二个日期参数的结果，长日期会忽略时间部分，第一个日期大于第二个日期时返回正数，第一个日期小于第二个日期时返回负数，两个日期相同时返回 0，天数不同时返回小数。
SELECT MONTHS_BETWEEN(fn_today,TO_DATE('2016-10-10','yyyy-mm-dd')) res FROM DUAL; -- res：3
SELECT MONTHS_BETWEEN(fn_today,TO_DATE('2017-02-10','yyyy-mm-dd')) res FROM DUAL; -- res：-1
NEXT_DAY：返回指定日期之后的下一个星期几，假如指定日期是星期六，那指定日期之后下一个星期日就是当周的周日，而指定日期之后下一个星期一就是下周的周一，第二个参数的取值可能是：{'Sunday'/'Mondy'/'Tuesday'/'Wednesday'/'Thursday'/'Friday'/'Saturday'}，也可能是：｛'星期日'/'星期一'/'星期二'……｝或｛1/2/3……6/7｝，取决于你操作系统的本地语言支持。示例：
SELECT fn_today res FROM DUAL; -- res：2017-01-10
SELECT NEXT_DAY(fn_today,'Sunday') res FROM DUAL; -- res：2017-01-15
SELECT NEXT_DAY(fn_today,'Monday') res FROM DUAL; -- res：2017-01-16
SYSDATE & SYSTIMESTAMP：返回当前系统时间，SYSTIMESTAMP 的返回值还包含 6 位的小数秒和时区。示例：
SELECT SYSDATE res FROM DUAL; -- res：2017-01-13 21:42:20
SELECT SYSTIMESTAMP res FROM DUAL; -- res：13-1月 -17 09.40.34.235000 下午 +08:00
SELECT TO_CHAR(SYSTIMESTAMP,'yyyy-mm-dd hh24:mi:ss.ff') res FROM DUAL; -- res：2017-01-13 21:42:33.266000
3.3、日期函数补充
INTERVAL：返回变动后的日期。示例：
SELECT fn_now+(INTERVAL '1' SECOND) res FROM DUAL; -- res：2017-01-10 19:21:31，加 1 秒钟
SELECT fn_now+(INTERVAL '1' MINUTE) res FROM DUAL; -- res：2017-01-10 19:22:30，加 1 分钟
SELECT fn_now+(INTERVAL '1' HOUR) res FROM DUAL; -- res：2017-01-10 20:21:30，加 1 小时
SELECT fn_now+(INTERVAL '1' DAY) res FROM DUAL; -- res：2017-01-11 19:21:30，加 1 天
SELECT fn_now+(INTERVAL '1' MONTH) res FROM DUAL; -- res：2017-02-10 19:21:30，加 1 个月
SELECT fn_now+(INTERVAL '1' YEAR) res FROM DUAL; -- res：2018-01-10 19:21:30，加 1 年
SELECT fn_now+(INTERVAL '1:1:1' HOUR TO SECOND) res FROM DUAL; -- res：2017-01-10 20:22:31，加指定时间
NUMTOYMINTERVAL(d,c)：将 d 转换为 INTERVAL YEAR TO MONTH 类型，c 的常见取值有 'year'|'month'。
SELECT fn_today+NUMTOYMINTERVAL(3,'year') res FROM DUAL; -- res：2020-01-10，加 3 年
NUMTODSINTERVAL(d,c)：将 d 转换为 INTERVAL DAY TO SECOND 类型，c 的常见取值有 'day'|'hour'|'minute'|'second'。
SELECT fn_today+NUMTODSINTERVAL(3,'day') res FROM DUAL; -- res：2017-01-13，加 3 天
除上面所有日期计算函数之外，Oracle 的日期计算还支持+和-。示例：
SELECT fn_now+1 res FROM DUAL; -- res: 2017-01-11 19:21:30，表示加一天
SELECT fn_now-1 res FROM DUAL; -- res: 2017-01-09 19:21:30，表示减一天
综合示例：
SELECT LAST_DAY(fn_now)-fn_now res FROM DUAL; -- res: 21，计算本月剩余天数
4、总结
本文主要介绍了常见的字符函数、数字函数和日期函数，其中部分函数在使用的时候需要注意操作系统的本地语言支持（NLS/Native Language Support），可以通过设置 NLS_LANG 环境变量来改变系统的本地语言支持。
演示汉字（韩）与数字码之间的推导过程如下：
SELECT ASCIISTR('韩') res FROM DUAL; -- res：'\97E9'
SELECT TO_NUMBER('97E9','xxxx') res FROM DUAL; -- res：38889
SELECT NCHR(38889) res FROM DUAL; -- res：'韩'
本文链接：http://www.cnblogs.com/hanzongze/p/oracle-systemfunction-1.html
版权声明：本文为博客园博主 韩宗泽 原创，作者保留署名权！欢迎通过转载、演绎或其它传播方式来使用本文，但必须在明显位置给出作者署名和本文链接！本人初写博客，水平有限，若有不当之处，敬请批评指正，谢谢！
