8.3 使用TO_CHAR、TO_NUMBER和TO_DATE转换函数

这一考试目标包括使用示例对TO_NUMBER、TO_DATE和TO_CHAR函数作系统描述。对TO_CHAR的讨论可分为从两种类型的数据到字符的转换：DATE和NUMBER。控制转换到字符值的不同格式掩码的可用性保证了这种划分。还有其他许多转换函数，但这些转换函数是最常用的。

使用转换函数

在很多情况下都需要使用转换函数。这些情况包括格式化报告中的DATE字段，以确保从字符字段提取的数字在应用于算术表达式之前被正确地转换为数字。

表8-3说明了单行显式数据类型转换函数的语法。

可选国家语言支持参数(nls_parameters)对于指定返回日期和数字元素的语言和格式非常有用。通常没有指定这些参数，因此会使用日期和数字元素的默认值(例如日或者月的名称和缩写词)。如图8-2所示，有一种公共可用的视图NLS_SESSION_PARAMETERS，它包含当前会话的NLS参数。NLS_CURRENCY的默认值是美元符号($)，但它在用户会话级别可以改变。例如，要将该货币符号改变为三个字符长的字符串GBP，那么可以执行下面的命令：ALTER SESSION set NLS_CURRENCY='GBP';

1．使用TO_CHAR函数将数字转换为字符

TO_CHAR函数返回VARCHAR2数据类型的值。当将它应用于NUMBER数据类型的值时，可以使用几种格式选项。其语法如下所示：

        TO_CHAR(num, [format], [nls_parameter])

num参数是强制性的，它必须是一个数字值。可选的format参数用来指定数字格式信息——例如宽度、货币符号、小数点的位置和组(或者千位)分隔符，必须将它们包含在单引号内。除此之外，对于要转换为字符的数字而言，还有其他一些表示数字格式信息的选项，其中一些格式如表8-4所示。例如下面两个查询：

        查询1: select to_char(00001)||' is a special number' from dual;
        查询2: select to_char(00001, '0999999')||' is a special number' from dual;

查询1计算数字00001，删除其前面的零，将数字1转换为字符’1'，返回字符串’1 is a special number'。查询2将数字格式掩码’0999999’应用于数字00001，将数字00001转换为字符串'0000001'。在连接到字符字面值之后，返回的字符串是’0000001 is a special number'。对于TO_CHAR函数而言，格式掩码中的零和6个9说明必须显示前面的零，而且显示的宽度必须设置为7个字符。因此，TO_CHAR函数返回的字符串包含7个字符。

2．使用TO_CHAR函数将日期转换为字符

使用TO_CHAR函数，可以利用各种格式模型将DATE项转换为几乎所有日期的字符表示形式。其语法如下所示：

        TO_CHAR(date1, [format], [nls_parameter])

只有date1参数是强制的，date1必须是可以被隐式转换为日期的值。可选的format参数区分大小写，必须将它包含在单引号内。格式掩码指定提取哪些日期元素，是用长的名称还是用缩写名称来描述这个元素。还会自动给日和月的名称填充空格。可以使用格式掩码的修饰符来删除这些空格，这个修饰符称为填充模式(fm)运算符。在格式模型之前添加字母fm，就会命令Oracle 从日和月的名称中删除所有空格。对于被转换为字符的日期而言还有许多格式选项，其中一些选项如表8-5所示。考察下面三个查询：

        查询1: select to_char(sysdate)||' is today''s date' from dual;
        查询2: select to_char(sysdate, 'Month')||'is a special time' from dual;
        查询3: select to_char(sysdate, 'fmMonth')||'is a special time' from dual;

如果当前系统日期是03/JAN/09，默认显示格式是DD/MON/RR，那么查询1返回字符串’03/ JAN/09 is today's date'。查询2中有两个值得注意的部分。第一，只提取当前系统日期的月部分并转换为字符类型。第二，因为格式掩码区分大小写，而’Month’是首字母大写，所以返回的字符串是’January is a special time'。不需要在字面值’is a special time’前面添加空格，因为TO_CHAR函数会自动给月名添加空格。如果查询2中的格式掩码是’MONTH'，那么返回的字符串应该是’JANUARY is a special time'。将fm修饰符应用于查询3，返回的字符串是'Januaryis a special time'。注意，January和字面值’is a special time’之间没有空格。在表8-5中，假设格式元素作用于日期02-JUN-1975，现在的年份是2009。

表8-6列出了关于周、季度、世纪和其他不常用格式掩码的日期格式。该表使用日期24-SEP-1000BC，以及格式元素列的格式掩码来计算TO_CHAR函数，得到的结果在表中结果列显示。

使用表8-7中的格式模型提取日期时间数据类型的时间部分。该表使用包括其时间部分的日期27-JUN-2010 21:35:13，以及格式元素列中的格式掩码计算TO_CHAR函数，得到结果列。

表8-8列出了其他一些能够在日期时间格式模型中使用的元素。标点符号用来分隔格式元素。有三种类型的后缀可以用来格式化日期时间元素的组件。而且，如果将字符字面值包含在双引号内，那么就能够在返回值中包含它们。使用日期12/SEP/08 14:31，以及说明和格式掩码列中的格式掩码来计算TO_CHAR函数，就可以得到表8-8中的结果。

JOB_HISTORY表记录公司员工从事的工作。图8-3中的查询依据END_DATE、EMPLOYEE_ ID和JOB_ID字段检索关于每名员工辞职日期的说明语句。需要将必要的字符表达式连接到格式模型为’fmDay "the "ddth "of" Month YYYY’的TO_CHAR函数。fm修饰符用来删除跟在较短的日和较短的月名称后面的空格。双引号中包含的两个字符字面值是单词"the"和"of"。将’th’格式模型应用于’dd’日期元素来创建序数日，如第17和第31。'Month’ 格式模型以首字母大写的形式显示END_DATE列中月元素的全名。最后，YYYY格式掩码检索4位数的年部分。

尽管在默认情况下不会显示世纪组成部分，但当插入或者更新日期值时，它还是会保存在数据库中，并且可用于检索。DD-MON-RR格式掩码是显示和输入的默认格式。当插入或者更新日期值时，可以从SYSDATE函数中获得世纪部分(如果没有提供它)。RR日期格式掩码不同于YY格式掩码，因为它可以依据当前和指定的年份确定不同的世纪。考虑下面的原则，可以更好地理解分配给日期(该日期的年组成部分使用RR格式)的世纪组成部分：

● 如果当前年和指定年的两个数字在0～49之间，则返回当前世纪。假设当前日期是02-JUN- 2007，那么以DD-MON-RR为格式的日期24-JUL-04返回的世纪是20。

● 如果当前年的两个数字介于0～49之间，指定年介于50～99之间，那么返回上一个世纪。假设当前日期是02-JUN-2007，那么为24-JUL-94返回的世纪是19。

● 如果当前年和指定年的两个数字介于50～99之间，在默认情况下返回当前世纪。假设当前日期是02-JUN-1975，那么为24-JUL-94返回的世纪是19。

● 如果当前年的两个数字介于50～99之间，指定年介于0～49之间，则返回下个世纪。假设当前日期是02-JUN-1975，那么为24-JUL-07返回的世纪是20。

3．使用TO_DATE函数将字符转换为日期

TO_DATE函数返回DATE类型的值。转换为日期的字符串可能包含所有或者部分组成DATE的日期时间元素。当只转换包含日期时间元素子集的字符串时，Oracle 提供默认值来构造完整的日期。字符串的组成部分通过格式模型或掩码与不同的日期时间元素相关联。其语法如下所示：

        TO_DATE(string1, [format], [nls_parameter])

只有string1参数是强制性的，如果没有提供格式掩码，string1必须会隐式转换为日期。几乎总是使用可选的 format 参数，在单引号内指定它。格式掩码与表8-5、表8-6和表8-7中列出的格式掩码相同。TO_DATE函数有fx修饰符，它类似于TO_CHAR函数使用的fm。fx修饰符指定string1和格式掩码完全匹配。指定fx修饰符时，不完全匹配格式掩码的字符项会产生错误。例如下面5个查询：

        查询1: select to_date('25-DEC-2010') from dual;
        查询2: select to_date('25-DEC') from dual;
        查询3: select to_date('25-DEC', 'DD-MON') from dual;
        查询4: select to_date('25-DEC-2010 18:03:45', 'DD-MON-YYYY
                HH24:MI:SS') from dual;
        查询5: select to_date('25-DEC-10', 'fxDD-MON-YYYY') from dual;

查询1计算字符串25-DEC-2010，它有足够的信息将其隐式转换为DATE项，使用的默认掩码是DD-MON-YYYY。连字号分隔符会被另一个标点字符取代。因为没有提供时间组成部分，因此将转换日期的时间设置为午夜或者00:00:00。查询2无法将字符串隐式转换为日期，因为没有足够的信息，所以会返回“ORA-01840:input value is not long enough for date format”错误。在查询3中给字符串25-DEC提供格式掩码DD-MON, Oracle能够让数字25匹配DD，让缩写的月名DEC匹配MON部分。没有年和时间部分，因此使用SYSDATE函数返回的当前年，并将时间设置为午夜。如果当前年是2009，查询3就会返回日期25/DEC/09 00:00:00。查询4执行包含所有日期时间元素的字符串的完整转换，Oracle没有提供默认值。查询5在其格式掩码中使用 fx 修饰符。因为字符串的年部分是10，而对应的格式掩码是YYYY，因此会返回错误“ORA-01862: the numeric value does not match the length of the format item”。

4．使用TO_NUMBER函数将字符转换为数字

TO_NUMBER函数返回NUMBER类型的值。转换为数字的字符串必须有合适的格式，以便用相应的格式掩码转换或者删除所有非数字组成部分。其语法如下所示：

        TO_NUMBER(string1, [format], [nls_parameter])

只有string1参数是强制性的，如果没有提供格式掩码，string1就必须是可以隐式转换为数字的值。用单引号指定可选的format参数。格式掩码与表8-4列出的格式掩码相同。例如下面两个查询：

        查询1: select to_number('$1,000.55') from dual;
        查询2: select to_number('$1,000.55', '$999,999.99') from dual;

查询1不能执行到数字的隐式转换，因为有美元符号、逗号和句号，因此返回错误“ORA-1722: invalid number”。查询2让字符串中的美元符号、逗号和句号匹配格式掩码，尽管数字宽度大于字符串宽度，但还是返回数字1000.55。