oracle数据类型及其隐式转换

oracle数据类型及其隐式转换2011-12-1222:37:16|分类：oracle|字号订阅oracle有三种最基本的数据类型，即字符型、数值型、日期型。oracle提供的单行函数中，针对不同的数据类型，提供大量实用的函数，同时提供一系列数据类型转换函数。相信这些函数，在进行数据库的操作过程中，都大量的被使用过。。1）to_char数值、日期-字符型语法：to_char(num|date,[formatmask],[nls_parameters])参数：num|date待转换的数值或者日期formatmask:可选参数数字-字符型的可用格式格式元素元素说明格式数字字符结果9数字宽度999912120显示前面的零09999001200012.小数点的位置09999.999030.4000030.400D小数点分隔符的位置(默认为句点)09999D999030.4000030.400,逗号的位置09999,9990304000003,040G组分隔符的位置(默认为逗号)09999G9990304000003,040$美元符号$09999903040$003040L当地货币L09999903040GBP003040如果nls_currency设置为GBPMI表示负数的减号的位置99999MI-30403040-PR包围在括号内的负数99999PR-30403040EEEE科学计数法99.99999EEEE121.9761.21976E+02UNls_dual_currencyU09999903040CAD003040如果nls_dual_currency设置为CADV乘以10n次(n是V之后9的数量)9999V993040304000S前面加上+或者-S9999993040+3040日期-字符型的可用格式(示例日期:02-JUN-1975)格式说明说明结果Y年的最后一位5YY年的最后两位75YYY年的最后三位975YYYY四位数字表示的年1975RR两位数字表示的年75YEAR区别大小写并用英语拼写的年NINETEENSEVENTY-FIVEMM两位数表示的月06MON月的三个字母缩写JUNMONTH区分大小写并用英语拼写的月JUNED星期几2DD月的两位数日(即是本月的第几天)02DDD年的日153DY星期的三个字母缩写MONDAY区分大小写并用英语拼写的星期MONDAY其他不常用的格式掩码演示数据：24-SEP-1000BC格式元素说明结果W月的周数4WW年的周数39Q年的季度3CC世纪10SprecedingCC,YYYY,YEAR如果日期是BC,那么减号就在结果之前-10,-1000或者-ONETHOUSANDIYYY,IYY,IY,I分别表示4,3,2,1为ISO日期1000,000,00,0BC,AD,B.C.andA.D.J儒略日—从公元前4713年12月31日开始的天数1356075IWISO标准周(1到53)39RM用罗马数字表示的月IX时间组件的日期格式掩码演示数据:27-JUN-201021:35:13格式元素说明结果AM,PM,A.M.和P.M.子午线指示器PMHH,HH12,HH24一天的小时,1-12时和0-23时09,09,21MI分(0-59)35SS秒(0-59)12SSSSS午夜之后的秒(0-86399)77713其他日期格式掩码格式元素说明和格式掩码结果-/.,?#!标点符号:’MM.YY’09.08“anycharacterliteral”字符值:’”Week”W“”of”Month’Week2ofSeptemberTH位置或者序数文本:’DDth”of”Month’12THofSeptemberSP拼写出数字：’MmSPMonthYYYYSP’NineSeptemberTwoThousandEightTHSPorSPTH拼写出位置或者序数:’hh24SpTh’Fourteenth2）to_date字符值-日期值语法：to_date(string,[formatmask],[nls_parameters])参数：string待转换的字符值formatmask:可选参数格式掩码同to_char转换为date时相同。备注：转换时要根据给定的string设定正确的格式掩码，否则Ora_01840:inputvalueisnotlongenoughfordateformat.Ora_01862:thenumericvaluedoesnotmatchthelengthoftheformatitem.3）to_number字符值-数字值语法：to_number(string,[formatmask],[nls_parameters])参数：string待转换的字符值formatmask:可选参数格式掩码同to_char转换为number时相同。备注：如果使用较短的格式掩码就会返回错误。例如:to_number(123.56,’999.9’)返回错误。在oracle中，如果不同的数据类型之间关联，如果不显式转换数据，则它会根据以下规则对数据进行隐式转换1)对于INSERT和UPDATE操作，oracle会把插入值或者更新值隐式转换为字段的数据类型。假如id列的数据类型为numberupdatetsetid='1';-相当于updatetsetid=to_number('1');insertintot(id)values('1')-insertintotvalues(to_number('1'));2)对于SELECT语句，oracle会把字段的数据类型隐式转换为变量的数据类型。如假设id列的数据类型为varchar2select*fromtwhereid=1;-select*fromtwhereto_number(id)=1;但如果id列的数据类型为number，则select*fromtwhereid='1';-select*fromtwhereid=to_number('1');(参考下文)3)当比较一个字符型和数值型的值时，oracle会把字符型的值隐式转换为数值型。如假设id列的数据类型为numberselect*fromtwhereid='1';-select*fromtwhereid=to_number('1');4)当比较字符型和日期型的数据时，oracle会把字符型转换为日期型。如假设create_date为字符型，select*fromtwherecreate_datesysdate;-select*fromtwhereto_date(create_date)sysdate;(注意，此时session的nls_date_format需要与字符串格式相符)假设create_date为date型，select*fromtwherecreate_date'2006-11-1111:11:11';-select*fromtwherecreate_dateto_date('2006-11-1111:11:11');(注意，此时session的nls_date_format需要与字符串格式相符)5)如果调用函数或过程等时，如果输入参数的数据类型与函数或者过程定义的参数数据类型不一直，则oracle会把输入参数的数据类型转换为函数或者过程定义的数据类型。如假设过程如下定义p(p_1number)execp('1');-execp(to_number('1'));6)赋值时，oracle会把等号右边的数据类型转换为左边的数据类型。如varanumbera:='1';-a:=to_number('1');7)用连接操作符(||)时，oracle会把非字符类型的数据转换为字符类型。select1||'2'fromdual;-selectto_char(1)||'2'fromdual;8)如果字符类型的数据和非字符类型的数据(如number、date、rowid等)作算术运算，则oracle会将字符类型的数据转换为合适的数据类型，这些数据类型可能是number、date、rowid等。如果CHAR/VARCHAR2和NCHAR/NVARCHAR2之间作算术运算，则oracle会将她们都转换为number类型的数据再做比较。9)比较CHAR/VARCHAR2和NCHAR/NVARCHAR2时，如果两者字符集不一样，则默认的转换方式是将数据编码从数据库字符集转换为国家字符集。简单总结：比较时，一般是字符型转换为数值型，字符型转换为日期型算术运算时，一般把字符型转换为数值型，字符型转换为日期型连接时(||)，一般是把数值型转换为字符型,日期型转换为字符型赋值、调用函数时，以定义的变量类型为准。