Oracle 資料類型及儲存方式（一）

概述
通過執行個體，全面而深入的分析oralce的基礎資料型別 (Elementary Data Type)及它們的儲存方式。以ORACLE 10G為基礎，介紹oralce 10g引入的新的資料類型。讓你對oracle資料類型有一個全新的認識。揭示一些不為人知的秘密和被忽略的盲點。從實用和最佳化的角度出發，討論每種資料類型的特點。從這裡開始oracle之旅！
第一部份 字元類型
§1.1  char
定長字串，會用空格來填充來達到其最大長度,最長2000個位元組。
1． 建立一個測試表test_char.,只有一個char類型的列。長度為10
SQL> create table test_char(colA char(10));
Table created
2． 向這個表中插入一些資料。
SQL> insert into test_char values('a');
1 row inserted
SQL> insert into test_char values('aa');
1 row inserted
SQL> insert into test_char values('aaa');
1 row inserted
SQL> insert into test_char values('aaaa');
1 row inserted
SQL> insert into test_char values('aaaaaaaaaa');
1 row inserted
注意：最多隻能插入10個位元組。否是就報錯。
SQL> insert into test_char values('aaaaaaaaaaa');
insert into test_char values('aaaaaaaaaaa')
ORA-12899: value too large for column "PUB_TEST"."TEST_CHAR"."COLA" (actual: 11, maximum: 10)
3． 使用dump函數可以查看每一行的內部存數結構。
SQL> select colA, dump(colA) from test_char;
COLA       DUMP(COLA)
---------- --------------------------------------------------------------------------------
a          Typ=96 Len=10: 97,32,32,32,32,32,32,32,32,32
aa         Typ=96 Len=10: 97,97,32,32,32,32,32,32,32,32
aaa        Typ=96 Len=10: 97,97,97,32,32,32,32,32,32,32
aaaa       Typ=96 Len=10: 97,97,97,97,32,32,32,32,32,32
aaaaaaaaaa Typ=96 Len=10: 97,97,97,97,97,97,97,97,97,97
注意：Typ=96 表示資料類型的ID。Oracle為每一種資料類型都進行了編號。說明char類型的編號是96.
Len =10 表示所在的內部儲存的長度(用位元組表示)。雖然第一例只存了一個字元’a’，但是它還是佔用了10個位元組的空間。
97,32,32,32,32,32,32,32,32,32 表示內部儲存方式。可見oracle的內部儲存是以資料庫字元集進行儲存的。
97正好是字元a的ASCII碼。
可以使用chr函數把ASCII碼轉成字元。
SQL> select chr(97) from dual;
CHR(97)
-------
a
要想知道一個字元的ASCII碼，可以使用函數ascii
SQL> select ascii('a') from dual;
ASCII('A')
----------
        97
32正好是空格的ascii碼值。
Char類型是定長類型。它總會以空格來填充以達到一個固定寬度。
使用char類型會浪費儲存空間。
Oracle的資料類型的長度單位是位元組。
SQL> select dump('漢') from dual;
DUMP('漢')
---------------------
Typ=96 Len=2: 186,186
可見一個漢字在oracle中是佔用了兩個位元組的。
英文字母或符號只佔用一個位元組。
Char(10)最多可存放5個漢字。
§1.2  varchar2
是一種變長的字元類型。最多可佔用4000位元組的儲存空間。
1. 建立一個表,只有一列，類型為varchar2,長度為10
SQL> create table test_varchar( col varchar2(10));
Table created
2. 插入一些資料
SQL> insert into test_varchar values('a');
1 row inserted
SQL> insert into test_varchar values('aa');
1 row inserted
SQL> insert into test_varchar values('aaa');
1 row inserted
SQL> insert into test_varchar values('aaaaaaaaaa');
1 row inserted
SQL> insert into test_varchar values('aaaaaaaaaaa');
2. 用dump函數查看每一行的內部儲存結構。
SQL> select col, dump(col) from test_varchar;
COL        DUMP(COL)
---------- --------------------------------------------------------------------------------
a          Typ=1 Len=1: 97
aa         Typ=1 Len=2: 97,97
aaa        Typ=1 Len=3: 97,97,97
aaaaaaaaaa Typ=1 Len=10: 97,97,97,97,97,97,97,97,97,97
Typ=1,說明varchar2類型在oracle中的類型編號為1
Len代表了每一行資料所佔用的位元組數。
後面是具體的儲存值。
由此可見，varchar2是存多少就佔用多少空間。比較節省空間的。不會像char那樣用空格填充。
§1.3  byte 和char
在10g中，字元類型的寬度定義時，可以指定單位。
Byte就是位元組。
Char就是字元。
Varchar2(10 byte) 長度為10個位元組。
Varchar2(10 char) 長度為10個字元所佔的長度。
Char(10 byte)長度為10個位元組。
Char(10 char) 長度為10個字元所佔的長度。
一個字元佔用多少個位元組，是由當前系統採用的字元集來決定的。
如一個漢字佔用兩個位元組。
查看當前系統採用的字元集
SQL> select * from nls_database_parameters where parameter ='NLS_CHARACTERSET';
PARAMETER                      VALUE
------------------------------ --------------------------------------------------------------------------------
NLS_CHARACTERSET               ZHS16GBK
如果在定義類型時，不指定單位。預設是按byte，即以位元組為單位的。
採用char為單位的好處是，使用多位元組的字元集。
比如，在ZHS16GBK字元集中，一個漢字佔用兩個位元組。
把資料表的某一列長度定義為可存放10個漢字，通過下面的定義就可以了。
Create table test_varchar(col_char  varchar2(10 char));
這樣相對簡單一些。在資料庫表設計時需要注意。
繼續實驗，建立一個表，包含兩列。一列採用byte為單位，一列採用char為單位
SQL> create table test_varchar2 (col_char varchar2(10 char),col_byte varchar2(10 byte));
Table created
Col_char列，定義為可存放10個字元。
Col_byte 列，定義為可存放10個位元組的字元。
當前的系統採用字元集為ZHS16GBK.所以一個字元佔兩個位元組。
試著在表中插入一些資料
SQL> insert into test_varchar2 values('a','a');
1 row inserted
SQL> insert into test_varchar2 values('袁','a');
1 row inserted
SQL> insert into test_varchar2 values('袁袁袁袁袁袁袁袁袁袁','aaaaaaaaaa');
1 row inserted
SQL> insert into test_varchar2 values('袁袁袁袁袁袁袁袁袁袁','袁袁袁袁袁袁袁袁袁袁');
insert into test_varchar2 values('袁袁袁袁袁袁袁袁袁袁','袁袁袁袁袁袁袁袁袁袁')
ORA-12899: value too large for column "PUB_TEST"."TEST_VARCHAR2"."COL_BYTE" (actual: 20, maximum: 10)
第一次， 在兩列中都插入字元a
第二次， 在col_char列插入字元’袁’,在col_byte插入字元a
第三次， 在col_char列中插入10個中文字元’袁’,在col_byte插入10個字元a
第四次， 在兩列中都插入中文字元’袁’時，報錯了。第二列長度不夠。
   再看看每一行的儲存結構
SQL> select col_char, dump(col_char) from test_varchar2;
COL_CHAR             DUMP(COL_CHAR)
-------------------- --------------------------------------------------------------------------------
a                    Typ=1 Len=1: 97
袁                   Typ=1 Len=2: 212,172
袁袁袁袁袁袁袁袁袁袁 Typ=1 Len=20: 212,172,212,172,212,172,212,172,212,172,212,172,212,172,212,172,21
當我們在col_char列插入10個漢字時，它的長度為20.
儘管我們在定義的時候是採用varchar2(10,char).
由此可見,oracle是根據當前資料庫採用的字元集，每個字元的所佔位元組數 X 欄位長度來決定了該欄位所佔的位元組數。
在本例中，varchar2(10,char)相當於varchar2(20).
不信，我們可以試試看。
SQL> desc test_varchar2;
Name     Type         Nullable Default Comments
-------- ------------ -------- ------- --------
COL_CHAR VARCHAR2(20) Y                        
COL_BYTE VARCHAR2(10) Y 
當採用多位元組的字元集時，定義欄位長度還是採用char為單位指定為佳。因為可以避免欄位長度的問題。
當不知道當前資料庫採用的字元集，一個字元佔用多少位元組時，可以使用lengthb函數。
SQL> select lengthb('袁') from dual;
LENGTHB('袁')
-------------
            2
§1.4  char還是varchar
1. 建立一個表,一列為char類型，一列為varchar2類型
SQL> create table test_char_varchar(char_col char(20),varchar_col varchar2(20));
Table created
2. 向該表中的兩列都插入相關的資料
SQL> insert into test_char_varchar values('Hello World','Hello World');
1 row inserted
SQL> select * from test_char_varchar;
CHAR_COL             VARCHAR_COL
-------------------- --------------------
Hello World          Hello World
3. 以char_col列為條件查詢
SQL> select * from test_char_varchar where char_col ='Hello World';
CHAR_COL             VARCHAR_COL
-------------------- --------------------
Hello World          Hello World
4. 以varchar_col列為條件查詢
SQL> select * from test_char_varchar where varchar_col ='Hello World';
CHAR_COL             VARCHAR_COL
-------------------- --------------------
Hello World          Hello World
5.似乎char 和varchar類型沒有什麼兩樣。再看看下面的語句。
SQL> select * from test_char_varchar where varchar_col =char_col;
CHAR_COL             VARCHAR_COL
-------------------- --------------------
這已經看出他們並不一樣，這涉及到字串比較的問題。
因為已經發生了隱式轉換,在與char列char_col進行比較時，char_col列的內容已經轉換成了char(20).在Hello World後面以空格進行填充了。而varchar_col列並沒有發生這種轉換。
如果要讓char_col列與varchar_col列相等。有兩種方法。
第一種是：使用trim把char_col列的空格去掉。
第二種是：使遙rpad把varchar_col列用空格進行填充長度為20的字元。
SQL> select * from test_char_varchar where trim(char_col) = varchar_col;
CHAR_COL             VARCHAR_COL
-------------------- --------------------
Hello World          Hello World
SQL> select * from test_char_varchar where char_col = rpad(varchar_col,20);
CHAR_COL             VARCHAR_COL
-------------------- --------------------
Hello World          Hello World
如果使用trim函數，如果char_col列上有索引，那麼索引將不可用了。
此外還會在綁定變數時出現問題。

§1.5  NCHAR和NVARCHAR2
如果系統需要集中管理和儲存多種字元集，就需要使用這兩種字元類型。在使用NCAHR和NVARCHAR2時，常值內容採用國家字元集來儲存和管理。而不是預設字元集。
這兩種類型的長度指的是字元數，而不是位元組數。
NLS國家語言支援(National Language Support)
在oracle 9i及以後的版本，資料庫的國家字元集可以是：utf-8和AL16UTF-16兩種。
Oracle 9i是utf -8, Oralce 10g是AL16UTF-16.
1.建立一個表,有兩列，類型分別為：nchar和nvarchar2.長度都為10
SQL> create table test_nvarchar(col_nchar nchar(10),col_nvarchar2 nvarchar2(10));
Table created
2.插入一些資料
SQL> insert into test_nvarchar values('袁','袁光東');
1 row inserted
SQL> insert into test_nvarchar values(N'袁',N'袁光東');
1 row inserted
(在9i之前的版本，插入時加上N時，在處理時跟普通方式有不同的方式。但是在10g的時候已經有了改變，加不加N都是一樣,這裡只是為了測試)
SQL> insert into test_nvarchar values('a','b');
1 row inserted
插入一行英文字母
3. 查看每行的col_nchar列的儲存方式。
SQL> select col_nchar, dump(col_nchar) from test_nvarchar;
COL_NCHAR            DUMP(COL_NCHAR)
-------------------- --------------------------------------------------------------------------------
袁                   Typ=96 Len=20: 136,129,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32
a                    Typ=96 Len=20: 0,97,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32
袁                   Typ=96 Len=20: 136,129,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32,0,32
Typ=96 與char的類型編碼一樣。
Len=20 每一行的長度都是20位元組。這一點跟char一樣。都是定長的，會以空格填充。
需要注意的是：統統以兩位來表示一個字元。
136,129 表示’袁’
0,97 表示’a’
0,32 表示空格。
4. nvarchar2的儲存
SQL> select col_nvarchar2, dump(col_nvarchar2) from test_nvarchar;
COL_NVARCHAR2        DUMP(COL_NVARCHAR2)
-------------------- --------------------------------------------------------------------------------
袁光東               Typ=1 Len=6: 136,129,81,73,78,28
b                    Typ=1 Len=2: 0,98
袁光東               Typ=1 Len=6: 136,129,81,73,78,28
Typ=1 與varchar2一樣。
每一行的len值都不樣同。不會使用空格進行填充。
每一個字元都佔有兩個位元組兩進行儲存。
b 儲存為： 0, 98
袁 儲存為： 136,129
5.nchar和nvarchar2的資料定義。
SQL> desc test_nvarchar;
Name          Type          Nullable Default Comments
------------- ------------- -------- ------- --------
COL_NCHAR     NCHAR(20)     Y                        
COL_NVARCHAR2 NVARCHAR2(20) Y   
雖然在定義nchar和nvarchar2時，指定的長度是指字元數。但是表結構的定義中，仍然是儲存著它的位元組數。
在定義時nchar(10)表示可以最大儲存10個字元。
在查看資料表結構時，顯示該列最大佔用的位元組數。
需要注意的是：在char和nchar中對漢字的實際儲存值是不一樣的。因為採用了不同的字元集，就有了不同的字元編碼。
SQL> insert into test_varchar values('袁');
1 row inserted
SQL> select col, dump(col) from test_varchar where col='袁';
COL        DUMP(COL)
---------- --------------------------------------------------------------------------------
袁         Typ=1 Len=2: 212,172
這時採用的字元集系統預設字元集ZHS16GBK。
這裡很容易的把它轉換成ascii碼。
高位 * 256(2的8次方) + 低位.
212 * 256 + 172 = 54444
SQL> select chr(54444) from dual;
CHR(54444)
----------
袁
而在Nchar 和Nvarchar中，採用的是UTF-8或UTF-16的字元集。
SQL> insert into test_nvarchar values('袁','袁');
1 row inserted
SQL> select col_nvarchar2, dump(col_nvarchar2) from test_nvarchar where col_nvarchar2='袁';
COL_NVARCHAR2        DUMP(COL_NVARCHAR2)
-------------------- --------------------------------------------------------------------------------
袁                   Typ=1 Len=2: 136,129
‘袁’儲存的值為：136,129
Oracle 10以上對nchar和nvarchar都採用utf-16字元集了。它的好處就是對字元採用固定長度的位元組儲存(2位元組),支援多國字元，在操作效率上會更高。但是它卻無法相容於ascii碼。
§1.6  RAW
RAW與CHAR和VARCHAR2相比。RAW屬於位元據，更可以把它稱為二進位串。在對CHAR和VARCHAR2類型進行儲存時，會進行字元集轉換。而對位元據進行儲存則不會進行字元集轉換。
SQL> create table test_raw (col_chr varchar2(10), col_raw raw(10));
Table created
SQL> insert into test_raw values('aa','aa');
1 row inserted
SQL> commit;
Commit complete
SQL> select * from test_raw;
COL_CHR    COL_RAW
---------- --------------------
aa         AA
SQL> select col_chr,dump(col_chr) from test_raw;
COL_CHR    DUMP(COL_CHR)
---------- --------------------------------------------------------------------------------
aa         Typ=1 Len=2: 97,97
SQL> select col_raw,dump(col_raw) from test_raw;
COL_RAW              DUMP(COL_RAW)
-------------------- --------------------------------------------------------------------------------
AA                   Typ=23 Len=1: 170
通過上面的分析，雖然我們通過select查詢得到的結果，raw列顯示為插入的字元。但是我們通過dump函數得知到raw並不是以字元的方式儲存。它是把插入的字元認為是16進位的值。
比如本例，我們向raw列插入aa,但是它佔用的空間為1個位元組。值為170.
170轉為16進位正好是aa
向raw列插入資料時會發生一個隱式轉換HEXTORAW
從raw列讀取資料時會發生一個隱式轉換RAWTOHEX
如果向raw列插入值不是有效十六進位值時，會報錯的。
SQL> insert into test_raw values('h','h');
insert into test_raw values('h','h')
ORA-01465: invalid hex number