oracle資料類型

標籤：

原文: oracle資料類型

 

 

oracle資料類型 

 

 

有道是，磨刀不誤砍柴工。多瞭解一些底層的東西，對於Oracle開發、維護大有裨益。個人總結了一些Oracle資料類型集解，相信讀者閱讀了本文以後，Oracle資料庫開發起來會事半功倍！

在Oracle資料庫中，每個關係表都由許多列組成。給每一列指派特定的資料類型來定義將在這個列中儲存得資料類型。

1、CHAR
最多可以以固定長度的格式儲存2000個字元或位元組。預設指定為以字元形式進行儲存，這個資料類型是固定長度的，並且當位元不夠時，需要在其右邊添加空格來補滿。

例如：
CREATE TABLE test
(name char(20))

2、VARCHAR和VARCHAR2
最多可以以可變長度來儲存4000B，因此不需要空格來作補充。VARCHAR2 比 VARCHAR 更適合使用，由於相容性的原因，所以仍然在 Oracle 資料庫中保留著 VARCHAR 。

例如：
CREATE TABLE test
(name varchar2(20))

3、NCHAR
NLS（national language support ， 國際語言支援）的資料類型僅可以儲存由資料庫 NLS 字元集定義的 Unicode 字元集。該資料類型最多可以儲存2000B。NCHAR 的列在位元不夠時需要在右邊填充空格。例如：
CREATE TABLE test
(name Nchar(20))
注意：在 Oracle9i 資料庫及其更新的版本中，僅使用 Unicode 資料類型

4、NVARCHAR2
NLS 的資料類型與 VARCHAR2 資料類型等價。這個資料類型最多可儲存4000B 。例如：
CREATE TABLE test
(name Nvarchar2(20))
注意：在 Oracle9i 資料庫及其更新的版本中，僅使用 Unicode 資料類型

5、NUMBER
用於儲存零、正數、定長負數以及浮點數。NUMBER 資料類型可以以 NUMBER(P,S)的

形式來定義數位精度和範圍。 這裡：p 表示精度 （1-38），它表示儲存在列中數

字的總長度是 p 位： s 表示範圍，它表示小數點後的位元。該取值範圍可以從-84 到 127 。例如：
CREATE TABLE test
(name number(5))
使用精度 5 來定義一個正數（如 12345）。
CREATE TABLE test
(name number(5,2))
使用精度 5 和範圍 2 來定義一個數字。符合這種資料類型的數字值如 123.45 和 12.34

6、LONG
LONG 類型的列儲存可變長度的字串，最多可以儲存 2GB 的資料。LONG 類型的列有很多在 VARCHAR2 類型列中所具有的特徵。可以使用 LONG 類型的列來儲存 LONG 類型的文本字串。LONG 資料類型的使用是為了向前相容的需要。建議使用 LOB 資料類型來代替 LONG 類型。例如：
CREATE TABLE test
(name long)

7、DATE
用於在資料庫中儲存日期和時間。儲存時間的精度可以達到 1/100s。不提供時區的相關資訊。例如：
CREATE TABLE test
(name DATE)

8、TIMESTAMP
使用年、月、日、小時、分鐘、秒域來對日期/時間提供更詳細的支援。最多可以使用 9 位元字的精度來儲存秒（受底層作業系統支援的限制）。這個資料類型沒有時區的相關資訊，它可以在 Oracle9i 資料庫及其更新的版本中使用。例如：
CREATE TABLE test
(timestamp_column TIMESTAMP);

9、TIMESTAMP WITH TIME ZONE
包含 TIMESTAMP 資料類型中的所有域，除此之外，還包含兩個額外的域：timezone_hour 和 timezone_minute。這個資料類型包含支援時區的相關資訊。這個資料類型可以在 Oracle9i 資料庫及其更新的版本中使用。例如：
CREATE TABLE test
(timestamp_column
TIMESTAMP WITH
TIME ZONE);

10、TIMESTAMP WITH LOCAL TIME ZONE
除了在資料庫中儲存的時區採用標準化以外，所包含的其他域與 TIMESTAMP 資料類型中的域相同。當選擇列時，將日期/時間標準為會話的時區。這個資料類型可以在 Oracle9i 資料庫及其更新的版本中使用。例如
CREATE TABLE test
(timestamp_column
TIMESTAMP WITH
LOCAL TIME ZONE);

11、INTERVAL YEAR TO MONTH
用於儲存一個時間段，由月份和年組成。需要 5B 來儲存。這個資料類型可以在 Oracle9i 資料庫及其更新的版本中使用。例如：
SELECT INTERVAL
‘01-05‘
YEAR TO MONTH -
INTERVAL ‘01-02‘ YEAR
TO MONTH FROM dual;

12、INTERVAL DAY TO SECOND
用於儲存一個時間段，由日和秒組成。需要 11B 來儲存。這個資料類型可以在 Oracle9i 資料庫及其更新的版本中使用。例如：
SELECT INTERVAL
‘100 10:20:42.22‘
DAY(3) TO SECOND(2) -
INTERVAL
‘101 10:20:42.22‘
DAY(3) TO SECOND(2)
FROM dual;

13、RAW
用於儲存 raw 類型的位元據。最多可以儲存 2000B。建議使用 BLOB 來代替它。例如：
CREATE TABLE test
(raw_column
RAW(2000));

14、LONG RAW
用於儲存 raw 類型的位元據。最多可以儲存 2GB 的資料。建議使用BLOB來代替它。例如：
CREATE TABLE test
(raw_column LONG RAW);

15、ROWID
表中 ROWID 類型的字串表示。使用這個資料類型來儲存由 ROWID 類型偽列的傳回值。例如：
CREATE TABLE test
(rowid_column ROWID);

16、UROWID
在索引組織表中表示邏輯行地址。例如：
CREATE TABLE test
(urowid_column UROWID);

17、CLOB
用於儲存基於字元的大對象。在 Oracle9i 資料庫中最多可以儲存 4GB 的資料，這比 Oracle10g 資料庫中可儲存的最大資料還要大，這也是現在資料庫規定塊大小的一個因素（~4GB* 資料區塊大小）。例如：
CREATE TABLE test
(clob_column CLOB);

18、NCLOB
可以使用由資料庫國際字元集所定義的字元集來儲存僅為 Unicode 類型的基於字元的資料。最多可以儲存 4GB 的資料，這比 Oracle10g 資料庫中可儲存的最大資料還要大，這也是現在資料庫規定塊大小的一個因素（~4GB* 資料區塊大小）。例如：
CREATE TABLE test
(nclob_column NCLOB);

19、BLOB
最多可以儲存 4GB 資料的二進位大對象，這比 Oracle10g資料庫中可儲存的最大資料還要大，這也是現在資料庫規定塊大小的一個因素（~4GB* 資料庫塊大小）。例如：
CREATE TABLE test
(blob_column BLOB);

20、BFILE
儲存指向資料庫外部檔案的定位器。外部檔案最大為 4GB。例如：
CREATE TABLE test
(bfile_column BFILE);

21、BINARY_FLOAT
該資料類型是一個基於 ANSI_IEEE745 標準的浮點數據類型。它定義了一個 32 位的雙精確度浮點數。這個資料類型需要 5B 的儲存空間。例如：
CREATE TABLE test
(b_float BINARY_FLOAT);

22、BINARY_DOUBLE
該資料類型是一個基於 ANSI_IEEE745 標準的雙精確度浮點數據類型，它定義了一個32位的雙精確度浮點數。這個資料類型需要 9B 節的儲存空間。例如：
CREATE TABLE test
(b_float BINARY_DOUBLE);

關於各種資料類型的注釋

1、關於 CHAR 和 VARCHAR2 資料類型的注釋
由資料庫字元集來確定特定的 CHAR、VARCHAR 或者 VARCHAR2 字元類型所佔的位元組數。多位元組字元集中的字元可以儲存 1~4B。CHAR 或 VARCHAR2 資料類型的大小由該資料類型可以儲存的位元組數或字元數來決定（這成為字元的語義）。所有定義的儲存大小都是預設以位元組為單位的。如果使用多位元組字元集（大部分常見的西方字元集都是以單位元組為單位的，而值得注意的是，有一個例外就是 UTF 字元集不是以單位元組為單位的），則可能需要定義以字元為單位的儲存，下面的例子說明了這個問題：

CREATE TABLE test
(name VARCHAR2(20 char));

建議在表的末尾處建立一些值為 NULL 的列。通過使用這種方法，可以用 VARCHAR2 資料類型來儲存一些資料，但不能使用 CHAR 資料類型來儲存資料，因為 Oracle 資料庫是以在一行中連續儲存多個 NULL 值的方式來進行儲存的。

Oracle9i 資料庫及其更新的版本提供資料壓縮功能，該功能僅在包含於唯讀資料表空間中的表中使用（也就是說，表中的資料將不會改變）。僅當通過下面的批量裝載（bulk-load）操作中的任意一種操作將資料裝載進表中時，才會執行資料壓縮操作

：
使用 create table as select(CTAS)操作來建立表。
在直接模式下的 insert 操作或者並行的 insert 操作。
SQL*Loader 裝載程式在直接模式下的操作。

注意：
如果使用 update 語句對資料進行了修改，則已經壓縮過的資料區塊將不會再次壓縮！因此，一張很小的表可以快速地增長成一張很大的表。通過使用 alter table move 命令可以在已經存在的表中壓縮資料。下面是建立壓縮表的例子和對已經存在的表進行壓縮的例子：

CREATE TABLE my_tab (id NUMBER,current_value VARCHAR2(2000)) COMPRESS;
ALTER TABLE my_comp_tab MOVE COMPRESS;

關於 CHAR 和 VARCHAR2 資料類型，常常會有這樣一個問題：到底是用哪一個資料類型最好？下面是對這個問題的一些指導性建議：
通常 VARCHAR2 資料類型比 CHAR 資料類型優先使用。
如果資料大小是變化的則在資料庫中使用 VARCHAR2 資料類型可以節省空間的。
如果 VARCHAR2 資料類型列中的資料需要頻繁更新，則 VARCHAR2 資料類型列的擴充可能會導致行串連或者行遷移的發生。當最終需要使用 VARCHAR2 資料類型列總大小的時候，可以考慮使用 CHAR 資料類型來代替 VARCHAR2 資料類型。

注意：
通常來說，使用 VARCHAR2(1)資料類型比使用 CHAR(1)資料類型開銷要大，但上面所講的則是例外。

2、關於 NUMBER 資料類型的注釋
NUMBER 資料類型內部使用科學計數法以可變長度格式來儲存資料。使用 1B 儲存指數，而另外 20B（這個位元組數可以變化）用於儲存該數字剩下的部分。這種儲存模式使得 NUMBER 資料類型可以表示的精度為 38 位。

如果想要確定給定的數字所佔位元組數的大小，可以使用 vsize 函數，如下所示：

SQL>SELECT VSIZE(100) FROM dual;
VSIZE(100)
----------
 2

在上面的例子中，該 vsize 函數用於將數位大小指定為100.使用 2B 儲存。1B 儲存數字，另 1B儲存指數。
也可以使用 dump 函數來確定任意列的具體大小，如下所示：
SQL>SELECT id,dump(id) did FROM test;
 ID DID
-----------------------------
 123 Typ=2 Len=3: 194,2,24
 141 Typ=2 Len=3: 194,2,42
     123456 Typ=2 Len=4: 195,13,35,57

NUMBER 資料類型可以有若干種形式來定義。可以根據需要使用或禁用精度和範圍的方式來定義 NUMBER 資料類型。如果使用特定的精度來定義 NUMBER 列，則當超過所定義的精度時，Oracle 資料庫將會產生錯誤。例如，NUMBER(6,2) 在儲存數字 1234.56 時，不會對該數位值產生任何影響，而在儲存數字 123.456 時將會對該數字進行四捨五入，最終儲存的數字將變成 123.46；如果以相同的 NUMBER 資料類型來儲存數字 12345.67 時，資料庫將產生錯誤，因為該數位精度是 7 而不是 6.

最後需要注意的是，也可以將列定義為沒有精度的 NUMBER 資料類型，這表明將把

NUMBER 資料類型的列作為沒有範圍屬性的整數來看待。

3、關於 LONG RAW 資料類型的注釋

LONG RAW 資料類型用於儲存資料庫無法解釋的位元據。這個資料類型最多能夠儲存 2GB 的資料並且它的儲存容量是可變的。Oracle 資料庫不鼓勵使用 LONG RAW 資料類型，因為該資料類型已經由 BLOB 資料類型所代替了。Oracle 資料庫

（Oracle9i資料庫及其更新的版本）已經提供了通過 alter table 命令將 LONG RAW 資料類型列轉換到相應的 CLOB 資料類型的能力。也可以使用 to_lob 函數將 LONG RAW 資料格式轉換為 BLOB 資料格式。

4、關於 LONG 資料類型的注釋

LONG 資料類型用於儲存大量的字元文本。LONG 資料類型會受到某些儲存的限制，最多可以儲存 2GB 的資料。Oracle 資料庫不鼓勵使用 LONG 資料類型，因為這個資料類型已經由 CLOB 資料類型所代替了。Oracle資料庫（Oracle9i 資料庫及其更新的版本）已經提供了通過 alter table 命令將 LONG 資料類型列轉換到相應的 LOB 資料類型的能力。也可以使用 to_lob 函數將 LONG 資料格式轉換成 CLOB 資料格式。
在下面的 SQL 陳述式類型中可以引用 LONG 資料類型列：
* select 列表
* 在 update 語句的 set 子句中
* 在 insert 語句的 values 子句中使用 LONG 資料類型將會受到許多的限制；
* 在每個表中只允許使用一個 LONG 資料類型的列。
* 不能使用 LONG 資料類型的屬性建立物件類型。
* Oracle 資料庫的 where 子句或者完整性條件約束不能引用 LONG 資料類型，僅有一種例外的情況，就是 LONG 資料類型可以出現在 NULL 和 NOT NULL 約束中，或者可以作為 NULL 或者 NOT NULL 的 where 子句謂語的一部分。
* 不能索引 LONG 資料類型的列。
* 不能對包含 LONG 資料類型的列進行分布操作。所有包含 LONG 資料類型列的事務

必須在同一個資料庫中協同工作。
* 複製不支援 LONG 資料類型。
* 如果所建立的表有 LONG 和 CLOB 兩種資料類型的列，則在同一 SQL 陳述式中，同時綁定到 LONG 和 CLOB 資料類型列上的資料不能超過 4000B。但是可以將超過 4000B 的資料單獨綁定到 LONG 資料類型或者 CLOB 資料類型列上。
除了前面所講的約束外，當 LONG 資料類型出現在 SQL 陳述式中時，還有另一些關於 LONG 資料類型列的約束。首先需要注意的是，下面的操作不支援 LONG 資料類型列：

* group by
* order by
* connect by
* distinct
* unique
* 任何 SQL 的內建函數、運算式或者條件
* 任何 select 語句（在該語句中，union、intersect 或者 minus 操作將查詢或者子查詢結合起來）
也會存在關於 LONG 資料類型的 DDL 約束，如下所示：
* 在 create cluster 語句的表列中不能包含 LONG 資料類型列。
* 在 alter table ...move 語句中不能包含 LONG 資料類型列。
* 在 create table as select 語句的挑選清單中不能包含 LONG 資料類型列。

在PL/SQL 程式單元和觸發器中使用 LONG 資料類型列時，同樣也存在一些約束，如下所示：
* 不能使用 LONG 資料類型表示觸發器中的變數。
* 觸發器變數 :new 和 :old 的資料類型不能是 LONG 資料類型列。
* PL/SQL 儲存函數不能返回 LONG 資料類型，但是 PL/SQL 程式中的變數或參數可以使用 LONG 資料類型，不過不能從 SQL 陳述式中調用這種 PL/SQL 程式單元。

5、關於 DATE 資料類型的注釋

DATE 資料類型是 Oracle 資料庫中內建的一種用於儲存日期和時間的方法。當 DATE 資料類型儲存在資料庫中的時候，這個資料類型佔據 7B 的內部儲存空間。這些位元組分別用來儲存世紀、年、月、日、小時、分和秒的詳細資料。
預設的顯示格式為 dd-mon-yy,它表示日、月以及兩位元的年，由破折號（-）將其分離。例如預設格式為：01-FEB-01。如果要重新定義日期格式，可以在資料庫參數檔案中設定 nls_date_format 變數。如果要改變日期的格式，也可以為特定的會話使

用 alter session 命令設定 nls_date_format 變數的值。如下所示：
SQL>SELECT sysdate FROM dual;
SYSDATE
---------
24-MAY-03
SQL>ALTER SESSION SET nls_date_format=‘mm/dd/yyyy hh24:mi:ss‘;
Session altered.
SQL>SELECT sysdate FROM dual;
SYSDATE
--------------------
06/25/2009 16:49:09

在上面的例子中，將日期格式的時間標誌改變為 24 小時制格式的時間，並且在年的前面加上了世紀值。
如果想要在特定系統的所有會話中使用不同的日期格式，可以設定 NLS_LANG 作業系統的環境變數，並且同時將 NLS_DATE_FORMAT 作為作業系統環境變數進行設定。這將使得每一次登入系統時，會發出 alter session 命令。需要注意的是，只有當同時設定了環境變數 NLS_LANG 時，環境變數 NLS_DATE_FORMATE 才會生效。

注意：
如果使用 RMAN（Oracle Recovery Manager，Oracle 恢複管理），則顯示的左右日期格式都將是預設的日期格式。應該在啟動 RMAN 之前設定 NLS_LANG 和 NLS_DATE_FORMAT 環境變數以設定所需要的日期格式。

在 SQL 陳述式中為了改變輸出的格式，需要使用 Oracle 資料庫的內建函數 to_char。如果有基於字元的日期要插入到 DATE 資料類型列中，則需要使用 to_date 函數。關於 to_char 函數轉換日期格式的例子如下所示：
SQL>SELECT to_char(sysdate,‘mm/dd/yyyy hh24:mi:ss‘) the_date FROM dual;
 THE_DATE
-----------------
06/25/2009 17:05:36

在上面的例子中，將日期格式轉化為由四位元表示的年以及 24 小時制、分鐘、秒錶示的時間格式。

6、關於 TIMESTAMP 和 INTERVAL 資料類型的注釋

某些新的 TIMESTAMP 資料類型的值依賴於資料庫中適當的時區設定。資料庫的時區預設為當前作業系統的時區。在資料庫建立的時候，可以通過在 create database 命令中使用 set time_zone 參數為資料庫設定不同的時區。也可以通過使用 alter database set time_zone 命令改變已經存在的資料庫時區。使用 alter session 命令可以在會話級上修改時區設定。可以定義基於UTC(Universal Time Coordinated，國際標準時間)小時位移的時區，或者使用一個指定的時區，如 CST 或 EST。下面是設定資料庫時區的例子：
ALTER DATABASE SET time_zone=‘CST‘;
ALTER DATABASE SET time_zone=‘-05:00‘;

一些轉換函式可以用於 TIMESTAMP 和 INTERVAL 資料類型。這些函數包括 to_data、to_char、to_timestamp_tz、to_yminterval 和 to_dsinterval，同時 nls_timestamp_format 參數和 nls_timestamp_tz_format 參數也與 TIMESTAMP 和 INTERVAL 資料類型有一些特定的關聯。
當從 TIMESTAMP 資料類型列中擷取資料時，可以使用其他的內建函數。extract 函數可以從 TIMESTAMP 資料類型列中擷取特定的資訊，如小時或者分鐘的資訊。如下所示（注意，從 sysdate 函數到 TIMESTAMP 資料類型格式的隱式轉換）：
CREATE TABLE my_tab(test_col TIMESTAMP);
INSERT INTO my_tab VALUES (sysdate);
SELECT test_col,EXTRACT(HOUR FROM test_col) FROM my_tab;
TEST_COL           HOUR
-------------------------------------
25-JUNE -09 05.18.50.000000 PM     9

 

參考文獻：【美】夫雷曼（Freeman.R.G.）《Oracle + DBA 必備技能詳解》
關於《Oracle + DBA 必備技能詳解》中譯本，作者有一本影印版的PDF格式的，40M大小，已經上傳至作者 CSDN 資源共用給大家，有興趣的朋友可以去看看，：http://download.csdn.net/source/1410669

oracle資料類型