    這一節主要介紹 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

oracle 各資料型別dump說明(一)

相關文章