ORACLE常用函数功能演示
文档撰写：孟祥新
写在前面：
ORACLE内置函数，相当于C语言中的库函数，提供了常规数据库编程中所需的绝大多数基本功能，并且通过函数的组合或嵌套使用，可以发挥其强大的功能。由于这些内置函数都是由ORACLE公司的编程大家所写，而且其执行代码是内嵌在ORACLE数据库系统中的，因此其执行效率极高。
在数据库编程中能广泛使用ORACLE所提供的内置函数，将大大提高程序代码的执行效率。“站在高人的肩膀上，将使你变得更高”。
演示之前的环境设置：
为了在SQL*PLUS中直观地显示函数返回的结果，我将“头”信息先屏蔽掉。操作如下：
一、单行函数
1． 数值函数
（1） 三角函数
① SIN、ASIN、SINH（正弦、反正弦、双曲正弦）
② COS、ACOS、COSH（余弦、反余弦、双曲余弦）
③ TAN、ATAN、TANH（正切、反正切，双曲正切）
（2） 幂、方根及对数运算函数
① POWER(m, n)（取m的n次幂）
② SQRT(n)（取n的平方根）
③ EXP(n)（取自然对数底e的n次方根）
④ LN(n)（取n的自然对数）
⑤ LOG(m, n)（取以m为底n的对数）
以上函数功能演示如下：
（3） 数值处理函数
① ABS(n)（取绝对值）
② SIGN(n)（符号函数）
③ CEIL(n)（取不小于n的最小整数）
④ FLOOR(n)（取不大于n的最大整数）
⑤ ROUND(n, [m])（按m精度对n进行4舍5入）
⑥ TRUNC(n, [m])（按m精度对n进行截取）
⑦ MOD(m, n)（取m除以n的余数）
以上函数功能演示如下：
2． 字符处理函数
（1） 对字符串的大小写处理的函数
① INITCAP（功能：将字符串中每个单词的首字母，变换为大写。）
② UPPER（功能：将字符串中的所有字母，转换为大写。）
③ LOWER（功能：将字符串中的所有字母，转换为小写。）
以上函数功能演示如下：
（2） 对字符串进行处理的函数
① CONCAT（字符串连接函数，也可用 || 替代）
功能演示如下：
② ASCII（取字符的编码）
③ CHR（将编码转换为对应的字符）
以上函数功能演示如下：
④ LTRIM（去除左侧空格/指定字符）
⑤ RTRIM（去除右侧空格/指定字符）
⑥ TRIM（去除两侧的空格/去除指定字符）
以上函数去除空格功能演示如下：
以上函数去除指定字符功能演示如下：
请注意：在用TRIM去除字符串中指定字符（非空格）时的特殊用法。
⑦ LPAD（左侧添充空格/指定字符）
⑧ RPAD（右侧添充空格/指定字符）
以上函数功能演示如下：
⑨ LENGTH（取字符串的长度）
⑩ SUBSTR（截取子字符串）
用法格式：SUBSTR(string, start [,length])
功能说明：从字符串string的start位置开始向后截取length长度的子串
以上函数功能演示如下：
⑾ INSTR（查找子字符串的位置）
用法格式：INSTR(string, search_string [,n [,m]])
功能说明：从字符串string的第n个字符开始查找search_string第m次出现的位置，如果按条件没有找到字符串，则返回0
（注意：此函数返回的位置是以原字符串首字符所在位置为起始点的，首字符位置为1）
函数功能演示如下：
⑿ REPLACE（子串替换）
用法格式：REPLACE(string, search_string [,replace_string])
功能说明：将字符串string中的所有search_string都替换为replace_string，如果省略replace_string，则将字符串string中的所有search_string都去掉。
⒀ TRANSLATE（字符转换）
用法格式：TRANSLATE(string, search_set, replace_set)
功能说明：将string中出现在search_set中的字符转换为replace_set相应位置的字符。
以上函数功能演示如下：
3． 日期函数
① SYSDATE（取当前的数据库系统时间）
② ADD_MONTHS(加减指定的月份)
③ MONTHS_BETWEEN（取两个日期之间相隔的月数）
④ LAST_DAY（取指定日期所在月的最后一天）
以上函数功能演示如下：
⑤ ROUND(date, ‘format_string’)
功能：按日期掩码format_string的格式对给定的日期date进行舍/入。
⑥ TRUNC(date, ‘format_string’)
功能：按日期掩码format_string的格式对给定的日期date进行截取。
以上函数功能演示如下：
4． 类型转换函数
功能：在数值和字符，以及字符和日期之间进行数据类型的转换
说明：在ORACLE数据服务器，可以对数值形式的字符串隐式地转换为数值类型的数据，也可以将数值类型的数据隐式地转换为数值形式的字符串。但也可以进行如下的显式转换。
① TO_CHAR(n)（将数值n转换为字符类型）
② TO_NUMBER(‘string’)（将字符串string转换为数值类型）
知识扩展：常用进制的转换
|
③ TO_CHAR(date, ‘format_string’)（按照时间掩码format_string的格式要求，将日期型数据date转换为字符型数据）
④ TO_DATE(‘string’, ‘format_string’)（将字符型数据string按照时间掩码format_string的格式要求，转换为相应的日期型数据）
常用的时间格式掩码如下：
掩码元素 | 含义 |
YYYY | 四位数年份 （如：2005） |
YY | 二位数年份（如 05） |
Q | 季度（1-4） |
MM | 月份（01-12） |
WW | 年的星期数（1-53），其中第一星期为年的第一天至第七天 |
W | 月的星期数（1-5），其中第一星期为月的第一天至第七天 |
DDD | 年的日（1-366） |
DD | 月的日（1-31） |
D | 周的日（1-7），其中周日为1，周六为7 |
HH24 | 24小时制（0-23） |
MI | 分钟（0-59） |
SS | 秒（0-59） |
SSSSS | 自午夜之后的秒（0-86399） |
以上函数功能演示如下：
5． 空值转换函数
① NVL(expr1, expr2)
功能：如果 expr1 的计算结果为 null 值，则返回 expr2 的值。如果 expr1 的计算结果不是 null 值，则返回 expr1 的值
② NVL2(expr1, expr2, expr3)
功能：如果expr1非空，则返回expr2的值；如果expr1为空，则返回expr3的值。
③ NULLIF(expr1, expr2)
功能：比较两个表达式，如果相等返回空；如果不相等，返回第一个表达式的值。
④ COALESCE(expr1, expr2, ..., exprn)
功能：返回表达式列表中的第一个非空表达式的值。
6． 查看环境参数函数
（1） USER函数 (返回当前用户名)
（2） USERENV函数
① USERENV('ISDBA') （查看当前用户是否是DBA）
② USERENV('LANGUAGE') （查看环境所用的字符集）
③ USERENV('TERMINAL') （查看当前用户终端的标识）
以上函数用法演示如下：
select USERENV('ISDBA'), USERENV('LANGUAGE'), USERENV('TERMINAL') from dual;
（3） SYS_CONTEXT函数
说明：SYS_CONTEXT函数是在ORACLE 8i中新增的，在功能上比USERENV函数更加全面。
用法格式：SYS_CONTEXT('USERENV', '<parameter>')
根据参数parameter来返回相应的信息
① 登录验证类型
select SYS_CONTEXT('USERENV', 'AUTHENTICATION_TYPE') from dual;
② 当前登录数据库的用户名
select SYS_CONTEXT('USERENV', 'CURRENT_USER') from dual;
③ 当前登录客户端的操作系统用户名
select SYS_CONTEXT('USERENV', 'OS_USER') from dual;
④ 当前所在客户机名（包括所在工作组名）
select SYS_CONTEXT('USERENV', 'HOST') from dual;
⑤ 当前所在客户机/终端名
select SYS_CONTEXT('USERENV', 'TERMINAL') from dual;
⑥ 当前所在客户端的IP
select SYS_CONTEXT('USERENV', 'IP_ADDRESS') from dual;
⑦ 当前是否为DBA帐户
select SYS_CONTEXT('USERENV', 'ISDBA') from dual;
⑧ 当前的字符集
select SYS_CONTEXT('USERENV', 'LANGUAGE') from dual;
⑨ 当前所使用的网络协议
select SYS_CONTEXT('USERENV', 'NETWORK_PROTOCOL') from dual;
⑩ 当前所使用的日历
select SYS_CONTEXT('USERENV', 'NLS_CALENDAR') from dual;
⑾ 当前的货币单位
select SYS_CONTEXT('USERENV', 'NLS_CURRENCY') from dual;
⑿ 当前的日期格式
select SYS_CONTEXT('USERENV', 'NLS_DATE_FORMAT') from dual;
⒀ 当前的日期语言
select SYS_CONTEXT('USERENV', 'NLS_DATE_LANGUAGE') from dual;
⒁ 当前所在的地域
select SYS_CONTEXT('USERENV', 'NLS_TERRITORY') from dual;
⒂ 当前代理用户
select SYS_CONTEXT('USERENV', 'PROXY_USER') from dual;
7． DECODE函数
DECODE函数用法格式与说明：
格式： DECODE(col|expression, search1, result1 [, search2, result2 ] [, search3, result3 ] [, ......, ...... ] [, default] ) 说明： 第一个参数可是是表的列，也可以是一个表达式。 如果第一参数的值与search1相等，则函数返回result1的值； 如果第一参数的值与search2相等，则函数返回result2的值；以此类推。 如果第一参数的值与所有的search都不相等，则函数返回default的值。 |
此函数用法示例如下：
二、聚组函数
功能：对一组数据进行运算。
函数 | 功能描述 |
MAX | 最大值 |
MIN | 最小值 |
AVG | 平均值 |
COUNT | 数量值 |
SUM | 总计值 |
以上函数功能演示如下：
三、分析函数
分析函数简介：ORACLE从8.1.6版开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚组函数的不同之处是对于每个组返回多行值，而聚组函数对于每个组只返回一行值。
（ORACLE有一整套分析函数，功能极其强大，用法也非常灵活，关于这一块的功能演示，请待续……）