除了SQL实用程序函数之外,Oracle内置函数库还包含类型转换函数.可能存在这样的情况:查询期望在特定数据类型中输入,但是它以不同的数据类型接收它.在这种情况下,Oracle会隐式尝试将意外值转换为可以替换的兼容数据类型,并且不会影响应用程序的连续性.类型转换可以由Oracle隐式完成,也可以由程序员明确完成.
隐式数据类型转换基于一个矩阵,它显示了Oracle对内部类型转换的支持.除了这些规则,Oracle还提供了类型转换函数,可以在查询中用于显式转换和格式化.事实上,建议执行显式转换,而不是依赖于软件智能.虽然隐式转换效果很好,但是为了消除不良输入可能难以在内部进行类型转换的倾斜机会.
一个VARCHAR2或者,CHAR值可以由Oracle隐式转换为NUMBER或DATE类型值.同样,Oracle服务器可以自动将NUMBER或DATA类型值转换为字符数据.请注意,仅当字符分别表示有效数字或日期类型值时才会发生impicit互换.
例如,检查以下SELECT查询.两个查询都会给出相同的结果,因为Oracle内部将15000和'15000'视为相同.
SELECT employee_id,first_name,salary FROM employees WHERE salary > 15000;
SELECT employee_id,first_name,salary FROM employees WHERE salary > '15000';
SQL转换函数是单行函数,可以对列值,文字或类型进行类型转换一种表达 . TO_CHAR,TO_NUMBER和TO_DATE是执行数据类型交叉修改的三个函数.
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即月份拼写并用空格填充,然后是这个月的两位数日,然后是四位数的年份.如果您希望以大小写形式显示月份名称(即"12月"),只需在格式参数中使用此案例:('Month DD,YYYY').
第二个图10-39中的TO_CHAR函数用于格式化SALARY以显示货币符号和两个小数位.
Oracle提供全面的格式模型.下表显示了格式模型列表,可用于使用TO_CHAR将日期和数字值作为字符进行类型转换.
格式模型 | 描述 |
---|---|
,(逗号) | 它返回指定位置的逗号.您可以在数字格式模型中指定多个逗号.限制:逗号元素无法启动数字格式模型.逗号不能出现在数字格式模型中的十进制字符或句点的右侧. |
.(句号) | 返回小数点,即指定位置的句点(.).限制:您只能在数字格式模型中指定一个句点 |
$ | 返回带有前导美元符号的值 |
0 | 返回前导零.返回尾随零. |
9 | 返回具有指定位数的值,前导空格为正数或带前导减去if负.前导零是空白的,除了零值,它为定点数的整数部分返回零. |
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( string1,[format],[nls_parameter])
下表显示了格式模型列表,可用于使用TO_NUMBER将字符值作为数字进行类型转换.
格式模型 | 描述 |
---|---|
CC | Century |
SCC | 世纪BC前缀为 - |
YYYY | 有4个数字的年份 |
SYYY | BC年前缀为 - |
IYYY | ISO年份有4个数字 |
YY | 有2个数字的年份 |
RR | 年份,2个数字兼容Y2k |
年 | 字符年份 |
SYEAR | 以字符为单位的年份,BC前缀为 - |
BC | BC/AD指标 |
Q | 数字季度(1,2,3,4) |
MM | 一年中的一个月01,02 ... 12 |
MONTH | 以字符为单位的月份(即1月) |
MON | JAN,FEB |
WW | 周数(即1) |
W | 每月的周数(即5) |
IW | ISO标准中的年份周数. |
DDD | 数字中的日期(即365) |
DD | 数字中的日期(即28) |
D | 星期几数字(即7) |
DAY | 字符中的星期几(即星期一) |
FMDAY | 星期几(星期一) |
DY | 短字符描述中的星期几(即SUN) |
J | 朱利安日(自1月以来的天数)公元前1 4713年,公元前4713年1月1日在甲骨文中为1) |
HH,H12 | 当天的小时数(1- 12) |
HH24 | 24小时表示法(0-23)的小时编号 |
AM,PM | AM或PM |
MI,SS | 分钟数和秒数(即59), |
SSSSS | 今天的秒数. |
DS | 短日期格式.取决于NLS设置.仅用于时间戳. |
DL | 长日期格式.取决于NLS设置.仅用于时间戳. |
E | 缩写的时代名称.仅适用于日历:日本帝国,ROC官方,泰国佛. |
EE | 完整时代名称 |
FF | 小数秒.与时间戳一起使用. |
FF1..FF9 | 小数秒.与时间戳一起使用.数字控制用于小数秒的小数位数. |
FM | 填充模式:抑制转换输出中的空白 |
FX | 格式精确:需要数据和格式模型之间的精确模式匹配. |
IYY或IY OR I | ISO标准年度的最后3,2,1位数字.仅输出 |
RM | 月份的罗马数字表示(I .. XII) |
RR | 年份的最后2位数字. |
RRRR | 用于输出的年份的最后2位数字.当用于输入时接受fout-digits年. |
SP | 拼写格式.可以出现一个数字元素的结尾.结果总是英文.例如,MMSP格式的第10个月返回"十" |
SPTH | 拼写和序数格式;首先得到1个结果. |
TH | 将数字转换为它的序数格式.例如1 becoms 1st. |
TS | 短时间格式.取决于NLS设置.仅用于时间戳. |
TZD | 缩写的时区名称.即PST. |
TZH,TZM | 时区小时/分钟位移. |
TZR | 时区区域 |
X | 本地基数字符.在美国,这是一个句点(.) |
下面的SELECT查询接受数字作为字符输入,并按照格式说明符打印它们./p>
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函数允许用户以任何格式输入日期,然后将条目转换为Oracle 11g使用的默认格式.
TO_DATE(string1,[format_mask],[nls_language])
format_mask参数包含一系列元素准确表示数据的样子,必须用单引号输入.
格式模型 | 描述 |
---|---|
年 | 年份,拼写 |
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-53),其中第1周从一年的第一天开始并继续到了第一天的第七天. |
W | 第1周从第1周开始的第1周(1-5)周本月的一周(1-52或1-53)基于该月的一周中的每一周(1-52或1-53). |
IW | ISO标准. |
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 | Meridian指标 |
AD,BC | AD,BC指标 |
TZD | 夏令时信息.例如,'PST' |
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函数将替换值替换为NULL值.
NVL(Arg1,replace_with)
在语法中,两个参数都是必需的.请注意,NVL函数适用于所有类型的数据类型.并且原始字符串和替换的数据类型必须处于兼容状态,即Oracle可以相同或隐式转换.
如果arg1是一个字符值,那么oracle会在替换它之前将替换字符串转换为与arg1兼容的数据类型,并在expr1的字符集中返回VARCHAR2. arg1是数字,然后Oracle确定具有最高数字优先级的参数,隐式地将另一个参数转换为该数据类型,并返回该数据类型.
下面的SELECT语句将显示'n/a'如果员工尚未被分配到任何工作,即JOB_ID为NULL.否则,它将显示实际的JOB_ID值.
SELECT first_name, NVL(JOB_ID, 'n/a') FROM employees;
作为对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函数比较两个参数expr1和expr2.如果expr1和expr2相等,则返回NULL;否则,它返回expr1.与其他null处理函数不同,第一个参数不能为NULL.
NULLIF (expr1,expr2)
请注意,第一个参数可以是一个计算结果为NULL的表达式,但它不能是文字NULL.这两个参数对于函数执行都是必需的.
以下查询返回NULL,因为两个输入值都相等.
SELECT NULLIF(12,12) FROM DUAL;
同样,在查询下方返回'SUN',因为两个字符串不相等.
SELECT NULLIF('SUN','MOON') FROM DUAL;
COALESCE函数,一种更通用的NVL形式,返回参数中的第一个非null表达式名单.它至少需要两个必需参数,但最大参数没有限制.
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(表达式,搜索,结果[,搜索,结果] ... [,默认])
DECODE函数按顺序将表达式与每个搜索值进行比较.如果表达式和搜索参数之间存在相等性,则返回相应的结果.如果不匹配,则返回默认值(如果已定义),否则为NULL.在任何类型兼容性不匹配的情况下,oracle内部可能会进行隐式转换以返回结果.
事实上,Oracle在使用DECODE函数时认为两个空值是等效的./p>
SELECT DECODE(NULL,NULL,'EQUAL','NOT EQUAL') FROM DUAL; DECOD ----- EQUAL
如果expression为null,则Oracle返回第一次搜索的结果也为null. DECODE函数中的最大组件数为255.
SELECT first_name, salary, DECODE (hire_date, sysdate,'NEW JOINEE','EMPLOYEE') FROM employees;
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