[轉]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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16674653/viewspace-756483/，如需轉載，請註明出處，否則將追究法律責任。