程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> oracle數據類型，mysql數據類型

oracle數據類型，mysql數據類型

編輯：Oracle教程

oracle數據類型，mysql數據類型

本文轉自：http://blog.csdn.net/defonds/article/details/4302695 謝謝原文作者

有道是，磨刀不誤砍柴工。多了解一些底層的東西，對於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