从零开始学习Oracle之函数

大家好，欢迎来到IT知识分享网。

Oracle提供了众多功能强大、方便易用的函数。使用这些函数，可以极大地提高用户对数据库的管理效率。Oracle中的函数包括：数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数等。

1.Oracle函数简介

函数表示对输入参数值返回一个具有特定关系的值，Oracle 提供了大量丰富的函数，在进行数据库管理以及数据的查询和操作时将会经常用到各种函数。通过对数据的处理，数据库功能可以变得更加强大，更加灵活地满足不同用户的需求。

2.数学函数

数学函数主要用来处理数值数据，主要的数学函数有：绝对值函数、三角函数(包括正弦 函数、余弦函数、正切函数、余切函数等）、对数函数、随机数函数等。在有错误产生时，数 学函数将会返回空值NULL。

• 绝对值函数ABS(x)——ABS（X）返回X的绝对值。
• 平方根函数SQRT(x)——SQRT(x)返回非负数x的二次平方根。
• 求余函数MOD(x,y)——MOD(x,y)返回x被y除后的余数，MOD()对于带有小数部分的数值也起作用，它返回除法运算后的精确余数。
• 获取整数的函数CEIL(x)——CEIL(x)返回不小于x的最小整数值。
• 获取整数的函数FLOOR(x)——FLOOR(x)返回不大于x的最大整数值，返回值转化为一个BIGINT。
• 获取随机数的函数 DBMS_RANDOM.RANDOM——DBMS RANDOM.RANDOM返回一个随机值。
• 获取随机数的函数DBMS RANDOM.VALUE (X,y)——DBMS_RANDOM.VALUE(x,y)函数产生x~y之间的随机数。
• 函数ROUND(x)——ROUND(x)返回最接近于参数x的整数，对x值进行四舍五入。
• 函数ROUND(x,y)——ROUND(x,y)返回最接近于参数x的数，其值保留到小数点后面y位，若y为负值，则将保留x值到小数点左边y位。
• 函数TRUNC(x,y)——TRUNC(x,y)返回被舍去至小数点后y位的数字x。若y的值为0，则结果不带有小数点或不带有小数部分。若y设为负数，则截去（归零）x小数点左起第y位开始后面所有低位的值。

注意：ROUND(x,y)函数在截取值的时候会四舍五入，而TRUNC(x,Y)直接截取值，并不进行四舍五入。

• 符号函数SIGN(x)——SIGN(x)返回参数的符号，x的值为负、零或正时返回结果依次为-1、0或1。
• 幂运算函数POWER（x,y)——POWER(x,y)函数返回x的y次乘方的结果值。
• 幂运算函数EXP(x)——EXP(x)返回e的x乘方后的值。
• 对数运算函数LOG（x,y）——LOG(x，y)返回以x为底y的对数。
• 对数运算函数LN（x）——LN(x)返回x的自然对数，即x相对于基数e的对数，参数x要求大于0。
• 正弦函数SIN(x)——SIN(x)返回x的正弦，其中x为弧度值。
• 反正弦函数ASIN(x)——ASIN(x)返回x的反正弦，即正弦为x的值。x的取值在-1～1的范围之内。
• 余弦函数COS（x)——COS(x)返回x的余弦，其中x为弧度值。
• 反余弦函数ACOS（x)——ACOS(x)返回x的反余弦，即余弦是x的值。x的取值在-1~1的范围之内。
• 正切函数TAN(x)——TAN(x)返回x的正切，其中x为给定的弧度值。
• 反正切函数ATAN(x)——ATAN(x)返回x的反正切，即正切为x的值。

3.字符串函数

字符串函数主要用来处理数据库中的字符串数据。

• 计算字符串长度的函数——LENGTH(str)返回值为字符串的字节长度。
• 合并字符串函数CONCAT(s1,s2)——CONCAT(sl,s2)返回结果为连接参数产生的字符串。
• 字符串搜索函数INSTR(s,x)——INSTR(s,x)返回x字符在字符串s的位置。
• 字母大小写转换函数——LOWER（str）可以将字符串str中的字母字符全部转换成小写字母。
• 字母大小写转换函数——UPPER（str)可以将字符串str中的字母字符全部转换成大写字母。
• 字母大小写转换函数——INITCAP(str)将输入的字符串单词的首字母转换成大写。如果不是两个字母连在一起，则认为是新的单词，类似a b a,b ab 这些情况，a和b都会转换成大写。
• 获取指定长度的字符串的函数SUBSTR(s，m，n)——SUBSTR(s，m，n)函数用于获取指定的字符串。其中参数s代表字符串，m代表截取的位置，n代表截取长度。
• 替换字符串的函数REPLACE(s1,s2,s3)——REPLACE（sl，s2,s3)是一个替换字符串的函数。其中参数s1表示搜索的目标字符串；s2表示在目标字符串中要搜索的字符串；s3是可选参数，用它替换被搜索到的字符串，如果不用该参数，表示从s1字符串中删除搜索到的字符串。
• 删除字符串首尾指定字符的函数LTRIM(s,n)——LTRIM(s，n)函数将删除指定的左侧字符。其中s是目标字符串，n是需要查找的字符。如果n不指定，则表示删除左侧的空格。
• 删除字符串首尾指定字符的函数RTRIM(s,n)——RTRIM(s,n)函数将删除指定的右则字符。其中s是目标字符串，n是需要查找的字符。如果n不指定，则表示删除右侧的空格。
• 删除指定字符串的函数TRIM（）——TRIM函数将删除指定的前缀或者后缀的字符，默认删除空格。具体的语法格式如下：

TRIM ([LEADING/TRAILING/BOTH][trim_character FROM]trim_source) 

其中LEADING指删除 trim_source 的前缀字符；TRAILING指删除trim_source的后缀字 符；BOTH指删除trim_source的前缀和后缀字符；trim_character指删除的指定字符，默认删除空格；trim_source 指被操作的源字符串。

• 字符集名称和ID互换函数——NLS_CHARSET_ID(string)函数可以得到字符集名称对应的ID。参数string表示字符集的名称。NLS_CHARSET_NAME(number)函数可以得到字符集ID对应的名称。参数number表示字符集的ID。

4.日期和时间函数

日期和时间函数主要用来处理日期和时间值，一般的日期函数除了使用DATE类型的参数外，也可以使用 TIMESTAMP类型的参数，但会忽略这些值的时间部分。

• 获取当前日期的函数——SYSDATE()函数获取当前系统日期。

使用日期函数获取指定格式的系统当前日期，输入语句如下：

• 获取当前时间的函数——SYSTIMESTAMPO函数获取当前系统时间，该时间包含时区信息，精确到微秒。返回类型为带时区信息的TIMESTAMP类型。
• 获取时区的函数——DBTIMEZONE函数返回数据库所在的时区。SESSIONTIMEZONE函数返回当前会话所在的时区。
• 获取指定月份最后一天的函数——LAST_DAY(date)函数返回参数指定日期对应月份的最后一天。
• 获取指定日期后一周的日期的函数——NEXT_DAY(date,char)函数获取指定日期向后一周的对应日期，char表示是星期几，全称和缩写都允许，但必须是有效值。
• 获取指定日期特定部分的函数——EXTRACT(datetime)函数可以从指定的时间中提取特定部分。例如，提取年份、月份或者时等。
• 获取两个日期之间的月份数的函数——MONTHS_BETWEEN(datel,date2)函数返回datel和date2之间的月份数。

5.转换函数

转换函数的主要作用是完成不同数据类型之间的转换。

• 字符串转ASCll类型字符串函数——ASCIISTR(char)函数可以将任意字符串转换为数据库字符集对应的 ASCI字符串。char为字符类型。
• 二进制转十进制函数——BINTONUMO函数可以实现将二进制转换成对应的十进制。
• 数据类型转换函数——在Oracle中，用户如果想把数字转换为字符或者字符转换为日期，通常使用CAST(expr astype_name)函数来完成。
• 数值转换为字符串函数——TOCHAR函数将一个数值型参数转换成字符型数据。具体语法格式如下：

TO_CHAR(n,[fmt[nlsparam]]) 

其中参数 n代表数值型数据；参数fmt代表要转换成字符的格式；nlsparam参数代表指定fmt的特征，包括小数点字符、组分隔符和本地钱币符号。

使用TO_CHAR函数把数值类型转换为字符串。输入语句如下：

使用TO_CHAR函数把日期类型转换为字符串类型。输入语句如下：

• 字符转日期函数——TODATE函数将一个字符型数据转换成日期型数据。具体语法格式如下：

TO_DATE(char[,fmt[,nlsparam]]) 

其中参数 char代表需要转换的字符串；参数 fmt代表要转换成字符的格式；nlsparam参 数控制格式化时使用的语言类型。

使用TODATE函数把字符串类型转换为日期类型。输入语句如下：

• 字符串转数字函数——TO_NUMBER函数将一个字符型数据转换成数字数据。具体语法格式如下：

TO_NUMBER(expr[,fmt[,nlsparam]]) 

其中参数expr代表需要转换的字符串；参数fmt代表要转换成数字的格式；nlsparam参数指定fmt的特征，包括小数点字符、组分隔符和本地钱币符号。

使用TO_NUMBER函数把字符串类型转换为数字类型。输入语句如下：

6.系统信息函数

Oracle中的系统信息函数有：返回登录名函数和返回会话以及上下文信息函数等。

• 返回登录名函数——USER函数返回当前会话的登录名。

• 返回会话以及上下文信息函数——USERENV函数返回当前会话的信息。具体的语法格式如下：

USERENV(parameter) 

当参数为Language时，返回会话对应的语言、字符集等；当参数为SESSION时，返回当前会话的ID；当参数为ISDBA时，返回当前用户是否为DBA。