使用转换函数

使用转换函数


除了 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

觉得文章有用?

点个广告表达一下你的爱意吧 !😁