Oracle數據類型及存儲方式

　　概述

　　通過實例全面而深入的分析oralce的基本數據類型及它們的存儲方式以ORACLE G為基礎介紹oralce g引入的新的數據類型讓你對oracle數據類型有一個全新的認識揭示一些不為人知的秘密和被忽略的盲點從實用和優化的角度出發討論每種數據類型的特點從這裡開始oracle之旅！

　　第一部份 字符類型

　　§ char

　　定長字符串會用空格來填充來達到其最大長度最長個字節

　　． 新建一個測試表test_char只有一個char類型的列長度為

　　SQL> create table test_char(colA char());

　　Table created

　　． 向這個表中插入一些數據

　　SQL> insert into test_char values(a);

　　 row inserted

　　SQL> insert into test_char values(aa);

　　 row inserted

　　SQL> insert into test_char values(aaa);

　　 row inserted

　　SQL> insert into test_char values(aaaa);

　　 row inserted

　　SQL> insert into test_char values(aaaaaaaaaa);

　　 row inserted

　　注意最多只能插入個字節否是就報錯

　　SQL> insert into test_char values(aaaaaaaaaaa);

　　insert into test_char values(aaaaaaaaaaa)

　　ORA: value too large for column PUB_TESTTEST_CHARCOLA (actual: maximum: )

　　． 使用dump函數可以查看每一行的內部存數結構

　　SQL> select colA dump(colA) from test_char;

　　COLA       DUMP(COLA)

　　

　　a          Typ= Len=:

　　aa         Typ= Len=:

　　aaa        Typ= Len=:

　　aaaa       Typ= Len=:

　　aaaaaaaaaa Typ= Len=:

　　注意Typ= 表示數據類型的IDOracle為每一種數據類型都進行了編號說明char類型的編號是

　　Len = 表示所在的內部存儲的長度(用字節表示)雖然第一例只存了一個字符a但是它還是占用了個字節的空間

　　 表示內部存儲方式可見oracle的內部存儲是以數據庫字符集進行存儲的

　　正好是字符a的ASCII碼

　　可以使用chr函數把ASCII碼轉成字符

　　SQL> select chr() from dual;

　　CHR()

　　

　　a

　　要想知道一個字符的ASCII碼可以使用函數ascii

　　SQL> select ascii(a) from dual;

　　ASCII(A)

　　

　　

　　正好是空格的ascii碼值

　　Char類型是定長類型它總會以空格來填充以達到一個固定寬度

　　使用char類型會浪費存儲空間

　　Oracle的數據類型的長度單位是字節

　　SQL> select dump(漢) from dual;

　　DUMP(漢)

　　

　　Typ= Len=:

　　可見一個漢字在oracle中是占用了兩個字節的

　　英文字母或符號只占用一個字節

　　Char()最多可存放個漢字

　　§ varchar

　　是一種變長的字符類型最多可占用字節的存儲空間

　　 創建一個表只有一列類型為varchar長度為

　　SQL> create table test_varchar( col varchar());

　　Table created

　　 插入一些數據

　　SQL> insert into test_varchar values(a);

　　 row inserted

　　SQL> insert into test_varchar values(aa);

　　 row inserted

　　SQL> insert into test_varchar values(aaa);

　　 row inserted

　　SQL> insert into test_varchar values(aaaaaaaaaa);

　　 row inserted

　　SQL> insert into test_varchar values(aaaaaaaaaaa);

　　 用dump函數查看每一行的內部存儲結構

　　SQL> select col dump(col) from test_varchar;

　　COL        DUMP(COL)

　　

　　a          Typ= Len=:

　　aa         Typ= Len=:

　　aaa        Typ= Len=:

　　aaaaaaaaaa Typ= Len=:

　　Typ=說明varchar類型在oracle中的類型編號為

　　Len代表了每一行數據所占用的字節數

　　後面是具體的存儲值

　　由此可見varchar是存多少就占用多少空間比較節省空間的不會像char那樣用空格填充

　　§ byte 和char

　　在g中字符類型的寬度定義時可以指定單位

　　Byte就是字節

　　Char就是字符

　　Varchar( byte) 長度為個字節

　　Varchar( char) 長度為個字符所占的長度

　　Char( byte)長度為個字節

　　Char( char) 長度為個字符所占的長度

　　一個字符占用多少個字節是由當前系統采用的字符集來決定的

　　如一個漢字占用兩個字節

　　查看當前系統采用的字符集

　　SQL> select * from nls_database_parameters where parameter =NLS_CHARACTERSET;

　　PARAMETER                      VALUE

　　

　　NLS_CHARACTERSET               ZHSGBK

　　如果在定義類型時不指定單位默認是按byte即以字節為單位的

　　采用char為單位的好處是使用多字節的字符集

　　比如在ZHSGBK字符集中一個漢字占用兩個字節

　　把數據表的某一列長度定義為可存放個漢字通過下面的定義就可以了

　　Create table test_varchar(col_char varchar( char));

　　這樣相對簡單一些在數據庫表設計時需要注意

　　繼續實驗新建一個表包含兩列一列采用byte為單位一列采用char為單位

　　SQL> create table test_varchar (col_char varchar( char)col_byte varchar( byte));

　　Table created

　　Col_char列定義為可存放個字符

　　Col_byte 列定義為可存放個字節的字符

　　當前的系統采用字符集為ZHSGBK所以一個字符占兩個字節

　　試著在表中插入一些數據

　　SQL> insert into test_varchar values(aa);

　　 row inserted

　　SQL> insert into test_varchar values(袁a);

　　 row inserted

　　SQL> insert into test_varchar values(袁袁袁袁袁袁袁袁袁袁aaaaaaaaaa);

　　 row inserted

　　SQL> insert into test_varchar values(袁袁袁袁袁袁袁袁袁袁袁袁袁袁袁袁袁袁袁袁);

　　insert into test_varchar values(袁袁袁袁袁袁袁袁袁袁袁袁袁袁袁袁袁袁袁袁)

　　ORA: value too large for column PUB_TESTTEST_VARCHARCOL_BYTE (actual: maximum: )

　　第一次 在兩列中都插入字符a

　　第二次 在col_char列插入字符袁在col_byte插入字符a

　　第三次 在col_char列中插入個中文字符袁在col_byte插入個字符a

　　第四次 在兩列中都插入中文字符袁時報錯了第二列長度不夠

　　再看看每一行的存儲結構

　　SQL> select col_char dump(col_char) from test_varchar;

　　COL_CHAR             DUMP(COL_CHAR)

　　

　　a                    Typ= Len=:

　　袁                   Typ= Len=:

　　袁袁袁袁袁袁袁袁袁袁 Typ= Len=:

　　當我們在col_char列插入個漢字時它的長度為

　　盡管我們在定義的時候是采用varchar(char)

　　由此可見oracle是根據當前數據庫采用的字符集每個字符的所占字節數 X 字段長度來決定了該字段所占的字節數

　　在本例中varchar(char)相當於varchar()

　　不信我們可以試試看

　　SQL> desc test_varchar;

　　Name     Type         Nullable Default Comments

　　

　　COL_CHAR VARCHAR() Y

　　COL_BYTE VARCHAR() Y

　　當采用多字節的字符集時定義字段長度還是采用char為單位指定為佳因為可以避免字段長度的問題

　　當不知道當前數據庫采用的字符集一個字符占用多少字節時可以使用lengthb函數

　　SQL> select lengthb(袁) from dual;

　　LENGTHB(袁)

　　

　　

　　§ char還是varchar

　　 新建一個表一列為char類型一列為varchar類型

　　SQL> create table test_char_varchar(char_col char()varchar_col varchar());

　　Table created

　　 向該表中的兩列都插入相關的數據

　　SQL> insert into test_char_varchar values(Hello WorldHello World);

　　 row inserted

　　SQL> select * from test_char_varchar;

　　CHAR_COL             VARCHAR_COL

　　

　　Hello World          Hello World

　　 以char_col列為條件查詢

　　SQL> select * from test_char_varchar where char_col =Hello World;

　　CHAR_COL             VARCHAR_COL

　　

　　Hello World          Hello World

　　 以varchar_col列為條件查詢

　　SQL> select * from test_char_varchar where varchar_col =Hello World;

　　CHAR_COL             VARCHAR_COL

　　

　　Hello World          Hello World

　　似乎char 和varchar類型沒有什麼兩樣再看看下面的語句

　　SQL> select * from test_char_varchar where varchar_col =char_col;

　　CHAR_COL             VARCHAR_COL

　　

　　這已經看出他們並不一樣這涉及到字符串比較的問題

　　因為已經發生了隱式轉換在與char列char_col進行比較時char_col列的內容已經轉換成了char()在Hello World後面以空格進行填充了而varchar_col列並沒有發生這種轉換

　　如果要讓char_col列與varchar_col列相等有兩種方法

　　第一種是使用trim把char_col列的空格去掉

　　第二種是使遙rpad把varchar_col列用空格進行填充長度為的字符

　　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);

　　CHAR_COL             VARCHAR_COL

　　

　　Hello World          Hello World

　　如果使用trim函數如果char_col列上有索引那麼索引將不可用了

　　此外還會在綁定變量時出現問題

From:http://tw.wingwit.com/Article/program/Oracle/201311/17771.html