1.1.3　查询语句中的单行函数

在SELECT语句中不仅可以对数据进行前面讲述的各种运算，还可以把数据作为函数的参数，进行其他的计算。所谓单行函数，就是分别作用于查询结果中的每一行，对于每一行，分别得到一个计算结果。这些函数都是Oracle提供的系统函数，用户可以在SELECT语句或其他SQL语句中直接使用它们。Oracle提供的函数很多，这里仅仅把最常用的函数进行分类介绍。在函数中处理的数据可以是某个列的列值，也可以是某个表达式，在举例时不再单独说明。

1.字符串处理函数

顾名思义，这类函数以字符串为处理对象，处理的结果是另一字符串或者一个数字。

（1）CONCAT函数

这个函数的参数是两个字符串，计算的结果是将两个字符串连接在一起，生成一个新的字符串。例如，concat（‘Hello，’，‘World’）的结果为Hello, World。

（2）CHR与ASCII函数

CHR函数的参数是一个正整数，它将这个正整数作为ASCII码，返回对应的字母。例如chr（65）的结果为A。ASCII函数的作用正好相反，它以一个字符为参数，返回这个字符对应的ASCII码。例如ASCII（‘A’）的结果为65。

（3）INSTR函数

这个函数在一个字符串中查找另一个字符串，如果找到，则返回出现的位置，否则返回0，位置的编号从1开始。这个函数的语法格式为：

instr（字符串，子字符串，start, occurrence）

其中前两个参数是必需的，这个函数在第一个参数中查找第二个参数，得到的结果是第二个参数在第一个参数中出现的位置，如果没有找到，则返回0。后两个参数是可选的，参数start指定从第一个参数的什么位置开始查找，默认从1开始，即第一个字符。参数occurrence指定查找子字符串的第几次出现。例如，要在部门名称中查找字符串‘TI’，相应的SELECT语句为：

SQL＞SELECT dname, instr（dname，'TI'）AS location FROM dept；

DNAME LOCATION

ACCOUNTING 7

RESEARCH 0

SALES 0

OPERATIONS 6

（4）LENGTH函数

这个函数的作用是求得一个字符串的长度。例如length（‘Hello’）的结果为5。

（5）LOWER和UPPER函数。

这两个函数的作用是进行字符串的大小写转换，它们的参数都是一个字符串。其中LOWER函数将字符串中的字母转换为对应的小写字母，UPPER函数将字符串中的字母转换为对应的大写字母。例如，函数lower（‘Hello’）的结果为hello，而函数upper（‘Hello’）的结果为HELLO。

（6）LPAD与RPAD函数

这两个函数的作用是在字符串中填充指定的字符，使字符串达到指定的长度。LPAD函数从左边填充，RPAD函数从右边填充，处理的结果是得到一个新的字符串。这两个函数的语法格式为：

LPAD（字符串，长度，填充字符）

RPAD（字符串，长度，填充字符）

这两个函数在字符串中填充指定的字符，使其达到指定的长度，默认是填充空格。如果指定的长度比字符串本来的长度小，则将字符串截断，只保留新的长度。例如，对部门名称分别进行左填充和右填充，使其长度为10，填充的字符分别是“*”和“#”，对应的SELECT语句为：

SQL＞SELECT lpad（dname，12，'*'）AS左填充，rpad（dname，12，'#'）AS右填充FROM dept；

这条语句执行的结果为：

左填充右填充

**ACCOUNTING ACCOUNTING##

**RESEARCH RESEARCH####

*SALES SALES#######

**OPERATIONS OPERATIONS##

（7）LTRIM、RTRIM函数和TRIM函数

这三个函数的作用是去掉字符串左边或右边连续的空格，并得到一个新的字符串。例如，函数ltrim（‘Hello’）的结果为Hello, rtrim（‘Hello’）的结果为Hello。

TRIM函数的作用是同时去掉字符串左边和右边的连续空格，它相当于对字符串先执行LTRIM函数，再执行RTRIM函数，或者先执行RTRIM函数，再执行LTRIM函数。

（8）REPLACE函数

这个函数的作用是在一个字符串中查找另一个字符串，并将找到的字符串用第三个字符串代替。这个函数的语法格式为：

replace（字符串，子字符串，替换字符串）

如果在字符串没有找到子字符串，则不做任何处理，如果找到，则用替换字符串代替。如果没有指定替换字符串，则将找到的子字符串从原字符串中删除。例如，假设要在部门名称中查找字符串TI，并将其替换为Hello，为了进行对比，我们将替换前后的字符串都显示出来。对应的SELECT语句为：

SQL＞SELECT dname, replace（dname，'TI'，'Hello'）AS new_str FROM dept；

字符串替换的结果为：

DNAME NEW_STR

ACCOUNTING ACCOUNHelloNG

RESEARCH RESEARCH

SALES SALES

OPERATIONS OPERAHelloONS

（9）SUBSTR函数

这个函数的作用是根据指定的开始位置和长度，返回一个字符串的子字符串。它的语法格式为：

substr（字符串，开始位置，长度）

位置编号从1开始。开始位置可以是正整数或负整数。如果是负整数，则从字符串的右边开始数。长度是可选的，如果缺省，则返回从开始位置到字符串末尾的所有字符。例如：

substr（'Hello'，2，3）的结果为ell

substr（'Hello'，-3，2）的结果为ll

substr（'Hello'，-3）的结果为ello

2.数学函数

数学函数的处理对象是数字型数据，处理的结果一般也是数字型数据。

（1）ABS函数

这个函数的作用是求得一个数字的绝对值。

（2）CEIL和FLOOR函数

这两个函数都以一个数字为参数，CEIL函数返回大于或等于这个数字的最小整数。FLOOR函数返回小于或等于这个数字的最大整数。例如：

ceil（-15.6）的结果是-15，floor（-15.6）的结果是-16。

ceil（15.6）的结果是16，floor（15.6）的结果是15。

（3）MOD函数

这个函数有两个参数，结果是两个数相除所得的余数。

（4）ROUND和TRUNC函数

ROUND函数的作用是对数据进行四舍五入计算。这个函数的语法结构为：

round（数据，舍入位置）

其中舍入位置可以是正整数，也可以是负整数。如果是正整数，则从小数点开始向右数，一直到舍入位置，从这一位开始四舍五入。如果是负整数，则从小数点开始向左数，然后进行四舍五入。例如：

round（49.456，2）的结果为49.46

round（89.456，-1）的结果为90

round（89.456，-2）的结果为100

TRUNC函数的用法与ROUND函数类似，只不过它的功能是对数据进行截取运算，只舍不入，也就是把一个数据的指定位之后的数字全部舍去。例如：

trunc（49.456，2）的结果为49.45

trunc（49.456，-1）的结果为40

3.日期型函数

日期型函数的处理对象是日期型数据，处理的结果一般也是日期型数据。

（1）ADD_MONTHS函数

这个函数在某个日期的基础上，加上一个指定的月数，返回一个新的日期。它的格式为：

ADD_month（日期，月数）

这个函数是在指定的日期上加上若干个整月数，所以日期中的日应该保持不变。但是指定的日期如果是当月的最后一天，函数会做相应的调整，以保证返回的日期也是当月的最后一天。例如：

ADD_months（to_date（'12-05-10'，'dd-mm-yy'），6）的结果为"12-11月-10"；

ADD_months（to_date（'31-01-10'，'dd-mm-yy'），1）的结果为"28-2月-10"

ADD_months（to_date（'28-02-10'，'dd-mm-yy'），1）的结果为"31-3月-10"

在ADD_MONTHS函数中，不能直接使用类似“12-05-10”这样的日期型数据，因为Oracle把这样的数据是当做字符串来处理的，所以首先要调用to_date函数将它转换为真正的日期型数据。上述第一个例子容易理解。在第二个例子中，因为31日是2010年1月的最后一天，所以加上一个月后，得到2月的最后一天，即28日。同理，在2010年2月28日上加上一个月后，得到3月的最后一天，即3月31日。函数返回的结果可能因系统的不同而显示不同的格式，因为不同系统采用的日期格式可能不同。

（2）LAST_DAY函数

这个函数返回指定日期所在月份的最后一天。例如：

last_day（to_date（'01-02-10'，'dd-mm-yy'））的结果为"28-2月-10"。

（3）MONTHS_BETWEEN函数

这个函数有两个参数，都是日期型数据，返回的结果是两个日期之间相差的月数。这个函数的语法格式为：months_between（日期1，日期2）。例如：

months_between（to_date（'01-05-10'，'dd-mm-yy'），to_date（'01-05-09'，'dd-mm-yy'））

结果为12

months_between（to_date（'01-05-10'，'dd-mm-yy'），TO_DATE（'12-05-09'，'dd-mm-yy'））结

果为11.6451613

如果两个日期中的日相同，或者都是当月的最后一天，则返回结果是一个整数，否则将返回一个小数。第一个参数如果比第二个参数小，则返回的结果为负数。

（4）NEXT_DAY函数

这个函数有两个参数，一个是日期，一个是与星期几对应的整数，返回的结果是这个日期之后最近的星期几所对应的日期。函数的调用格式为：

next_day（日期，整数）

例如，假设2010年4月28日是星期三，则最近的星期五应该是2010年4月30日，最近的星期二应该是2010年5月27日。注意星期的编号方法是：星期天为1，星期一为2，依此类推。例如：

next_day（to_date（'28-04-10'，'dd-mm-yy'），6）的结果为"30-4月-10"

next_day（to_date（'28-04-10'，'dd-mm-yy'），3）的结果为"04-5月-10"

（5）ROUND函数

这个函数对指定的日期进行四舍五入处理。它有两个参数，一个是日期，一个是表示日期某个组成部分的格式字符串。函数的语法格式为：

round（日期，格式字符串）

其中格式字符串用于指定从日期的哪一部分开始四舍五入。Oracle支持的格式字符串包括yy（或yyyy）、mm、dd、hh，分别表示从年、月、日、时位进行四舍五入。例如：

round（to_date（'28-08-10'，'dd-mm-yy'），'mm'）的结果为"01-9月-10"

round（to_date（'12-08-10 13：50'，'dd-mm-yy hh24：mi'），'dd'）结果为"13-8月-10"

在第一个例子中，指定从月开始四舍五入，由于28日已经过了该月的一半，故该位向前进位。在第二个例子中，指定从日开始四舍五入，由于13时已经超过当天中午12时，故向前进位，使12日变为13日。

（6）SYSDATE函数

这是个很常用的函数，它用来获得系统当前时间。在有些日志操作中，常常需要记录当前时间，使用这个函数是很方便的。这个函数没有任何参数。这个函数返回的时间可以精确到秒，但在显示时可能只显示到日，根据系统的不同显示格式有所变化。如果希望得到时、分、秒，则需要通过to_char函数进行数据类型转换。例如：

SQL＞SELECT sysdate FROM dual；

SYSDATE

26-4月-10

（7）TRUNC函数

这个函数的功能类似于ROUND函数，但是它不进行四舍五入，而是从指定位开始，截断其后面的部分，它的语法格式与ROUND函数相同。例如：

trunc（to_date（'28-08-10 23：50'，'dd-mm-yy hh24：mi'），'dd'）结果为"28-8月-10"

trunc（to_date（'28-08-10'，'dd-mm-yy'），'mm'）结果为"01-8月-10"

4.类型转换函数

在进行数据处理时，常常需要对数据进行类型转换。数据类型转换主要涉及字符型、数字型和日期型数据之间的相互转换，涉及的函数有to_char、to_date和to_number。

（1）to_char

to_char函数的作用是将一个日期型或者数字型数据转换为字符串。如果操作对象是日期型数据，这个函数的语法格式为：

TO_CHAR（日期，格式字符串）

其中格式字符串是由日期格式元素和隔离符号组成的字符串，用来规定转换的格式。例如，下列语句将员工表中员工的受聘日期按照指定的格式转换为字符串：

SELECT ename, to_char（hiredate，'yyyy-mm-dd day'）FROM emp WHERE empno=7902；

ENAME TO_CHAR（HI

FORD 1981-12-03星期四

又如，下列SELECT语句获取当前系统时间，然后按照指定的格式进行转换：

SELECT to_char（sysdate，'yyyy-mm-dd hh24：mi：ss'）FROM dual；

TO_CHAR（SYSDATE，'YY

2010-05-26 12：17：25

常用的日期格式元素如表1.4所示。

如果没有指定格式字符串，则按照当前系统默认的时间格式转换为字符串。

如果操作对象是数字型数据，to_char函数的语法格式为：

to_char（数字，格式字符串）

其中格式字符串是由数字格式元素和小数点、分隔符组成的字符串，用来控制转换的格式。这种转换主要用在财务报表中。例如：

to_char（5432，'$9，999.99'）的结果为'$5，432.00'

常用的数字格式元素如表1.5所示。

（2）to_date函数

这个函数的作用是把一个字符串转换为一个日期型数据，它有两个参数，处理的结果是一个日期型数据。这个函数的语法格式为：

to_date（字符串，格式字符串）

其中格式字符串由表1.4中的格式元素组成，它的作用是把字符串中用分隔符分开的不同部分解释成一个日期的不同部分。例如：

to_date（'12-09-2010'，'dd-mm-yyyy'）结果为"12-9月-10"

通过格式字符串，把字符串中的12解释为日，09解释为月，2010解释为年，然后将这个字符串转换为一个日期型数据。格式字符串中的每部分对字符串中的每部分是一一对应地进行解释的，所以日期字符串中的每一部分对于格式字符串中的对应部分来说，必须是合法的数据。例如：

to_date（'12-02-2010'，'dd-mon-yyyy'）

将得到一个错误的结果，原因是格式字符串中的mon试图将字符串中的02解释为某个月份的缩写，而02并不是某个月份缩写。还要注意的是，转换后得到的日期格式与函数指定的格式并不一定相同，因为格式字符串是用来解释字符串中的不同部分的，而日期的显示格式依赖于当前系统的日期格式。

（3）to_number

这个函数的作用是把一个字符串转换为数字，它有两个参数，处理的结果是一个数字型数据。这个函数的语法格式为：

to_number（字符串，格式字符串）

其中格式字符串包含表1.5中所介绍的格式元素，它把字符串中的$以及用逗号、小数点分开的不同部分分别进行解释，它的分隔方式与字符串不同部分的分隔方式应该是一致的。例如：

to_number（'$1，000.50'，'$999，999.99'）的结果为1000.5

to_number（'$1000.50'，'$999，999.99'）的结果将出错

第二个例子出错的原因是字符串中的数字字符并没有每三位逗号隔开，而格式字符串却要按这种方式解释它。另外，格式字符串中的9或0的位数不应少于字符串中数字字符的位数。