使用转换函数
使用转换函数
除了 SQL 实用函数外，Oracle 内置函数库还包含类型转换函数。在某些情况下，查询需要特定数据类型的输入，但它以不同的数据类型接收输入。在这种情况下，Oracle 会隐式地尝试将意外值转换为可替换的兼容数据类型，并且不会影响应用程序的连续性。类型转换可以由 Oracle 隐式完成，也可以由程序员显式完成。
隐式数据类型转换基于一个矩阵进行，该矩阵展示了 Oracle 对内部类型转换的支持。除了这些规则之外，Oracle 还提供了类型转换函数，可以在查询中使用这些函数进行显式转换和格式化。事实上，建议进行显式转换，而不是依赖软件智能。虽然隐式转换效果很好，但要消除错误输入可能难以在内部进行类型转换的偏斜机会。
隐式数据类型转换
Oracle 可以将 VARCHAR2 或 CHAR 值隐式转换为 NUMBER 或 DATE 类型的值。类似地，一个 NUMBER 或 DATA 类型的值可以被 Oracle 服务器自动转换为字符数据。请注意，隐式相互转换仅在字符分别表示有效数字或日期类型值时发生。
例如，检查以下 SELECT 查询。这两个查询将给出相同的结果，因为 Oracle 在内部将 15000 和 ‘15000’ 视为相同。
查询 1
SELECT employee_id,first_name,salary FROM employees WHERE salary > 15000;
查询 2
SELECT employee_id,first_name,salary FROM employees WHERE salary > '15000';
显式数据类型转换
SQL 转换函数是单行函数，能够对列值、文字或表达式进行类型转换。TO_CHAR、TO_NUMBER 和 TO_DATE 是执行数据类型交叉修改的三个函数。
TO_CHAR 函数
TO_CHAR 函数用于将数字或日期输入类型转换为具有格式模型（可选）的字符类型。
句法
TO_CHAR(number1, [format], [nls_parameter])
对于数字到字符的转换，nls 参数可用于指定十进制字符、组分隔符、本地货币模型或国际货币模型。这是一个可选规范 – 如果不可用，将使用会话级别 nls 设置。对于日期到字符的转换，可以使用 nls 参数指定日期和月份名称（如果适用）。
使用 TO_CHAR 函数将日期转换为字符类型后，可以将其格式化为多种格式。TO_CHAR 函数用于让 Oracle 11g 以特定格式显示日期。格式模型区分大小写，必须用单引号括起来。
考虑下面的 SELECT 查询。查询使用 TO_CHAR 函数格式化 EMPLOYEES 表的 HIRE_DATE 和 SALARY 列。
SELECT first_name,
TO_CHAR (hire_date, 'MONTH DD, YYYY') HIRE_DATE,
TO_CHAR (salary, '$99999.99') Salary
FROM employees
WHERE rownum < 5;
FIRST_NAME HIRE_DATE SALARY
-------------------- ------------------ ----------
Steven JUNE 17, 2003 $24000.00
Neena SEPTEMBER 21, 2005 $17000.00
Lex JANUARY 13, 2001 $17000.00
Alexander JANUARY 03, 2006 $9000.00
第一个 TO_CHAR 用于将雇用日期转换为日期格式 MONTH DD, YYYY，即拼写出来并用空格填充的月份，然后是该月的两位数日期，然后是四位数的年份。如果您更喜欢以混合大小写形式显示月份名称（即“December”），只需在格式参数中使用这种大小写：(‘Month DD, YYYY’)。
图 10-39 中的第二个 TO_CHAR 函数用于格式化 SALARY 以显示货币符号和两个小数位。
Oracle 提供了一套全面的格式模型。下表显示了可用于使用 TO_CHAR 将日期和数字值类型转换为字符的格式模型列表。
| Format Model | 描述 |
|---|---|
| ,(comma) | 它在指定位置返回一个逗号。您可以在数字格式模型中指定多个逗号。限制：逗号元素不能开始数字格式模型。在数字格式模型中，逗号不能出现在十进制字符或句点的右侧。 |
| .(period) | 返回一个小数点，它是指定位置的句点 (.)。限制：您只能在数字格式模型中指定一个句点 |
| $ | 返回带有前导美元符号的值 |
| 0 | 返回前导零。返回尾随零。 |
| 9 | 返回具有指定位数的值，如果是正数则带有前导空格，如果是负数则带有前导减号。前导零是空白，除了零值，它为定点数的整数部分返回零。 |
| B | 当整数部分为零时（无论格式模型中的“0”如何），为定点数的整数部分返回空白。 |
| C | 在指定位置返回 ISO 货币符号（NLS_ISO_CURRENCY 参数的当前值）。 |
| D | 返回指定位置的十进制字符，即 NLS_NUMERIC_CHARACTER 参数的当前值。默认值为句点 (.)。限制： 一个数字格式模型中只能指定一个十进制字符。 |
| EEE | 返回使用科学记数法的值。 |
| FM | 返回一个没有前导或尾随空格的值。 |
| G | 返回指定位置的组分隔符（NLS_NUMERIC_CHARACTER 参数的当前值）。您可以在数字格式模型中指定多个组分隔符。限制：在数字格式模型中，组分隔符不能出现在小数字符或句点的右侧 |
| L | 返回指定位置的本地货币符号（NLS_CURRENCY 参数的当前值）。 |
| MI | 返回带有尾随减号 (-) 的负值。返回带有尾随空白的正值。限制： MI 格式元素只能出现在数字格式模型的最后位置。 |
| PR | 在 中返回负值。它只能出现在数字格式模型的末尾。 |
| RN,rm | 以大写的罗马数字形式返回值。以小写的罗马数字形式返回值。值可以是 1 到 3999 之间的整数。 |
| S | 返回带有前导或尾随减号 (-) 的负值。返回带有前导或尾随加号 (+) 的正值。限制： S 格式元素只能出现在数字格式模型的第一个或最后一个位置。 |
| TM | “最小文本”。返回（以十进制输出）尽可能少的字符数。此元素不区分大小写。 |
| U | 在指定位置返回“欧元”（或其他）双货币符号（NLS_DUAL_CURRENCY 参数的当前值）。 |
| V | 返回一个乘以 10n 的值（如有必要，将其四舍五入），其中 n 是“V”后 9 的个数。 |
| X | 返回指定位数的十六进制值。 |
TO_NUMBER 函数
TO_NUMBER 函数将字符值转换为数字数据类型。如果被转换的字符串包含非数字字符，则函数返回错误。
句法
TO_NUMBER (string1, [format], [nls_parameter])
下表显示了可用于使用 TO_NUMBER 将字符值类型转换为数字的格式模型列表。
| Format Model | 描述 |
|---|---|
| CC | 世纪 |
| SCC | 公元前世纪前缀 – |
| YYYY | 有 4 个数字的年份 |
| SYYY | BC 年前缀为 – |
| IYYY | 带有 4 个数字的 ISO 年份 |
| YY | 有 2 个数字的年份 |
| RR | 具有 Y2k 兼容性的 2 个数字的年份 |
| YEAR | 字符中的年份 |
| SYEAR | 以字符表示的年份，BC 前缀为 – |
| BC | BC/AD 指标 |
| Q | 季度数字 (1,2,3,4) |
| MM | 01, 02…12 月份 |
| MONTH | 以字符为单位的月份（即一月） |
| MON | 一月、二月 |
| WW | 周数（即 1） |
| W | 月份的周数（即 5） |
| IW | ISO 标准中一年中的周数。 |
| DDD | 一年中的第几天（即 365） |
| DD | 月份中的数字（即 28） |
| D | 星期几（即 7） |
| DAY | 以字符表示的星期几（即星期一） |
| FMDAY | 以字符表示的星期几（即星期一） |
| DY | 短字符描述中的星期几（即 SUN） |
| J | 儒略日（自公元前 4713 年 1 月 1 日以来的天数，其中公元前 4713 年 1 月 1 日在 Oracle 中为 1） |
| HH,H12 | 一天中的小时数 (1-12) |
| HH24 | 一天中的小时数，带有 24Hours 符号 (0-23) |
| AM, PM | 上午或下午 |
| MI, SS | 分秒数（即59）， |
| SSSSS | 这一天的秒数。 |
| DS | 短日期格式。取决于 NLS 设置。仅与时间戳一起使用。 |
| DL | 长日期格式。取决于 NLS 设置。仅与时间戳一起使用。 |
| E | 缩写的时代名称。仅适用于日历：日本帝国、中华民国官员、泰国佛陀。 |
| EE | 完整的时代名称 |
| FF | 小数秒。与时间戳一起使用。 |
| FF1..FF9 | 小数秒。与时间戳一起使用。数字控制用于小数秒的小数位数。 |
| FM | 填充模式：抑制转换输出中的空白 |
| FX | 格式精确：需要数据和格式模型之间的精确模式匹配。 |
| IYY OR IY OR I | ISO 标准年份的最后 3、2、1 位数字。仅输出 |
| RM | 月份的罗马数字表示 (I .. XII) |
| RR | 年份的最后 2 位数字。 |
| RRRR | 用于输出的年份的最后 2 位数字。用于输入时接受四位数年份。 |
| SP | 拼写格式。可以出现在数字元素的末尾。结果总是英文。例如，格式为 MMSP 的第 10 个月返回“十” |
| SPTH | 拼写和序数格式；1 结果第一。 |
| TH | 将数字转换为其序数格式。例如 1 变成 1st。 |
| TS | 短时间格式。取决于 NLS 设置。仅与时间戳一起使用。 |
| TZD | 缩写的时区名称。即太平洋标准时间。 |
| TZH,TZM | 时区小时/分钟位移。 |
| TZR | 时区区域 |
| X | 本地基数字符。在美国，这是一个句号 (.) |
下面的 SELECT 查询接受数字作为字符输入并按照格式说明符打印它们。
SELECT TO_NUMBER('121.23', '9G999D99')
FROM DUAL
TO_NUMBER('121.23','9G999D99')
------------------------------
121.23
SELECT TO_NUMBER('1210.73', '9999.99')
FROM DUAL;
TO_NUMBER('1210.73','9999.99')
------------------------------
1210.73
TO_DATE 函数
该函数将字符值作为输入并返回相同的格式化日期。TO_DATE 函数允许用户以任何格式输入日期，然后将输入转换为 Oracle 11g 使用的默认格式。
句法：
TO_DATE( string1, [ format_mask ], [ nls_language ] )
format_mask 参数由一系列元素组成，这些元素准确地表示数据应该是什么样子，并且必须用单引号引起来。
| Format Model | 描述 |
|---|---|
| YEAR | 年份, 拼写 |
| YYYY | 4 位数年份 |
| YYY,YY,Y | 年份的最后 3、2 或 1 位数字。 |
| IYY,IY,I | ISO 年份的最后 3、2 或 1 位数字。 |
| IYYY | 基于 ISO 标准的 4 位数年份 |
| RRRR | 接受 2 位数年份并返回 4 位数年份。 |
| Q | 一年中的季度（1、2、3、4；JAN-MAR = 1）。 |
| MM | 月（01-12；JAN = 01）。 |
| MON | 月份的缩写名称。 |
| MONTH | 月份名称，用空格填充，长度为 9 个字符。 |
| RM | 罗马数字月份（I-XII；JAN = I）。 |
| WW | 一年中的第 1 周 (1-53)，其中第 1 周从一年的第一天开始并持续到一年的第七天。 |
| W | 一个月中的第 (1-5) 周，其中第 1 周从当月的第一天开始，到第七天结束。 |
| IW | 基于 ISO 标准的一年中的第几周（1-52 或 1-53）。 |
| D | 星期几 (1-7)。 |
| DAY | 日名。 |
| DD | 一个月中的第几天 (1-31)。 |
| DDD | 一年中的第几天 (1-366)。 |
| DY | 日的缩写名称。 |
| J | 儒略日；自公元前 4712 年 1 月 1 日以来的天数。 |
| HH12 | 一天中的某个小时 (1-12)。 |
| HH24 | 一天中的小时 (0-23)。 |
| MI,SS | 分钟 (0-59)。 |
| SSSSS | 午夜过后的秒数 (0-86399)。 |
| FF | 小数秒。在 FF 后使用 1 到 9 之间的值来表示小数秒中的位数。例如，’FF4’。 |
| AM,PM | 经络指标 |
| AD,BC | AD、BC 指标 |
| TZD | 夏令时信息。例如，“太平洋标准时间” |
| TZH,TZM,TZR | 时区小时/分钟/地区。 |
以下示例将字符串转换为日期：
SELECT TO_DATE('January 15, 1989, 11:00 A.M.', 'Month dd, YYYY, HH:MI A.M.', 'NLS_DATE_LANGUAGE = American')
FROM DUAL;
TO_DATE('
---------
15-JAN-89
一般功能
通用函数用于处理数据库中的 NULL 值。一般 NULL 处理函数的目标是用替代值替换 NULL 值。我们将在下面简要介绍这些功能。
NVL
NVL 函数用替代值替换 NULL 值。
句法：
NVL( Arg1, replace_with )
在语法中，这两个参数都是必需的。请注意，NVL 函数适用于所有类型的数据类型。并且原始字符串和替换的数据类型必须处于兼容状态，即相同或由 Oracle 隐式转换。
如果 arg1 是字符值，则 oracle 在比较之前将替换字符串转换为与 arg1 兼容的数据类型，并返回 expr1 字符集中的 VARCHAR2。如果 arg1 是数字，则 Oracle 确定具有最高数字优先级的参数，将另一个参数隐式转换为该数据类型，并返回该数据类型。
如果员工尚未分配到任何工作，即 JOB_ID 为 NULL，则下面的 SELECT 语句将显示“n/a”。否则，它将显示实际的 JOB_ID 值。
SELECT first_name, NVL(JOB_ID, 'n/a') FROM employees;
NVL2
作为对 NVL 的增强，Oracle 引入了一个函数，不仅可以替换 NULL 列的值，还可以替换 NOT NULL 列的值。NVL2 函数可用于替换 NULL 和非 NULL 值的替代值。
句法：
NVL2( string1, value_if_NOT_null, value_if_null )
如果员工的 JOB_CODE 为 NULL，则下面的 SELECT 语句将显示“Bench”。对于 JOB CODE 的明确非空值，它将显示常量值“Job Assigned”。
SQL> SELECT NVL2(JOB_CODE, 'Job Assigned', 'Bench') FROM employees;
NULLIF
NULLIF 函数比较两个参数 expr1 和 expr2。如果 expr1 和 expr2 相等，则返回 NULL；否则，它返回 expr1。与其他空处理函数不同，第一个参数不能为空。
句法：
NULLIF (expr1, expr2)
请注意，第一个参数可以是计算结果为 NULL 的表达式，但它不能是文字 NULL。这两个参数都是函数执行所必需的。
以下查询返回 NULL，因为两个输入值 12 相等。
SELECT NULLIF (12, 12) FROM DUAL;
同样，下面的查询返回 ‘SUN’，因为两个字符串不相等。
SELECT NULLIF ('SUN', 'MOON')
FROM DUAL;
合并
COALESCE 函数是 NVL 的一种更通用的形式，它返回参数列表中的第一个非空表达式。它需要最少两个强制参数，但最大参数没有限制。
句法：
COALESCE (expr1, expr2, ... expr_n )
考虑下面的 SELECT 查询。它选择输入到员工地址字段的第一个非空值。
SELECT COALESCE (address1, address2, address3) Address FROM employees;
有趣的是，COALESCE 函数的工作类似于 IF..ELSIF..ENDIF 构造。上面的查询可以重写为 –
IF address1 is not null THEN result := address1; ELSIF address2 is not null THEN result := address2; ELSIF address3 is not null THEN result := address3; ELSE result := null; END IF;
条件函数
Oracle 提供了条件函数 DECODE 和 CASE 以在 SQL 语句中强加条件。
解码功能
该函数是 IF..THEN..ELSE 条件过程语句的 SQL 等价物。DECODE 适用于所有数据类型的值/列/表达式。
句法：
DECODE (expression, search, result [, search, result]... [, default])
DECODE 函数按顺序将表达式与每个搜索值进行比较。如果表达式和搜索参数之间存在相等，则返回相应的结果。如果不匹配，则返回默认值，如果已定义，则返回 NULL。在任何类型兼容性不匹配的情况下，oracle 内部会进行可能的隐式转换以返回结果。
事实上，在使用 DECODE 函数时，Oracle 认为两个空值是等效的。
SELECT DECODE(NULL,NULL,'EQUAL','NOT EQUAL') FROM DUAL; DECOD ----- EQUAL
如果 expression 为空，则 Oracle 返回第一次搜索的结果，该结果也为空。DECODE 函数中的最大组件数为 255。
SELECT first_name, salary, DECODE (hire_date, sysdate,'NEW JOINEE','EMPLOYEE') FROM employees;
CASE 表达式
CASE 表达式的工作原理与 DECODE 相同，但在语法和用法上有所不同。
句法：
CASE [ expression ] WHEN condition_1 THEN result_1 WHEN condition_2 THEN result_2 ... WHEN condition_n THEN result_n ELSE result END
Oracle 搜索从左开始向右移动，直到找到真条件，然后返回与其关联的结果表达式。如果没有发现任何条件为真，并且存在 ELSE 子句，则 Oracle 返回用 else 定义的结果。否则，Oracle 返回 null。
CASE 表达式中的最大参数数为 255。所有表达式都计入此限制，包括简单 CASE 表达式的初始表达式和可选的 ELSE 表达式。每个 WHEN … THEN 对算作两个参数。为避免超出此限制，您可以嵌套 CASE 表达式，以便 return_expr 本身是一个 CASE 表达式。
SELECT first_name, CASE WHEN salary < 200 THEN 'GRADE 1' WHEN salary > 200 AND salary < 5000 THEN 'GRADE 2' ELSE 'GRADE 3' END CASE FROM employees; ENAM CASE ---- ------- JOHN GRADE 2 EDWIN GRADE 3 KING GRADE 1
