ORACLE常用函数功能演示

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;

http://wenku.baidu.com/view/6eeff7c75fbfc77da269b104.html