4.4 Oracle常用系统函数
视频讲解：光盘\TM\lx\4\常用系统函数．mp4
SQL语言是一种脚本语言，它提供了大量内置函数，使用这些内置函数可以大大增强SQL语言的运算和判断功能。本节将对Oracle中的一些常用函数进行介绍，如字符类函数、数字类函数、日期和时间类函数、转换类函数、聚集类函数等。
4.4.1 字符类函数
字符类函数是专门用于字符处理的函数，处理的对象可以是字符或字符串常量，也可以是字符类型的列，常用的字符类函数有如下几种。
1. ASCII(c)函数和CHR(i)函数
ASCII（c）函数用于返回一个字符的ASCII码，其中参数c表示一个字符；CHR（i）函数用于返回给出ASCII码值所对应的字符，i表示一个ASCII码值。从这两个函数的功能中可以看出，它们二者之间具有互逆的关系，如图4.65所示。
图4.65 ASCII（c）函数
【例4.59】 分别求得字符“Z、H、D和空格”的ASCII值，具体代码及运行结果如下：
SQL> select ascii('Z') Z, ascii('H') H, ascii('D') D , ascii(' ') space
2 from dual;
说明
dual是Oracle系统内部提供的一个用于实现临时数据计算的特殊表，它只有一个列DUMMY，类型为VARCHAR2（1），后续相关内容若用到，将不再重复。
【例4.60】 对于例4.59中求得的ASCII值，使用CHR函数再返回其对应的字符，如图4.66所示。具体代码及运行结果如下：
图4.66 CHR（i）函数
SQL>select chr(90), chr(72), chr(68), (32)S from dual;
2. CONCAT（s1, s2）函数
该函数将字符串s2连接到字符串s1的后面，如果s1为null，则返回s2；如果s2为null，则返回s1；如果s1和s2都为空，则返回null。
【例4.61】 使用concat（）函数连接“Hello”和“World”两个字符串，具体代码及运行结果如下：
SQL>select concat('Hello', 'World! ')information from dual;
INFORMATION
------------------
Hello World!
3. INITCAP（s）函数
该函数将字符串s的每个单词的第一个字母大写，其他字母小写。单词之间用空格、控制字符、标点符号来区分。
【例4.62】 使用initcap（）函数转换字符串“oh my god! ”的输出，具体代码及运行结果如下：
SQL> select initcap('oh my god! ') information from dual;
INFORMATION
-----------------
Oh My God!
4. INSTR（s1, s2[, i][, j]）函数
该函数用于返回字符s2在字符串s1中第j次出现时的位置，搜索从字符串s1的第i个字符开始。当没有发现要查找的字符时，该函数返回值为0；如果i为负数，那么搜索将从右到左进行，但函数的返回位置还是按从左到右来计算。其中，s1和s2均为字符串；i和j均为整数，默认值为1。
【例4.63】 在字符串“oracle 11g”中，从第3个字符开始查询字符串“1”第2次出现的位置，具体代码及运行结果如下：
SQL> select instr('oracle 11g', '1',3,2) abc from dual;
ABC
--------------
9
5. LENGTH（s）函数
该函数用于返回字符串s的长度，如果s为null，则返回值为null。
【例4.64】 在SCOTT模式下，通过使用length（）函数返回雇员名称长度大于5的雇员信息及所在部门信息，具体代码如下：
SQL> select e.empno, e.ename, d.dname
2 from emp e inner join dept d
3 on e.deptno=d.deptno
4 where length(e.ename)>5;
本例运行结果如图4.67所示。
图4.67 雇员名称长度大于5的雇员信息
6. LOWER（s）函数和UPPER函数（s）
LOWER（s）函数和UPPER函数（s）分别用于返回字符串s的小写形式和大写形式，这两个函数经常出现在WHERE子句中。
【例4.65】 在HR模式下，在employees表中检索雇员名称以字母“a”开头的员工信息，并将first_name字段的值转换为小写，将last_name字段的值转换为大写，具体代码如下（实例位置：光盘\TM\sl\4\11）。
SQL> select employee_id, lower(first_name), upper(last_name) from employees where
lower(first_name) like 'a%';
本例运行结果如图4.68所示。
图4.68 大小写转换
7. LTRIM（s1, s2）函数、RTRIM（s1, s2）函数和TRIM（s1, s2）函数
这3个函数分别用来删除字符串s1左边的字符串s2、删除字符串s1右边的字符串s2、删除字符串s1左右两端的字符串s2。如果在这3个函数中不指定字符串s2，则表示去除相应方位的空格。
【例4.66】 使用LTRIM、RTRIM和TRIM函数分别去掉字符串“####East####”、“East ”和“####East###”中左侧“#”、右侧空格和左右两侧的“#”，具体代码及运行结果如下：
SQL>select ltrim('####East####', '#'), rtrim('East '), trim('#'from'####East###')from dual;
LTRIM('# RTRI TRIM
------------- ------- ---
East#### East East
8. REPLACE（s1, s2[, s3]）函数
该函数使用s3字符串替换出现在s1字符串中的所有s2字符串，并返回替换后的新字符串，其中，s3的默认值为空字符串。
【例4.67】 使用replace（）函数把字符串“Bad Luck Bad Gril”中的“Bad”字符串用“Good”替换掉，具体代码及运行结果如下：
SQL> select replace('Bad Luck Bad Gril', 'Bad', 'Good') from dual;
REPLACE('BADLUCKBAD
--------------------------------
Good Luck Good Gril
9. SUBSTR（s, i, [j]）函数
该函数表示从字符串s的第i个位置开始截取长度为j的子字符串。如果省略参数j，则直接截取到尾部。其中，i和j为整数。
【例4.68】 使用substr（）函数在字符串’MessageBox’中从第8个位置截取长度为3的子字符串，具体代码及运行结果如下：
SQL> select substr('MessageBox',8,3) from dual;
SUB
-----
Box
4.4.2 数字类函数
数字类函数主要用于执行各种数据计算，所有的数字类函数都有数字参数并返回数字值。Oracle系统提供了大量的数字类函数，这些函数大大增强了Oracle系统的科学计算能力。下面就列出Oracle系统中常见的数字类函数，如表4.6所示。
表4.6 数字类函数及其说明
在上表中列举了若干三角函数，这些三角函数的操作数和返回值都是弧度，而不是角度，这一点需要读者注意。接下来，对表4.6中常用的几个函数进行举例说明。
1. CEIL（n）函数
该函数返回大于或等于数值n的最小整数，它适合于一些比较运算。
【例4.69】 使用ceil（）函数返回3个指定小数的整数值，具体代码及运行结果如下：
SQL> select ceil(7.3), ceil(7), ceil(-7.3) from dual;
CEIL(7.3) CEIL(7) CEIL(-7.3)
--------------- --------- ---------
8 7 -7
2. ROUND(n1, n2)函数
该函数返回舍入小数点右边n2位的n1的值，n2的默认值为0，这会返回小数点最接近的整数。如果n2为负数，就舍入到小数点左边相应的位上，n2必须是整数。
【例4.70】 使用round（）函数返回PI为两位小数的值，具体代码及运行结果如下：
SQL> select round(3.1415926,2) from dual;
ROUND(3.1415926,2)
------------------------
3.14
3. POWER(n1, n2)函数
该函数返回n1的n2次方。其中n1和n2都为整数。
【例4.71】 使用power（）函数计算2的3次方的值，具体代码及运行结果如下：
SQL> select power(2,3) from dual;
POWER(2,3)
---------------
8
4.4.3 日期和时间类函数
在Oracle 11g中，系统提供了许多用于处理日期和时间的函数，通过这些函数可以实现计算需要的特定日期和时间，常用的日期和时间函数如表4.7所示。
表4.7 日期和时间类函数
日期类型的默认格式是“DD-MON-YY”，其中DD表示两位数字的“日”, MON表示3位数字的“月份”。YY表示两位数字的“年份”，例如，“01-10月-11”表示2011年10月1日。下面看几个常用函数的具体应用。
1. SYSDATE()函数
该函数返回系统当前的日期。
【例4.72】 使用sysdate（）函数返回系统当前的日期，具体代码及运行结果如下：
SQL>select sysdate as系统日期from dual;
系统日期
-------------
29-9月 -11
2. ADD_MONTHS(d, i)函数
该函数返回日期d加上i个月之后的结果。其中，i为任意整数。
【例4.73】 使用ADD_MONTHS函数在当前日期下加上6个月，并显示其值，具体代码及运行结果如下：
SQL> select ADD_MONTHS(sysdate,6) from dual;
ADD_MONTHS(SYS
--------------------------
29-3月 -12
4.4.4 转换类函数
在操作表中的数据时，经常需要将某个数据从一种类型转换为另外一种数据类型，这时就需要转换类型函数。比如常见的，有把具有“特定格式”字符串转换为日期、把数字转换成字符等。常用的转换函数如表4.8所示。
表4.8 转换类函数
下面来看几个常用转换函数的具体应用。
1. TO_CHAR()函数
该函数实现将表达式转换为字符串，format表示字符串格式。
【例4.74】 使用to_char（）函数转换系统日期为“YYYY-MM-DD”格式，具体代码及运行结果如下（实例位置：光盘\TM\sl\4\12）。
SQL>select sysdate as默认格式日期，to_char(sysdate, 'YYYY-MM-DD')as转换后日期
from dual;
默认格式日期 转换后日期
----------------- ---------
29-9月 -11 2011-09-29
2. TO_NUMBER(s[, format[lan]])函数
该函数将返回字符串s代表的数字，返回值按照format格式进行显示，format表示字符串格式，lan表示所使用的语言。
【例4.75】 使用to_number（）函数把十六进制数“18f”转换为十进制数，具体代码及运行结果如下：
SQL>select to_number('18f', 'xxx')as十进制数from dual;
十进制数
-------------
399
4.4.5 聚合类函数
使用聚合类函数可以针对一组数据进行计算，并得到相应的结果。比如常用的操作有计算平均值、统计记录数、计算最大值等。Oracle 11g所提供的主要聚合函数如表4.9所示。
表4.9 聚合函数
在实际的应用系统开发中，聚合函数应用比较广泛，比如统计平均值、记录总数等。下面来看一个例子。
【例4.76】 在SCOTT模式下，使用count（）函数计算员工总数，使用avg（）函数计算平均工资，具体代码及运行结果如下：
SQL>select count(empno)as员工总数，round(avg(sal),2)as平均工资from emp;
员工总数 平均工资
--------------- ---------
14 2073.21