8.1 描述和使用SQL中的字符、数字和日期函数

SQL函数可以广义区分为计算并返回数据集中各行值的函数和为所有行返回一个集合值的函数。本节将先讨论字符大小写转换函数，然后分别讨论字符操作函数、数字函数和日期函数。

8.1.1 定义函数

函数是接收输入参数(输入参数是可选的)，执行运算并返回单个值的程序。每次执行时，函数只返回一个值。

三个重要组成部分构成了函数定义的基础。第一个是输入参数列表。它指定零个或者多个参数，这些参数可以作为输入传递给函数处理。这些实参或者形参是可选的，具有不同的数据类型。第二个是返回值的数据类型。每次执行时，函数只返回一个预定数据类型的值。第三个封装函数执行处理的细节，包含可选操作输入参数、执行计算和运算并生成返回值的程序代码。

函数通常被称为黑盒(black box)，它获取输入，执行计算并返回值。不要求关注实现函数的细节，而是应该关注内置函数提供的功能。

函数可以嵌套在其他函数内，例如F1(x, y, F2(a, b), z)，其中F2有两个输入参数：a和b，它形成提交到F1的4个参数中的第三个参数。函数可以作用于任何数据类型，最常用的是字符、日期和数字数据。函数的操作数可以是列或者表达式。

例如，考虑用来计算某个人年龄的函数。AGE函数使用一个日期输入参数，这个参数是某人的生日。AGE函数返回的结果是表示此人年龄的数字。通过黑盒计算得到当前日期和生日输入参数之间年份的差值。

8.1.2 函数类型

可以将函数广义地分为两大类，即单行函数和多行函数。这两种函数的差别对于理解使用函数的较长上下文至关重要。

1．单行函数

有几种类型的单行函数，包括字符、数字、日期、转换函数和通用函数。这些函数每次只作用于数据集的一行。如果查询选择10行，函数就会执行10次，每行一次，并将来自一行的值作为函数的输入。

下面这个查询选择REGIONS表的两列以及一个对REGION_NAME列使用LENGTH函数的表达式：

        select region_id, region_name, length(region_name) from regions;

REGIONS表中的每行都要计算REGION_NAME列的长度，因此函数被执行了4次，每行返回一个结果。

单行函数操作行中的数据项，提取并且格式化它们以方便显示。单行函数的输入值可以是用户指定的常量或者字面值、列数据、变量或者由其他嵌套的单行函数提供的表达式。嵌套单行函数是一种常用方法。函数可以依据输入参数返回不同数据类型的值。前面的查询显示了LENGTH函数如何接受一个字符输入参数，返回数字输出。

除了包含在SQL查询的SELECT列表中之外，还可以在WHERE和ORDER BY子句中使用单行函数。

2．多行函数

顾名思义，这类函数每次作用于多行。通常使用多行函数来计算数字列值的和或者平均值，或者计算集合中记录的总数。这些函数有时称为聚合函数或者分组函数，第9章将讨论它们。

8.1.3 使用大小写转换函数

包括应用程序接口和批处理程序在内的众多数据源都将字符数据保存在表中。通常假设以大小写一致的方式输入字符数据，但这种假设并不可靠。字符大小写转换函数有两个重要作用。首先，可以使用它们来修改字符数据项的外观来进行显示；其次，可以使用它们来显示一致的字符数据项以便于比较运算。对于搜索字符串而言，使用一致的大小写格式比测试匹配字符串的大小写字符更简单。记住，这些函数没有更改保存在表中的数据。它们仍然是只读SQL查询的一部分。

这些函数所需的字符串参数可能是任何字符串字面值、字符列值、字符表达式或者数字或日期值(会被隐式转换为字符串)。

1．LOWER函数

LOWER函数用小写形式替换字符串中的大写字符。其语法是LOWER(s)。下列查询演示了该函数的用法：

        select lower(100+100), lower('SQL'), lower(sysdate) from dual;

假设系统的当前日期是17-DEC-2014。返回字符串“200”、“sql”和“17-dec-2014”。注意根据会话参数，日期结果可能不同。在执行LOWER函数之前先计算数字和日期表达式，并隐式转换为字符数据。

下列条件中使用LOWER函数来定位LAST_NAME字段中字母“u”和“r”彼此相邻的记录。

        select first_name, last_name, lower(last_name) from employees
        where lower(last_name) like '%ur%';

不使用大小写转换函数，再写一个查询，让它返回相同的结果。代码可能如下所示：

        select first_name, last_name from employees
        where last_name like '%ur%' or last_name like '%UR%'
        or last_name like '%uR%' or last_name like '%Ur%'

这个查询可以运行，但比较麻烦，随着搜索字符串长度的增加，所需OR子句的数量会显著增加。

2．UPPER函数

UPPER函数与LOWER函数逻辑相反，它用大写形式替换字符串中的小写字符。其语法是UPPER (s)。下面的查询说明了这个函数的用法：

        select * from countries where upper(country_name) like '%U%S%A%';

这个查询从COUNTRIES表中提取COUNTRY_NAME值依次包含字母“U”、“S”和“A”的行。

3．INITCAP函数

INITCAP函数将字符串转换为首字母大写的形式。它通常用于数据表示。字符串中每个单词的第一个字母都被转换为大写形式，而每个单词余下的字母被转换为小写字母形式。单词通常指相邻字符组成的字符串，单词之间用空格或者下划线分开，但有些字符(如百分比符号(%)、感叹号(! )或者美元符号($))也都是有效的单词分隔符。标点符号和特殊字符也是有效的单词分隔符。

INITCAP函数只能使用一个参数。其语法是INITCAP(s)。下面的查询说明了这个函数的用法：

        select initcap('init cap or init_cap or init%cap') from dual

该查询返回Init Cap Or Init_Cap Or Init%Cap。

练习8-1 使用大小写转换函数

构造一个查询，从EMPLOYEES表中检索所有FIRST_NAME和LAST_NAME值的列表，其中FIRST_NAME包含字符串“li”。

(1) 启动SQL Developer或SQL* Plus，连接到HR模式。

(2) WHERE子句必须将FIRST_NAME列值与包含字符串“li”的所有可能大小写组合的字符模式进行比较。因此，如果某行的FIRST_NAME包含字符串“LI”、“Li”、“lI”或者“li”，就必须检索该行。

(3) LIKE运算符用于字符匹配，使用由OR关键字分开的四个WHERE子句可以提取这4种组合。然而，大小写转换函数可以简化这些条件。如果在FIRST_NAME列上使用LOWER函数，那么使用一个WHERE子句条件可以完成比较，也可以使用UPPER或者INITCAP函数。

(4) 执行下列语句返回包含字符“li”的员工名字：

        select first_name, last_name from employees where lower(first_name) like '%li%';

8.1.4 使用字符操作函数

Oracle某些最强大的功能是字符操作函数。它们在数据操作方面的有用性几乎无可匹敌，许多经验丰富的技术专家都使用这些函数来处理数据项。嵌套这些函数也很常见。通常使用串联运算符(||)而不是CONCAT函数。LENGTH、INSTR、SUBSTR和REPLACE函数经常一起使用，RPAD、LPAD和TRIM也是一样。

1．CONCAT函数

CONCAT函数连接两个字符字面值、列或者表达式，从而生成一个更大的字符表达式。CONCAT函数有两个参数。其语法是CONCAT(s1, s2)，其中s1和s2表示字符串字面值、字符列值或者产生字符值的表达式。下面的查询说明了这个函数的用法：

        select concat('Today is:', SYSDATE) from dual;

CONCAT函数的第二个参数是SYSDATE，它返回当前系统日期。这个值被隐式转换为字符串，第一个参数中的字面值连接这个字符串。如果系统日期是17-DEC-2014，查询就返回字符串“Today is 17-DEC-2014”。

请思考如何使用CONCAT函数连接三个项，但只返回一个字符串。因为CONCAT只使用两个参数，因此只能使用它连接两项。解决方案是在一个CONCAT函数内嵌套另一个CONCAT函数，如下所示：

        select concat('Outer1 ', concat('Inner1', ' Inner2')) from dual;

第一个CONCAT函数有两个参数：第一个是字面值“Outer1”，第二个是嵌套的CONCAT函数。第二个CONCAT函数有两个参数：第一个是字面值“Inner1”，第二个是字面值“Inner2”。这个查询会生成下面的字符串：Outer1 Inner1 Inner2。后面的章节将详细讨论嵌套函数。第7章讨论的连接运算符||也可以用于连接这些项。

2．LENGTH函数

LENGTH函数返回组成字符串的字符数。空格、制表符和特殊字符都被LENGTH函数计算在内。LENGTH函数只有一个参数。其语法是LENGTH(s)。考虑下列查询：

        select * from countries where length(country_name) > 10;

LENGTH函数用于从COUNTRIES表中提取长度大于10个字符的COUNTRY_NAME值。

3．LPAD和RPAD函数

LPAD和RPAD函数也被称为左填充和右填充函数，它们分别返回在给定字符串左边或右边填充指定数量的字符后形成的字符串。用于填充的字符串包括字符字面值、列值、表达式、空格(默认)、制表符和特殊字符。

LPAD和RPAD函数有三个参数。其语法是LPAD(s, n, p)和RPAD(s, n, p)，其中s表示源字符串，n表示返回字符串的最终长度，p 指定用于填充的字符串。如果使用LPAD函数，填充字符串p就会被添加到源字符串s的左边，直到其长度为n为止。如果使用RPAD函数，填充字符串p就被添加到源字符串s的右边，直到其长度为n为止。注意，如果参数n小于或者等于源字符串s的长度，就不会添加任何字符，此时只返回源字符串s的前n个字符。考虑图8-1中所示的查询。

第一个查询没有填充数据，结果的可读性不如第二个查询的输出。RPAD用于在必要时给first_name和last_name的串联值添加空格，直至每个名字达18个字符长，而LPAD在薪水值的开始部分添加空格，直至每个薪水值达6个字符长。

4．TRIM函数

TRIM函数从字符值的开头或者结尾删除一些字符，从而生成一个更简短的字符项。TRIM函数使用的参数由一个强制组成部分和一个可选组成部分构成。其语法是TRIM([trailing|leading|both] trimstring from s)。被修整的字符串(s)是强制的。下面几点说明了使用该函数的规则：

● TRIM(s)删除输入字符串两边的空格。

● TRIM(trailing trimstring from s)从字符串s的结尾删除所有trimstring(如果存在的话)。

● TRIM(leading trimstring from s)从字符串s的开头删除所有trimstring(如果存在的话)。

● TRIM(both trimstring from s)从字符串s的开头和结尾删除所有trimstring(如果存在的话)。

        select trim(both '*' from '****Hidden****'),
        trim(leading '*' from '****Hidden****'),
        trim(trailing '*' from '****Hidden****') from dual;

前面的查询返回字符串“Hidden”、“Hidden****”和“****Hidden”。注意，虽然只指定一个修整字符，但如果它们继续存在，就会进行多次删除。

5．INSTR函数（In-string）

INSTR函数确定搜索字符串在给定字符串内的位置。它返回数字位置，在这个位置上，搜索字符串开始第 n 次出现(相对于指定的起始位置而言)。如果搜索字符串不存在，那么INSTR函数就返回零。

INSTR函数使用两个可选参数和两个强制参数。其语法是INSTR(source string, search string, [search start position], [nth occurrence])。search start position的默认值是1或者source string的开头。nth occurrence的默认值是1或者第1次出现。考虑下面的查询：

        查询1: select instr('1#3#5#7#9#', '#') from dual;
        查询2: select instr('1#3#5#7#9#', '#' ,5) from dual;
        查询3: select instr('1#3#5#7#9#', '#', 3, 4) from dual;

查询1从源字符串的开始处搜索哈希符第1次出现的位置，返回位置2。查询2从源字符串的位置5处开始搜索哈希符，发现哈希符后续出现在位置6上。查询3从源字符串的位置3处开始搜索哈希符，发现位置10是哈希符第4次出现的位置。

6．SUBSTR函数（Substring）

SUBSTR函数从给定源字符串中给定的位置开始，提取指定长度的子字符串。如果起始位置大于源字符串的长度，就会返回 null。如果从给定起始位置提取的字符数大于源字符串的长度，返回的部分就是从起始位置到字符串结尾的子字符串。

SUBSTR函数有三个参数，前两个是强制的。其语法是SUBSTR(source string, start position, [number of characters to extract])。要提取的默认字符数为从start position到source string结尾的字符数。考虑下面的查询：

        查询1: select substr('1#3#5#7#9#', 5) from dual;
        查询2: select substr('1#3#5#7#9#', 5, 3) from dual;
        查询3: select substr('1#3#5#7#9#', -3, 2) from dual;

查询1从位置5开始提取子字符串。因为没有指定第三个参数，所以默认的提取长度为从起始位置到源字符串结尾的字符数，也就是6。查询1返回的子字符串是“5#7#9#”。查询2返回位置5～7处的字符，即子字符串“5#7”。查询3将数字-3作为它的起始位置。负数起始位置参数说明Oracle从字符串的结尾向前搜索3个字符。因此，起始位置是从字符串结尾向前3个字符，即位置8。第三个参数是2，因此返回子字符串“#9”。

7．REPLACE函数

REPLACE函数用替换项取代源字符串中出现的所有搜索项。如果替换项的长度与搜索项的长度不同，那么返回字符串的长度与源字符串的长度也不同。如果没有找到搜索字符串，就会原封不动地返回源字符串。REPLACE函数有三个参数，前两个是强制的。其语法是REPLACE(source string, search item, [replacement term])。如果省略replacement term参数，就会从source string中删除所有出现的search item。也就是说用空字符串取代search item。考虑下面的查询：

        查询1: select replace('1#3#5#7#9#', '#', '->') from dual;
        查询2: select replace('1#3#5#7#9#', '#') from dual;

查询1中将哈希符指定为搜索字符，替换字符串指定为“->”。哈希符在源字符串中出现了5次，因此最后得到的字符串是：1->3->5->7->9->。查询2没有指定替换字符串。因此默认行为是用空字符串取代搜索字符串，实际上就是从源字符串中完全删除搜索字符，结果返回字符串“13579”。

8.1.5 使用数字函数

Oracle提供了一定数量的内置数字函数，来与流行的电子数据表软件包的数学工具包抗衡。数字函数与其他函数的显著不同是它们只接受和返回数字数据。Oracle提供的数字函数能够解决三角运算、求幂和对数等问题。本章重点关注三个单行数字函数：ROUND、TRUNC和MOD。

1．数字ROUND函数

ROUND函数依据指定的小数精度对数值进行舍入运算。返回依据有效数字以指定的小数精度进行上舍入或者下舍入的值。如果指定的小数精度为 n，则要舍入的有效数字在小数点右边(n+1)个位置。如果n为负数，那么要舍入的有效数字在小数点左边n个位置。如果有效数字的数值大于或者等于5，就进行“上舍入”，其他情况进行“下舍入”。

ROUND函数有两个参数。其语法是ROUND(source number, decimal precision)。source number参数表示任何数字值。decimal precision参数指定舍入的精度，它是可选的。如果没有指定decimal precision参数，则舍入的默认精度是零，也就是说将源数字舍入为最接近的整数。

看一下表8-1中列出的数字1601.916的小数精度。负的小数精度值位于小数点左边，而正值在小数点右边。

如果decimal precision参数是1，那么会将源数字舍入为最接近的十分位数。如果它是2，那么会将源数字舍入为最近的百分位数，以此类推。下面的查询说明了这个函数的用法：

        查询1: select round(1601.916, 1) from dual;
        查询2: select round(1601.916, 2) from dual;
        查询3: select round(1601.916, -3) from dual;
        查询4: select round(1601.916) from dual;

查询1的decimal precision参数(n)为1，也就是说将源数字舍入为最近的十分位数。因为百分位(n + 1)数字是1(小于5)，所以不进行舍入，返回的数字是1601.9。查询2中小数精度参数是2，因此将源数字舍入为最近的百分位数。因为千分位是6(大于5)，所以进行舍入，返回的数字是1601.92。查询3中的小数精度参数是-3。因为它是负数，所以舍入的有效数字在小数点左边第3个位置，即百位，也就是6。因为百位是6，所以进行舍入，返回的数字是2000。查询4未指定小数精度参数。这说明应该舍入到最接近的整数。因为十分位是9，因此向上舍入，返回1602。

2．数字TRUNC函数（Truncate）

TRUNC函数依据指定的小数精度对数值执行截取运算。数字截取不同于舍入，因为如果小数精度是正数的话，最后的值依据指定的小数精度删除数字，并不进行向上或者向下舍入。然而，如果指定的小数精度(n)为负数，输入值从小数点左边第n个数位开始向后归0。

TRUNC函数有两个参数。其语法是TRUNC(source number, decimal precision)。source number 表示任何数字值。decimal precision 指定截取的精度，它是可选的。如果没有指定decimal precision参数，那么默认精度为零，也就是说将source number截取到最接近的整数。

如果decimal precision参数为1，那么将source number截取到十分位。如果它为2，那么将source number截取到百分位，以此类推。下面的查询说明了这个函数的用法：

        查询1: select trunc(1601.916, 1) from dual;
        查询2: select trunc(1601.916, 2) from dual;
        查询3: select trunc(1601.916, -3) from dual;
        查询4: select trunc(1601.916) from dual;

查询1的decimal precision 参数为1，也就意味着将source number截取到十分位，返回数字1601.9。查询2中的decimal precision参数是2，因此在百分位截取source number，返回的数字是1601.91。注意，如果执行舍入运算，结果就不是这样，因为位置(n+1)上的数字为6(大于5)。查询3指定负数(-3)作为decimal precision。小数点左边的第三个位置说明截取在百位发生，如前面的表8-1所示。因此，从其百位数字(6)开始向后将source number归零，返回的数字是1000。最后，查询4没有decimal precision参数，也就意味着截取在整数精度完成。返回的数字是1601。

3．MOD函数（Modulus）

MOD函数返回除法运算的余数。提供两个数——被除数和除数，执行除法运算。如果除数是被除数的因数，MOD就返回零，因为没有余数。如果除数等于零，则返回no division by zero错误，MOD函数也返回零。如果除数大于被除数，那么MOD函数返回被除数作为结果。这是因为它将除数除以零次，余数等于被除数。

MOD函数有两个参数。其语法是MOD(dividend, divisor)。dividend和divisor参数都可以表示数字字面值、列或者表达式，它们可以是负数也可以是正数。下面的查询说明了这个函数的用法：

        查询1: select mod(6, 2) from dual;
        查询2: select mod(5, 3) from dual;
        查询3: select mod(7, 35) from dual;
        查询4: select mod(5.2, 3) from dual;

查询1用6除以2，余数为0。查询2用5除以3，商为1，余数为2。查询3用7除以35。因为除数大于被除数，所以返回数字7作为模数。查询4用小数作为被除数。5.2除以3商为1，余数为2.2。

8.1.6 使用日期

Date函数是解决与日期相关问题的简单函数，它不需要记录闰年或者特殊月份的天数。在详细讨论SYSDATE函数之前，我们先讨论Oracle如何保存日期以及默认的日期格式掩码。随后讨论日期运算和日期操作函数：ADD_MONTHS、MONTHS_BETWEEN、LAST_DAY、NEXT_DAY、ROUND和TRUNC。

1．数据库中的日期存储

数据库在内部以数字格式存储日期，这种格式支持存储世纪、年、月和日，以及时间信息，例如时、分和秒。从表中访问日期信息时，结果的默认格式由表示日的两个数字、表示月的三个字母缩写和表示年的两个数字组成。为了避免自动转换类型的异常，下述查询中的字面量日期已使用本章后面介绍的TO_DATE转换函数转换为日期类型。根据NLS会话参数的不同，结果可能不同。

2．SYSDATE函数

SYSDATE函数没有参数，它返回数据库服务器当前的系统日期和时间。如果数据库服务器安装时，使用AMERICAN作为默认语言，SYSDATE函数就返回当前系统日期的DD-MON-RR组成部分。如果数据库服务器所处的时区与查询数据库的客户机所在时区不同，那么SYSDATE返回的日期和时间就会与客户机上的本地操作系统时钟不同。检索数据库服务器日期的查询如下所示：

        select sysdate from dual;

3．日期运算

下面的等式说明了关于日期运算的重要原则：

Date1 - Date2 = Num1.

可以从另一个日期中减去日期。这两个日期项之间的差值表示它们之间的天数。可以将所有数字(包括小数)添加到日期项或者从日期项中减去。在该上下文中，数字表示天数。数字和日期项之间的和或者差值总是返回日期项。这条原则说明不允许相加、相乘或者相除两个日期项。

4．MONTHS_BETWEEN函数

MONTHS_BETWEEN函数返回表示两个强制的日期参数之间月数的数值。其语法是MONTHS_ BETWEEN(date1, date2)。该函数计算date1和date2之间月份的差值(每月31天)。如果date1在date2之前，就返回负数。这两个日期参数之间的差值可能由整数和小数部分组成。整数表示这两个日期之间的月数。小数部分表示计算年和月之间整数差值之后剩余的天数和时间，以31天的月份为基础。如果要比较的日期的日组成部分相同或者是各自月份的最后一天，那么就返回没有小数部分的整数。

下面的查询说明了MONTHS_BETWEEN函数的用法：

        select months_between(sysdate, sysdate-31) from dual;

假设当前日期是16-APR-2009。这个查询返回1，因为16-APR-2009和16-MAY-2009之间是1个月。

5．ADD_MONTHS函数

ADD_MONTHS函数返回日期项，这个日期项通过将指定月数添加到给定日期值计算得出。ADD_MONTHS函数有两个强制参数。其语法是ADD_MONTHS (start date, number of months)。在将指定的月数添加到start date之后，函数才计算目标日期。月数可能是负数，这样返回的目标日期就早于起始日期。number of months可以是小数，但会忽略小数部分，而使用整数部分。下面三个查询说明了ADD_MONTHS函数的用法。

        Query 1: select add_months(to_date('07-APR-2009', 'DD-MON-YYYY'), 1) from dual;
        Query 2: select add_months(to_date('31-DEC-2008', 'DD-MON-YYYY'), 2.5) from dual;
        Query 3: select add_months(to_date('07-APR-2009', 'DD-MON-YYYY'), -12) from dual;

第一个查询返回07-MAY-2009，其中日组成部分保持不变而月份递增1。第二个查询有两个有趣的方面。用来指定要添加的月数的参数包含小数部分，但小数部分会被忽略。给日期31-DEC-2008添加2个月，应该返回日期31- FEB-2009，但这个日期不存在，因此返回该月的最后一天28-FEB-2009。第三个查询中添加的月数是-12，所以返回07-APR-2008，这个日期比起始日期早12个月。

6．NEXT_DAY函数

NEXT_DAY函数返回的日期是星期内指定的日子下一次出现时的日期。NEXT_DAY函数有两个强制参数。其语法是NEXT_DAY (start date, day of the week)。函数计算在start date之后 day of the week 参数下一次出现的日期。day of the week 参数必须是由NLS_DATE_LANGUAGE会话参数确定的有效字符值。对于NLS_DATE_LANGUAGE=AMERICAN，默认值至少是日子名称的前三个字符，表示星期几的字符值可以指定为任何大小写形式。简短名称可以大于三个字符，例如，星期日可以表示为sun、sund、sunda或者Sunday。考虑下列查询：

        查询1: select next_day(to_date('01-JAN-2009', 'DD-MON-YYYY'), 'tue')from dual;
        查询2: select next_day(to_date('01-JAN-2009', 'DD-MON-YYYY'), 'WEDNE') from dual;

01-JAN-2009是星期四。因此下一次出现星期二的时间是五天之后(即06-JAN-2009)。第二个查询指定字符字面值“WEDNE”，它表示星期三。01-JAN-2009之后的下一个星期三是07-JAN-2009。

7．LAST_DAY函数

LAST_DAY函数返回指定日子所属的月的最后一天的日期。LAST_DAY函数有一个强制参数。其语法是LAST_ DAY(start date)。该函数提取start date参数所属的月，并计算该月最后一天的日期。下面的查询返回日期31-JAN-2009。

        select last_day(to_date('01-JAN-2009', 'DD-MON-YYYY')) from dual;

8．日期ROUND函数

日期ROUND函数依据指定的日期精度格式对值进行舍入运算。返回的值要么向上舍入，要么向下舍入为最接近的日期精度格式。日期ROUND函数使用一个强制参数和一个可选参数。其语法是ROUND(source date, [date precision format])。source date参数表示任意日期项。date precision format参数指定舍入的精度，它是可选的。如果没有指定它，默认的舍入精度是日。date precision formats包括世纪(CC)、年(YYYY)、季度(Q)、月(MM)、星期(W)、日(DD)、时(HH)和分(MI)。

向上舍入到世纪相当于给当前世纪加1个世纪。如果日部分大于16，就会向上舍入到下一个月，否则就会向下舍入到当月的开头。如果月在1～6之间，那么舍入到年就会返回当年开头的日期，否则返回下一年开头的日期。考虑下列查询及其结果：

        SQL> select round(sysdate) day, round(sysdate, 'w') week,
          2        round(sysdate, 'month') month, round(sysdate, 'year') year
          3  from dual;

       DAY        WEEK       MONTH      YEAR
        ---------  ---------  ---------  ---------
        17-APR-09  15-APR-09  01-MAY-09  01-JAN-09

假定这个查询在17-APR-2009的00:05运行。第一项将日期舍入到最接近的日子。因为时间是00:05，它在12:00之后，因此不将日期上舍入。第二项将日期舍入到与当月的第一天是同一周天的日期。由于01-APR-2009是星期三，返回的日期是该日期所处周的星期三。记住，默认情况下，一周的第一天是星期日。因此从12-APR-2009开始的一周的第一个星期三是15-APR-2009。第三项将日期舍入为下个月的开头，因为日部分是17，它返回01-MAY-2009。第四项向上舍入为当年开头的日期，因为月部分是4，它返回01-JAN-2009。

9．日期TRUNC函数

日期TRUNC函数依据指定的日期精度格式对值进行截取运算。

日期TRUNC函数使用一个强制参数和一个可选参数。其语法是TRUNC(source date, [date precision format])。source date参数表示任意日期项。date precision format参数指定截取的精度，它是可选的。如果没有指定它，默认的截取精度是日。这就意味着将source date的所有时间部分都设置为午夜，即00:00:00 (00时、00分和00秒)。在月级别上的截取将source date 的日期设置为该月的第一天。年级别上的截取返回当年开头的日期。下列查询显示了SELECT列表中的四项，每一项都将日期字面值截取到不同的精度：

        SQL> select trunc(sysdate) day, trunc(sysdate, 'w') week,
          2        trunc(sysdate, 'month') month, trunc(sysdate, 'year') year
          3  from dual;

       DAY        WEEK       MONTH      YEAR
        ---------  ---------  ---------  ---------
        17-APR-09  15-APR-09  01-APR-09  01-JAN-09

假定这个查询在17-APR-2009的12:05AM运行。第一项将时间部分00:05设置为00:00，返回当天。第二项将日期截取为与当月的第一天是同一周天星期三的日期，它返回15-APR-2009。第三项将日期截取为当月的开头，返回01-APR-2009。第四项将日期截取为当年的开头，它返回01-JAN-2009。

练习8-2 使用字符操作函数

连接到OE模式，构造一个查询，从CUSTOMERS.EMAIL列中提取唯一的电子邮件主机名。

(1) 启动SQL Developer并连接到OE模式。

(2) CUSTOMERS.CUST_EMAIL项的格式通常如下所示：firstname.lastname@hostname. EXAMPLE.COM。主机名位于@符号与EXAMPLE.com字符之间。SUBSTR函数可用于提取该值。不过，开始位置和长度仍是未知的。INSTR函数可用于定位@符号和字符EXAMPLE.com第一次出现的位置。

(3) 可能的解决方案如下所示：

        select distinct substr(cust_email, instr(cust_email, '@')+1,
        instr(cust_email, '.EXAMPLE.COM')-instr(cust_email, '@')-1)
        hostname from customers;

8.2 描述SQL中可用的各种类型的转换函数

SQL转换函数是单行函数，它们可用来改变列值、表达式或者字面值数据类型的本质。最广泛使用的转换函数是TO_CHAR、TO_NUMBER和TO_DATE这三个函数。TO_CHAR函数将数字和日期信息转换为字符，而TO_NUMBER和TO_DATE函数将字符数据分别转换为数字和日期。

转换函数

Oracle允许将列定义为ANSI、DB2和SQL/DS数据类型。在内部这些类型被转换为Oracle数据类型。每一列都有一个相关数据类型，这种数据类型约束列能够存储的数据的性质。NUMBER列不能存储字符信息。DATE列不能存储随机字符或者随机数。然而，在VARCHAR2字段中可以存储数字和日期信息的字符形式。

如果接受字符输入参数的函数使用数字，Oracle就会自动将它转换为字符形式。如果接受数字或者日期参数的函数使用字符值，就有一些特殊条件，在这些条件下才会发生自动数据类型转换。虽然可以使用隐式数据类型转换，但更可靠的方法是使用单行转换函数将这些值从一种数据类型转换为另一种数据类型。

1．隐式数据类型转换

如果可能，可以将数据类型与函数所需参数的数据类型不相符的值隐式转换为所需的格式。VARCHAR2和CHAR数据类型统称为字符类型。字符字段非常灵活，几乎允许存储所有类型的信息。因此，可以方便地将DATE和NUMBER值转换为它们的字符形式。这些转换称为数字到字符(number to character)和日期到字符(date to character)转换。例如下面的查询：

        查询1: select length(1234567890) from dual;
        查询2: select length(SYSDATE) from dual;

这两个查询都使用LENGTH函数，该函数使用字符串参数。在LENGTH函数计算之前，查询1中的数1234567890被隐式转换为字符串’1234567890'。这个查询返回数字10。查询2先计算SYSDATE函数，假设它为07-APR-38。这个日期被隐式转换为字符串’07-APR-38', LENGTH函数返回数字9。

将字符数据隐式转换为数字数据类型的情况并不常见，因为出现这种情况的唯一条件是该字符数据表示有效数字。字符串’11’会被隐式转换为数字，但’11.123.456’则不会，如下面的查询所示：

        查询3: select mod('11', 2) from dual;
        查询4: select mod('11.123', 2) from dual;
        查询5: select mod('11.123.456', 2) from dual;
        查询6: select mod('$11', 2) from dual;

在MOD函数计算并返回结果1和1.123之前，查询3和查询4分别将字符串’11’和’11.123'隐式转换为数字11和11.123。当Oracle试图实现隐式的字符到数字(character to number)转换时，查询5返回错误“ORA-1722:invalid number”。这种转换会失败是因为字符串’11.123.456'不是有效数字。查询6也会因为无效数字错误而失败，因为美元符号不能被隐式转换为数字。

当字符串符合下面的日期格式时，可以实现隐式字符到日期(character to date)的转换：[D|DD]separator1[MON|MONTH]separator2[R|RR|YY|YYYY]。D和DD分别表示月份中1位和2位的日子。MON是月的三字符缩写词，而MONTH是月的全名。R和RR分别表示1位和2位数字的年。YY和YYYY分别表示2位和4位数字的年。separator1和separator2元素可以是大多数标点符号、空格和制表符。表8-2说明了字符到日期的隐式转换，它列出了一些函数调用以及SQL Developer返回的结果。这里的结果假定系统使用American会话默认值。

2．显式数据类型转换

许多函数可以将值从一种数据类型转换为另一种数据类型，这些函数称为显式数据类型转换函数。这些函数返回的值保证是所需的类型，它们为转换数据项提供了一种安全可靠的方法。

使用TO_CHAR函数，可以将NUMBER和DATE项显式转换为字符项。使用TO_NUMBER函数，可以将字符串显式转换为NUMBER项。TO_DATE函数用来将字符串显式转换为DATE项。Oracle的格式掩码能够广泛控制字符到数字和字符到日期的转换。