oracle 各数据类型dump说明(一)

最新推荐文章于 2021-04-22 20:45:53 发布

congse3359

最新推荐文章于 2021-04-22 20:45:53 发布

阅读量342

点赞数

文章标签：数据库

    这一节主要介绍 CHAR,VARCHAR2,NCHAR,NVARCHAR2,NUMBER,INTEGER,DATE,sysdate类型.
环境:
os:centos 6.6
db version:11.2.0.4

#查看地区语言字符集信息
select * from v$nls_parameters;
/*
PARAMETER   VALUE
NLS_LANGUAGE   AMERICAN
NLS_TERRITORY   AMERICA
NLS_CURRENCY   $
NLS_ISO_CURRENCY   AMERICA
NLS_NUMERIC_CHARACTERS   .,
NLS_CALENDAR   GREGORIAN
NLS_DATE_FORMAT   DD-MON-RR
NLS_DATE_LANGUAGE   AMERICAN
NLS_CHARACTERSET   AL32UTF8
NLS_SORT   BINARY
NLS_TIME_FORMAT   HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT   DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT   HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT   DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY   $
NLS_NCHAR_CHARACTERSET   AL16UTF16
NLS_COMP   BINARY
NLS_LENGTH_SEMANTICS   BYTE
NLS_NCHAR_CONV_EXCP   FALSE
*/

#建测试表,里面定义CHAR,VARCHAR2,NCHAR,NVARCHAR2,NUMBER,INTEGER,DATE类型的字段
create table SCOTT.TB_TYPE
(
t_char      CHAR(10),
t_varchar2 VARCHAR2(10),
t_nchar     NCHAR(10),
t_nvarchar2 NVARCHAR2(10),
t_number    NUMBER(10),
t_number_2 NUMBER(10,2),
t_integer   INTEGER,
t_date      DATE
);

#插入测试数据
insert into scott.tb_type
select 'selectsh','selectsh','selectsh','selectsh',12.345,12.345,12.345,sysdate,'selectshen'
from dual;
commit;

#查看插入后的记录
select * from scott.tb_type;
/*
T_CHAR   T_VARCHAR2   T_NCHAR   T_NVARCHAR2   T_NUMBER   T_NUMBER_2   T_INTEGER   T_DATE
selectsh     selectsh   selectsh     selectsh   12   12.35   12   2016/2/3 17:28:48
*/

#char类型
#char类型定义的是固定长度,最大2000个字节,内部类型号Typ=96
#t_char定义的是CHAR(10),由于是固定长度,可以看到selectsh虽然只有八个字符,但len还是同定义的长度len=10,长度不足用空格(character 32)去补足.
select t_char,dump(t_char) from scott.tb_type;
/*
T_CHAR   DUMP(T_CHAR)
selectsh     Typ=96 Len=10: 115,101,108,101,99,116,115,104,32,32
*/
#dump出来的115,101,108...是字符对应的ascii值
select chr(115),chr(101) from dual;
/*
CHR(115)   CHR(101)
s           e
*/
#更新t_char的值为中文,在v$nls_parameters中可以看到NLS_CHARACTERSET使用的字符集是AL32UTF8,AL32UTF8中一个中文字符占3个字节.注意另外还有一个常用的字符集ZHS16GBK一个中文字符是占2个字节.
update scott.tb_type
set t_char ='小苹果';
commit;
#可以看到3个中文字符占9个字节,最后一个用32(16进制20)补足
select t_char,dump(t_char,16) from scott.tb_type;
/*
T_CHAR   DUMP(T_CHAR,16)
小苹果    Typ=96 Len=10: e5,b0,8f,e8,8b,b9,e6,9e,9c,20
*/
#dump出来的e5,b0,8f...是字符对应的ascii 16进制值
select to_char(ascii('小'),'xxxxxx'),to_char(ascii('苹'),'xxxxxx'),to_char(ascii('果'),'xxxxxx') from dual;
/*
TO_CHAR(ASCII('小'),'XXXXXX')   TO_CHAR(ASCII('苹'),'XXXXXX')   TO_CHAR(ASCII('果'),'XXXXXX')
e5b08f                          e88bb9                          e69e9c
*/
#更新t_char的类型为char(3 char),再更新值.
update scott.tb_type set t_char =null;
alter table scott.tb_type modify t_char char(3 char);
update scott.tb_type set t_char ='小苹果';
commit;
insert into scott.tb_type(t_char) select 'sel' from dual;
#可以看到当定义为char(3 char),内部类型号Typ=96不变,可插入的数量按字符个数控制,但实际存储根据字符的字节数不同长度len不同.
select t_char,dump(t_char,16) from scott.tb_type;
/*
T_CHAR   DUMP(T_CHAR,16)
小苹果   Typ=96 Len=9: e5,b0,8f,e8,8b,b9,e6,9e,9c
sel       Typ=96 Len=3: 73,65,6c
*/
#回滚插入的第二条记录
rollback;

#varchar2类型
#varchar2类型定义的是可变长度,最大4000个字节,内部类型号Typ=1
#t_varchar2定义的是VARCHAR2(10),由于是可变长度,可以看到selectsh有八个字符,len是实际长度len=8,长度不足VARCHAR2(10)无需补足.
select t_varchar2,dump(t_varchar2) from scott.tb_type;
/*
T_VARCHAR2   DUMP(T_VARCHAR2)
selectsh   Typ=1 Len=8: 115,101,108,101,99,116,115,104
*/
#varchar2类型dump的值转换回字面值,VARCHAR2(x char)原理同char类型

#nchar类型
#nchar类型定义的是固定长度,最大2000个字节,内部类型号同char类型Typ=96
#NCHAR类型会根据v$nls_parameters中NLS_NCHAR_CHARACTERSET的字符集确定字符所占的字节数,这里不同于char和varchar2的是单字节字符在这里也会按NLS_NCHAR_CHARACTERSET的字符集中字符所占的字节数确认实际存储.
#t_nchar定义的是NCHAR(10),由于是固定长度,AL16UTF16一个字符占两个字节,字符串selectsh虽然只有八个字符占16个字节,但len的长度是len=20,长度不足用空格(character 0,32)去补足.
#单字节字符在NCHAR中用0补足,例如字符s对应0,115
select t_nchar,dump(t_nchar) from scott.tb_type;
T_NCHAR   DUMP(T_NCHAR)
selectsh     Typ=96 Len=20: 0,115,0,101,0,108,0,101,0,99,0,116,0,115,0,104,0,32,0,32
#更新t_nchar的值为中文,看到一个中文字符也是占两个字节,对应的是ASCIISTR转换出来的值.
update scott.tb_type set t_nchar ='小苹果';
select t_nchar,dump(t_nchar,16) from scott.tb_type;
T_NCHAR   DUMP(T_NCHAR,16)
小苹果          Typ=96 Len=20: 5c,f,82,f9,67,9c,0,20,0,20,0,20,0,20,0,20,0,20,0,20
select ASCIISTR(t_nchar) from scott.tb_type;
ASCIISTR(T_NCHAR)
\5C0F\82F9\679C

#nvarchar2类型
#nvarchar2类型定义的是可变长度,最大4000个字节,内部类型号同varchar2类型Typ=1
#NCHAR类型会根据v$nls_parameters中NLS_NCHAR_CHARACTERSET的字符集确定字符所占的字节数,这里不同于char和varchar2的是单字节字符在这里也会按NLS_NCHAR_CHARACTERSET的字符集中字符所占的字节数确认实际存储.
#t_nvarchar2定义的是NVARCHAR2(10),由于是可变长度,可以看到selectsh有八个字符,len是实际长度len=16,长度不足NVARCHAR2(10)=2*10=20无需补足.
select t_nvarchar2,dump(t_nvarchar2) from scott.tb_type;
T_NVARCHAR2   DUMP(T_NVARCHAR2)
selectsh   Typ=1 Len=16: 0,115,0,101,0,108,0,101,0,99,0,116,0,115,0,104
#nvarchar2类型dump的值转换回字面值原理同nchar类型

#number类型
#number类型是可变长度,内部类型号Typ=2
#t_number定义为NUMBER(10),t_number_2定义为NUMBER(10,2),t_integer定义为INTEGER,这三个字段插入的值都是12.345,但由于定义的不同,存储的实际是12,12.35,12.这根据number(precision [,scale])在定义时决定,integer等同于number(precision).
select t_number,dump(t_number) from scott.tb_type;
/*
T_NUMBER   DUMP(T_NUMBER)
12   Typ=2 Len=2: 193,13
*/
select t_number_2,dump(t_number_2) from scott.tb_type;
/*
T_NUMBER_2   DUMP(T_NUMBER_2)
12.35   Typ=2 Len=3: 193,13,36
*/
select t_integer,dump(t_integer) from scott.tb_type;
/*
T_INTEGER   DUMP(T_INTEGER)
12   Typ=2 Len=2: 193,13
*/
#dump出来的值的格式是sign bit/exponent,digit1,digit2,…,digit20
#exponent byte由三部分组成,sign bit,offset,exponent.
#sign根据第一个字节最高位是0还是1决定是负数还是正数.offset恒为65.exponent值的区间为-65~62,为基于100为底的指数形式.
#如果第一个字节的值大于等于128,则为正数,exponent=第一个字节的值-128-65=第一个字节的值-193.这样根据exponent依次减1,与digit减1的值相乘,然后再相加即可得出实际值.
#如果第一个字节的值小于128,则为负数,exponent=(255-第一个字节的值)-128-65=62-第一个字节的值.这样根据exponent依次减1,与101减digit的值相乘(最后一个digit是102为标记不参与计算),然后再相加,最后加负号即可得出实际值.
#例如:
select dump(12.35) from dual;
/*
DUMP(12.35)
Typ=2 Len=3: 193,13,36
*/
12.35=193,13,36的转换形式是,因为193大于128,exponent为193-193=0,
所以值为:(100^0)*(13-1)+(100^-1)*(36-1)
       =1*12+0.01*35
       =12.35
#例如:
select dump(0.357896) from dual;
/*
DUMP(0.357896)
Typ=2 Len=4: 192,36,79,97
*/
0.357896=192,36,79,97的转换形式是,因为192大于128,exponent为192-193=-1,
所以值为:(100^-1)*(36-1)+(100^-1)*(79-1)+(100^-2)*(97-1)
       =0.01*36+0.0001*79+0.000001*97
       =0.357896
#例如:
select dump(-159.94941637) from dual;
/*
DUMP(-159.94941637)
Typ=2 Len=8: 61,100,42,7,7,85,64,102
*/
-159.94941637=61,100,42,7,7,85,64,102的转换形式是,因为61小于128,exponent为62-61=1,
所以值为:(100^1)*(101-100)+(100^0)*(101-42)+(100^-1)*(101-7)+(100^-2)*(101-7)+(100^-3)*(101-85)+(100^-4)*(101-64)
       =100*1+1*59+0.01*94+0.0001*94+0.000001*16+0.00000001*37
       =159.94941637即-159.94941637

#date类型
#date类型为固定长度len=7,没有毫秒位和时区,内部类型号Typ=12
#前两个字节为世纪和年,世纪和年存储是有加100,接下来两个字节是月和日,最后三个字节是24小时格式的时分秒,时分秒存储是都有加1.
select t_date,dump(t_date) from scott.tb_type;
/*
T_DATE   DUMP(T_DATE)
2016/2/3 17:13:34   Typ=12 Len=7: 120,116,2,3,18,14,35
*/
#2016/2/3 17:13:34=120,116,2,3,18,14,35,其中世纪120-100=20,年=116-100=16,月=2,日=3,时=18-1=17,分=14-1=13,秒=35-1=34

#sysdate类型
#sysdate也是一个内部类型,虽然不能用户定义的时候使用,固定长度len=8,没有毫秒位和时区,内部类型号Typ=13
#前两个字节为世纪和年,第一个字节和第二个字节的顺序在不同平台不同,在sun等平台当第一个字节小于128为AD公元,年=第一个字节值*256+第二个字节值;当第一个字节大于128为BC公元前,年=(256-第一个字节值)*256+第二个字节值
#在linux x86_64平台下正好相反,当第二个字节小于128为AD公元后,年=第二个字节值*256+第一位值;当第二个字节大于128为BC公元前,年=(256-第二位值)*256+第一个字节值
#接下来两个字节是月和日,再接下来三个字节是24小时格式的时分秒,最后一个字节不使用.
select sysdate,dump(sysdate) from dual;
/*
SYSDATE   DUMP(SYSDATE)
2016/2/3 17:23:46   Typ=13 Len=8: 224,7,2,3,17,23,46,0
*/
#2016/2/3 17:23:46=224,7,2,3,17,23,46,0,年=7*256+224=2016,月=2,日=3,时=17,分=23,秒=46

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28539951/viewspace-1986367/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28539951/viewspace-1986367/