Oracle内置函数

一、Dual表

该表是Oracle中真实存在的一个表，任何用户都可以读取，多数情况下可以用在没有目标的SELECT查询语句中。它本身只包含了一个DUMMY字段。DUAL表对Oracle很重要，用户不要试图删除该表，一旦删除，Oracle将无法启动。

二、数值型函数

1.绝对值、取余、判断数值正负函数

(1)ABS(n)函数：用于返回绝对值。

SELECT ABS('100'),ABS(-50) FROM DUAL;
--OUTPUT:100, 50

(2)MOD(n2, n1)函数：返回n2除以n1的余数。

参数为任意数值或可以隐式转成数值的类型。如果n1为0，那么该函数将返回n2。

SELECT MOD(5,2), MOD(8/2,5),MOD('11',5),MOD(-10,6),MOD(3,0) FROM DUAL;
--OUTPUT:1, 4, 1, -4, 3

(3)SIGN(n)函数：返回参数n的符号。

正数返回1，0返回0，负数返回-1。但如果n为BINARY_FLOAT或BINARY_DOUBLE类型时，n>=0或者n=NaN函数会返回1。

SELECT SIGN('9'),SIGN(-9),SIGN(0.00),SIGN(-2*'9') FROM DUAL;
--OUTPUT:1, -1, 0, -1

2.三角函数

(1)COS(n)函数：用于返回参数n的余弦，n为弧度表示的角度。

SELECT COS(3.1415926),COS('3.1416926') FROM DUAL;
--OUTPUT:-0.99999999999999856406703032941211807559,-0.99999999500535754754861880392607652199

(2)其它三角函数

ACOS(n)：返回n的反余弦值。

COSH(n)：返回n的双曲余弦值。

SIN(n)：返回n的正弦值。

SINH(n)：返回n的双曲正弦值。

ASIN(n)：返回n的反正弦值。

TAN(n)：返回n的正切值。

TANH(n)：返回n的双曲正切值。

ATAN(n)：返回n的反正切值。

3.返回以指定数值为准整数的函数

(1)CEIL(n)函数：返回大于等于输入参数的最小整数。

该输入参数要求是十进制数值类型，或可以隐式地转换成数值的类型，可以是非整数。

SELECT CEIL(10),CEIL('10.5'),CEIL(-10.2) FROM DUAL;
--OUTPUT:10, 11, -10

(2)FLOOR(n)函数：返回小于或等于参数的最大整数。

该函数输入参数要求是十进制数值类型，或可以隐式地转换成数值的类型，可以是非整数。

SELECT FLOOR(10),FLOOR('10.5'),FLOOR(-10.2) FROM DUAL;
--OUTPUT:10, 10, -11

4.指数、对数函数

(1)POWER(n2,n1)函数：利用该函数可以得到n2的n1次幂的结果。

这两个参数为任意数值，但如果n2为负数，那么n1必须为整数。

SELECT POWER(5,2),POWER('5',2),POWER(5.5,2.5),POWER(-5,2), 5 * 5 FROM DUAL;
--OUTPUT:25, 25, 70.94253836732937201280515546736005249344, 25, 25

类似函数：EXP(n)函数，表示返回e的n次幂

(2)SQRT(n)函数：该函数返回n的平方根。

n为数字类型的时候不能为负数，将返回一个实数，当n为BINARY_FLOAT或BINARY_DOUBLE类型时，n<0将返回Nan。

SELECT SQRT(100),SQRT('64') FROM DUAL;
--OUTPUT:10, 8

(3)LOG(n1,n2)函数：返回以n1为底n2的对数。

n1是除1和0以外的任意正数，n2为正数。

SELECT LOG(10,100),LOG('10','1000'),LN(10) FROM DUAL;
--OUTPUT:2, 3, 2.3025850929940456840179914546843642076

类似函数：LN(n)函数，表示返回n的自然对数。

5.四舍五入截取函数

(1)ROUND(n,integer)函数：将数值n四舍五入成第二个参数指定的形式的十进制数。

参数integer要求是整数，如果不是整数，那么它将被自动截取为整数部分。当integer为正整数时，表示n被四舍五入为integer位小数。如果该参数为负数，则n被四舍五入至小数点向左integer位。

SELECT ROUND(3.1415926,3), ROUND(3.1416926,2.5),ROUND(13.1415926,-1) FROM DUAL;
--OUTPUT:3.142, 3.14, 10

(2)TRUNC(n,integer)函数：它把数值n根据integer的值进行截取。

SELECT TRUNC(3.1415926), TRUNC(3.1415926,4), TRUNC(3.1415926,2.5),TRUNC(13.1415926,-1) FROM DUAL;
--OUTPUT:3, 3.1415, 3.14, 10

三、字符串处理函数

1.系统日期、时间函数

(1)CHR(n[USING NCHAR_CS])函数：根据相应的字符集，把给定的ASCII码转换为字符。

USING NCHAR_CS指明字符集。

SELECT CHR(65) || CHR(67) ABC,CHR(54678) FROM DUAL;
--OUTPUT:AC, $

(2)ASCII(str)函数函数：返回参数首字母的ASCII码值。

该返回值总是以用户使用的字符集为基础的，如果用户的数据库字符集是7位的ASCII值，那就得到一ASCII码值。

SELECT ASCII('中'),ASCII('HELLO') FROM DUAL;
--OUTPUT:14989485, 72

2.获取字符串长度函数

(1){[LENGTH] | [LENGTHB] | [LENGTHC] | [LENGTH2] | [LENGTH4]} (str)函数：返回指定字符串的长度。

SELECT LENGTH('HELLO WORLD') FROM DUAL;
--OUTPUT:11

3.字符串截取函数

(1) {[SUBSTR] | [SUBSTRB] | [SUBSTRC] | [SUBSTR2] | [SUBSTR4]} (str,position[,substring_length])函数：该函数提供截取字符串的功能。

各参数表示含义如下：

SUBSTR：以字符为单位。

SUBSTRB：以字节为单位。

SUBSTRC：以unicode字符为单位。

SUBSTR2：以UCS2代码点为单位。

SUBSTR4：以UCS4代码点为单位。

str：原始字符串。

position：要截取字符串的开始位置。初始为1，如果该值为负数，则表示从str的右边算起。

substring_length：截取的长度。

SELECT SUBSTR('HELLO WORLD',3,4),SUBSTR('HELLO WORLD',-3,4),SUBSTRB('ABCDEFG',5,4.2) FROM DUAL;
--OUTPUT:LLO, RLD, EFG

4.字符串连接函数

(1)CONCAT(str1,str2)函数：该函数连接两个参数并返回，效果和连接符“||”相似。

SELECT CONCAT('HELLO',' WORLD') FROM DUAL;
--OUTPUT:HELLO WORLD

5.字符串搜索函数

(1){[INSTR] | [INSTRB] | [INSTRC] | [INSTR2] | [INSTR4]} (string, substring[,position[,occurrence]])函数：该函数可以让我们在指定的字符串中搜索是否存在另一个字符串。

各项参数表示含义如下：

INSTR：以字符为单位。

INSTRB：以字节为单位。

INSTRC：以unicode字符为单位。

INSTR2：以UCS2代码点为单位。

INSTR4：以UCS4代码点为单位。

string：待搜索的字符串。

substring：要搜索的字符串。

position：搜索的开始位置，默认为1，表示字符串左边第一个位置；如果为负数，则表示字符串的右边位置为起始位置。

occurrence：substring第几次出现，默认是1。

SELECT INSTR('CORPORATE FLOOR','OR', -3, 2),INSTRB('CORPORATE FLOOR','OR',5,2) FROM DUAL;
--OUTPUT:2, 14

6.字母大小写转换函数

(1)UPPER(str)函数：该函数将指定的参数全部转换成大写字母。

SELECT UPPER('hello world') FROM DUAL;
--OUTPUT:HELLO WORLD

(2)LOWER(str)函数：该函数将指定的参数全部转换成小写字母。

SELECT LOWER('HELLO WORLD') FROM DUAL;
--OUTPUT:hello world

(3)INITCAP(str)函数：该函数参数的所有单词首字母转换成大写字母。

SELECT INITCAP('hello world') FROM DUAL;
--OUTPUT:Hello World

7. 带排序参数的字母大小写转换函数

(1)NLS_INITCAP(str[,nlsparam])函数：将指定参数的第一个字母转换成大写。

nlsparam参数为可选参数，其设置可以到NLS_DATABASE_PARAMETERS表中查询。

SELECT NLS_INITCAP('hello world'),NLS_INITCAP('this is my cat','NLS_SORT=SCHINESE_STROKE_M') FROM DUAL;
--OUTPUT:Hello World, This Is My Cat

(2)NLS_UPPER(str[,nlsparam])函数：将指定参数变成大写。

nlsparam参数同NLS_INITCAP函数设置。

SELECT NLS_UPPER('this is my cat','NLS_SORT=SCHINESE_PINYIN_M') FROM DUAL;
--OUTPUT:THIS IS MY CAT

(3)NLS_LOWER(str[,nlsparam])函数：将指定参数转换成小写。

nlsparam参数同NLS_INITCAP函数设置。

SELECT NLS_LOWER('HELLO CHINA','NLS_SORT=XGerman') FROM DUAL;
--OUTPUT:hello china

8.为指定参数排序函数

(1)NLSSORT(str[,nlsparam])函数：根据nlsparam指定的方式对str进行排序。

9.替换字符串函数

(1)REPLACE(str,search_string[,replacement_string])函数：替换字符串的函数。

具体代表的含义如下：

str：表示搜索的目标字符串。

search_string：在目标字符串中要搜索的字符串。

replacement_string：该参数可选，用它可替代被搜索到的字符串，如果该参数不用，则表示从str参数中删除search_string字符串。

SELECT REPLACE('this is a dog','dog','cat') FROM DUAL;
--OUTPUT:this is a cat

10. 字符串填充函数

(1)RPAD(expr1,n[,expr2])函数：该函数功能是在字符串expr1的右边用字符串expr2填充，直到整个字符串长度为n时为止。如果expr2不存在，则以空格填充。

SELECT RPAD('--',6,'*') FROM DUAL;
--OUTPUT:--****

(2)LPAD(expr1,n[,expr2])函数：该函数功能是在字符串expr1的左边用字符串expr2填充，直到整个字符串长度为n时为止。如果expr2不存在，则以空格填充。

SELECT LPAD('--',6,'*') FROM DUAL;
--OUTPUT:****--

11. 删除字符串首尾指定字符的函数

(1)TRIM( [LEADING | TRAILING | BOTH] [trim_character FROM] trim_source)函数：该函数将删除指定的前缀或尾随的字符，默认删除空格。

SELECT TRIM('   HELLO WORLD') FROM DUAL;
--OUTPUT:HELLO WORLD

(2)RTRIM(str[,set])函数：与RPAD函数相反，该函数会提供将str右边出现在set中的字符删除掉。如果set没有，则默认删除空格。

SELECT LTRIM('-----HELLO WORLD*****','-') FROM DUAL;
--OUTPUT:HELLO WORLD*****

(3)LTRIM(str[,set])函数：与RTRIM函数相似，该函数会提供将str左边出现在set中的字符删除掉。如果set没有，则默认删除空格。

SELECT RTRIM('-----HELLO WORLD*****','*') FROM DUAL;
--OUTPUT:-----HELLO WORLD

12. 字符集名称和ID互换函数

(1)NLS_CHARSET_ID(string)函数：该函数可以得到字符集名称对应ID。

string表示字符集名称。

SELECT NLS_CHARSET_ID('US7ASCII') FROM DUAL;
--OUTPUT:1

(2)NLS_CHARSET_NAME(number)函数：该函数可以根据字符集ID得到对应名称。

number表示字符集ID。

SELECT NLS_CHARSET_NAME(3),NLS_CHARSET_NAME(1) FROM DUAL;
--OUTPUT:WE8HP, US7ASCII

四、日期型函数

1.系统日期、时间函数

(1)SYSDATE函数：可以得到系统的当前日期，

SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MM:SS') FROM DUAL;
--OUTPUT:2021-08-15 18:08:12

(2)SYSTIMESTAMP函数：返回系统时间，该时间包含时区信息，精确到微秒。

SELECT SYSTIMESTAMP FROM DUAL;
--OUTPUT:15-8月 -21 06.52.55.442000000 下午 +08:00

2.得到数据库时区函数

(1)DBTIMEZONE函数：返回数据库时区。

SELECT DBTIMEZONE FROM DUAL;
--OUTPUT:+00:00

3.为日期加上指定月份函数

(1)ADD_MONTHS(date,integer)函数：该函数将返回在指定的日期上加一个月份数后的日期。

其中date表示指定的日期； integer表示要加的月份数，该值如果为负数，则表示减去的月份数。

需要注意，当指定的日期是月的最后一天时，最后函数返回的结果也将是新月的最后一天。而如果新的月份比指定日期月份的天数少，则函数将自动回调有效日期。

SELECT TO_CHAR(ADD_MONTHS(TO_DATE('2021-8-15','YYYY-MM-DD'),3),'YYYY-MM-DD') FROM DUAL;
--OUTPUT:2021-11-15

(2)SESSIONTIMEZONE函数：可以返回当前会话的时区。

SELECT SESSIONTIMEZONE FROM DUAL;
--OUTPUT:Asia/Shanghai

4.返回指定月份最后一天函数

(1)LAST_DAY(date)函数：该函数返回参数指定日期对应月份的最后一天。

SELECT LAST_DAY(SYSDATE) FROM DUAL;
--OUTPUT:31-8月 -21

5.返回指定日期后一周的日期函数

(1)NEXT_DAY(date,str)函数：该函数返回当前日期向后的一周char的对应日期，char表示的是星期几，全称和缩写都允许。

SELECT SYSDATE,NEXT_DAY(SYSDATE,'星期二') FROM DUAL;
--OUTPUT:15-8月 -21, 17-8月 -21

6.返回会话所在时区当前日期函数

(1)CURRENT_DATE函数：该函数得到会话时区的当前日期。

SELECT SESSIONTIMEZONE, TO_CHAR(CURRENT_DATE,'YYYY-MM-DD') FROM DUAL;
--OUTPUT:Asia/Shanghai, 2021-08-15

7. 提取指定日期特定部分的函数

(1)EXTRACT (datetime)函数：该函数可以从指定的时间当中提取到指定的日期部分.

例如从给定的日期得到年、月、分等。

SELECT EXTRACT(YEAR FROM SYSDATE),
       EXTRACT(MINUTE FROM TIMESTAMP '2021-08-27 8:30:56'),
       EXTRACT(SECOND FROM TIMESTAMP '2021-08-27 8:30:56') 
  FROM DUAL;
--OUTPUT:2021, 30, 56

8.得到两个日期之间的月份数

(1)MONTHS_BETWEEN(date1,date2)函数：该函数返回date1和date2之间的月份数。

函数两个参数都为日期型数据。当date1>date2时，如果两个参数表示日期是某月中的同一天，或它们都是某月中的最后一天，则该函数返回一整型数；否则，将返回小数。当date1<date2时，则返回一负值。

SELECT MONTHS_BETWEEN(TO_DATE('2021-9-15','YYYY-MM-DD'), TO_DATE('2021-8-15','YYYY-MM-DD')) AS ONE,
       MONTHS_BETWEEN(TO_DATE('2021-9-15','YYYY-MM-DD'), TO_DATE('2021-10-15','YYYY-MM-DD')) AS TWO,
       MONTHS_BETWEEN(TO_DATE('2021-9-15','YYYY-MM-DD'), TO_DATE('2021-8-21','YYYY-MM-DD')) AS THREE 
  FROM DUAL;
--OUTPUT:1, -1, 0.8064516129032258064516129032258064516129

9.时区时间转换函数

(1)NEW_TIME(date,timezone1,timezone2)函数：该函数将返回时间date在时区timezone1转换到时区timezone2的时间。

SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MM:SS'), 
       TO_CHAR(NEW_TIME(SYSDATE,'PDT','EST'),'YYYY-MM-DD HH24:MM:SS')
  FROM DUAL;
--OUTPUT:2021-08-15 20:08:29, 2021-08-15 22:08:29

10.日期四舍五入、截取函数

(1)ROUND(date[,fmt])函数：该函数将date舍入到fmt指定的形式。

如果参数fmt被省略，则date将被处理到最近的一天。

SELECT ROUND(SYSDATE,'YEAR') FROM DUAL;
--OUTPUT:01-8月 -21

(2)TRUNC(date[,fmt])函数：该函数将date截取到fmt指定的形式。

如果fmt省略，则截取到最近的日期。

SELECT TRUNC(SYSDATE,'MONTH') FROM DUAL;
--OUTPUT:01-8月 -21

更多用法参考：

五、转换函数

1.字符串转ASCII类型字符串函数

(1)ASCIISTR(str)函数：该函数可将任意字符集的字符串转换为数据库字符集对应的ASCII字符串。

SELECT ASCIISTR('世界,WORLD') FROM DUAL;
--OUTPUT:\4E16\754C,WORLD

2.二进制转十进制函数

(1)BIN_TO_NUM(data[,data...])函数：该函数可以将二进制转换成对应的十进制。data表示二进制数，一位用“，”隔开。

SELECT BIN_TO_NUM(1),BIN_TO_NUM(1,0,0),BIN_TO_NUM(1,0,0,0) FROM DUAL;
--OUTPUT:1, 4, 8

3.数据类型转换函数

(1)CAST(expr as type_name)函数：该函数是进行类型转换的，可以把expr参数转成type_name类型。基本上用于数字与字符之间以及字符与日期类型之间的转换。

SELECT CAST('258' AS INTEGER),
       CAST(258 AS VARCHAR2(6)),
       CAST(SYSDATE AS VARCHAR2(20)) 
  FROM DUAL;
--OUTPUT:258, 258, 15-8月 -21

具体规则参考：

4.字符串和ROWID相互转换函数

(1)CHARTOROWID(str)函数：该函数将字符串类型转成ROWID类型。

char为待转的字符串，其类型可以是CHAR、VARCHAR2、NCHAR、NVARCHAR2，但必须符合ROWID格式，长度为18。

SELECT T.ROWID,T.* FROM DUAL T;
SELECT CHARTOROWID('AAAACOAABAAAAWJAAA') FROM DUAL;
--OUTPUT:AAAACOAABAAAAWJAAA

(2)ROWIDTOCHAR(rowid)函数：该函数将行记录的ROWID转成字符串。参数rowid长度为18，所以返回结果长度18。

SELECT ROWIDTOCHAR('AAAACOAABAAAAWJAAA') FROM DUAL;
--OUTPUT:AAAACOAABAAAAWJAAA

(2)ROWIDTONCHAR(rowid)函数：同ROWIDTOCHAR(rowid)操作相同，但返回类型是NVARCHAR2。

SELECT ROWIDTONCHAR('AAAACOAABAAAAWJAAA') FROM DUAL;
--OUTPUT:AAAACOAABAAAAWJAAA

5.字符串在字符集间转换函数

(1)CONVERT(str,dest_char_sest[,source_char_set])函数：该函数用于把字符串从一个字符集转到另一个字符集。

各参数的表示含义如下：

str：等待转换的字符。可以是CHAR、VARCHAR2、NCHAR、NVARCHAR2、CLOB、NCLOB类型。

dest_char_sest：转变后的字符集。

source_char_set：原字符集，如果没有该参数，则默认数据库实例字符集。

SELECT CONVERT('China', 'US7ASCII', 'ZHS16GBK') FROM DUAL;
--OUTPUT:China

6.十六进制字符串与RAW类型相互转换函数

(1)HEXTORAW(str)函数：该函数把十六进制的字符串转换成raw类型的数据。

SELECT HEXTORAW('4E') FROM DUAL;
--OUTPUT:4E

(2)RAWTOHEX(raw)函数：与HEXTORAW函数相反，它把raw类型表示成一个由十六进制字符表示的串，返回VARCHAR2类型。

SELECT RAWTOHEX('4E') FROM DUAL;
--OUTPUT:3445

(3)RAWTONHEX(raw)函数：同函数RAWTOHEX(raw)转换效果相同，不过返回的类型是NVARCHAR2类型，而不是VARCHAR2类型。

SELECT RAWTONHEX('4E') FROM DUAL;
--OUTPUT:3445

7.数值转换成字符型函数

(1)TO_DATE(str[,fmt[,nlsparam]])函数：该函数可将字符型数据转换成日期型数据。

各参数表示含义如下：

str：待转换的字符。

fmt：表示转换的格式。

nlsparam：控制格式化时使用的语言类型。

SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MM:SS','NLS_DATE_LANGUAGE=ENGLISH') FROM DUAL;
--OUTPUT:2021-08-15 21:08:02

(2)TO_CHAR (n,[, fmt[,nlsparam]])函数：该函数将一个数值型参数转换成一个字符型数据。它同前面介绍的同名函数一样，只不过转换的对象变化了。

SELECT TO_CHAR(16.652,'99.99') FROM DUAL;
--OUTPUT:16.65

8.字符转日期型函数

(1)TO_DATE(str[,fmt[,nlsparam]])函数：该函数可将字符型数据转换成日期型数据。

各参数的具体含义如下：

str：待转换的字符。类型可以是CHAR、VARCHAR2、NCHAR、NVARCHAR2。

fmt：表示转换的格式。

nlsparam：控制格式化时使用的语言类型。

SELECT TO_DATE('2021-08-27','YYYY-MM-DD') FROM DUAL;
--OUTPUT:27-8月 -21

9.字符串转数字函数

(1)TO_NUMBER (expr[,fmt[,nlsparam]])函数：该函数将字符串转成数字。

各参数表示的具体含义如下：

expr：待转换的字符，其类型可以是CHAR、VARCHAR2、NCHAR、NVARCHAR2。

fmt：指定转换的数字格式。

nlsparam：该参数指定fmt的特征。通常包括小数点字符、组分隔符、本地钱币符号。

SELECT TO_NUMBER('1000.6562', '9999.9999') FROM DUAL;
--OUTPUT:1000.6562

10.全角转半角函数

(1)TO_SINGLE_BYTE(str)：该函数将全角转为半角。

SELECT TO_SINGLE_BYTE('HELLO WORLD') FROM DUAL;
--OUTPUT:HELLO WORLD

六、NULL函数

1.返回表达式为NULL的函数

(1)COALESCE(expr)函数：返回列表中第一个不为null的表达式。如果都为null，则返回一个null。

SELECT COALESCE(NULL,9-9,NULL) FROM DUAL;