oracle基礎資料型別 (Elementary Data Type)

標籤：

     ORACLE基礎資料型別 (Elementary Data Type)（亦叫內建資料類型 built-in datatypes)可以按類型分為：字串類型、數字類型、日期類型、LOB類型、LONG RAW& RAW類型、ROWID & UROWID類型。

在講敘字串類型前，先要講一下編碼。字串類型的資料可依編碼方式分成資料庫字元集（CHAR/VARCHAR2/CLOB/LONG)和國際字元集(NCHAR/NVARCHAR2/NCLOB)兩種。資料庫中的字串資料都通過字元集將字元轉換為數字後(二進位)，才儲存到資料區塊中。通過不同的編碼集轉換，即便是相同的字元，也可能會轉換成不同的二進位編碼。這也是產生亂碼的原因。資料庫的編碼格式一般是在建立資料庫時指定的。當然也可以修改資料庫的編碼。

查看資料庫檢視所包含的資料類型:SELECT * FROM DBA_TYPES WHERE OWNER IS NULL.具體細節情況參見Oracle® Database SQL Language Quick Reference 10/11g 或官方文檔

 

 

一 字串類型

    字串資料型別還可以依據儲存空間分為固定長度類型（CHAR/NCHAR) 和可變長度類型（VARCHAR2/NVARCHAR2)兩種.

    所謂固定長度：是指雖然輸入的欄位值小於該欄位的限制長度，但是實際儲存資料時，會先自動向右補足空格後，才將欄位值的內容儲存到資料區塊中。這種方式雖然比較浪費空間，但是儲存效率較可變長度類型要好。同時還能減少資料行遷移情況發生。

所謂可變長度：是指當輸入的欄位值小於該欄位的限制長度時，直接將欄位值的內容儲存到資料區塊中，而不會補上空白，這樣可以節省資料區塊空間。

1.1：CHAR類型 CHAR(size [BYTE | CHAR])

CHAR類型，定長字串，會用空格填充來達到其最大長度。非NULL的CHAR（12）總是包含12位元組資訊。CHAR欄位最多可以儲存2,000位元組的資訊。如果建立表時，不指定CHAR長度，則預設為1。另外你可以指定它儲存位元組或字元，例如 CHAR(12 BYTYE) CHAR(12 CHAR).一般來說預設是儲存位元組，你可以查看資料庫參數

NLS_LENGTH_SEMANTICS的值。

SQL Code

1. SQL> show parameter nls_length_semantics;
2.  
3. NAME                   TYPE           VALUE
4. ------------------   ----------- -----------------
5.  
6. nls_length_semantics   string          BYTE
7.  
8. eg:
9.  
10. CREATE TABLE TEST
11.  
12. (
13.  
14. NAME_OLD CHAR(10),
15.  
16. NAME_NEW CHAR(10 CHAR)
17.  
18. )
19.  
20. INSERT INTO TEST
21.  
22. ( NAME_OLD, NAME_NEW)
23.  
24. SELECT ‘ABCDEFGHIJ‘ , ‘你清除位元組與字元‘ FROM DUAL;
25.  
26. COMMIT;
27.  
28. INSERT INTO TEST
29.  
30. ( NAME_OLD, NAME_NEW)
31.  
32. SELECT ‘你清除位元組與字元‘ , ‘ABCDEFGHIJ‘ FROM DUAL;
33.  
34. ORA-12899: 列 "SYS"."TEST"."NAME_OLD" 的值太大 (實際值: 24, 最大值: 10) 

注意：資料庫的NLS_CHARACTERSET 為AL32UTF8，即一個漢字佔用三到四個位元組。如果NLS_CHARACTERSET為ZHS16GBK，則一個字元佔用兩個位元組。

如果串的長度小於或等於250（0x01~0xFA）， Oracle 會使用1 個位元組來表示長度。對於所有長度超過250 的串，都會在一個標誌位元組0xFE 後跟有兩個位元組來表示長度。因此，如果有一個包含“Hello World”的VARCHAR2(80)，則在塊中可能12.-1 所示

1.2： NCHAR類型

這是一個包含UNICODE格式資料的定長字串。NCHAR欄位最多可以儲存2,000位元組的資訊。它的最大長度取決於國家字元集。另外查詢時，如果欄位是NCHAR類型，則需要如下書寫

SELECT translated_description FROM product_descriptions

WHERE translated_name = N‘LCD Monitor 11/PM‘;

1.3 VARCHAR類型

不要使用VARCHAR資料類型。使用VARCHAR2資料類型。雖然VARCHAR資料類型目前是VARCHAR2的同義字，VARCHAR資料類型將計劃被重新定義為一個單獨的資料類型用於可變長度的字串相比，具有不同的比較語義。

1.4： VARCHAR2類型

變長字串，與CHAR類型不同，它不會使用空格填充至最大長度。VARCHAR2最多可以儲存4,000位元組的資訊。

1.5： NVARCHAR2類型

這是一個包含UNICODE格式資料的變長字串。 NVARCHAR2最多可以儲存4,000位元組的資訊。

 

 

二. 數字類型

 

2.1 NUMBER類型

NUMBER(P,S)是最常見的數字類型，可以存放資料範圍為10^130~10^126（不包含此值)，需要1~22位元組(BYTE)不等的儲存空間。

P 是Precison的英文縮寫，即精度縮寫，表示有效數位位元，最多不能超過38個有效數字

S是Scale的英文縮寫，可以使用的範圍為-84~127。Scale為正數時，表示從小數點到最低有效數位位元，它為負數時，表示從最大有效數字到小數點的位元

下面是官方文檔的樣本

Actual Data	Specified As	Stored As
123.89	NUMBER	123.89
123.89	NUMBER(3)	124
123.89	NUMBER(6,2)	123.89
123.89	NUMBER(6,1)	123.9
123.89	NUMBER(3)	124
123.89	NUMBER(4,2)	exceeds precision
123.89	NUMBER(6,-2)	100
.01234	NUMBER(4,5)	.01234
.00012	NUMBER(4,5)	.00012
.000127	NUMBER(4,5)	.00013
.0000012	NUMBER(2,7)	.0000012
.00000123	NUMBER(2,7)	.0000012
1.2e-4	NUMBER(2,5)	0.00012
1.2e-5	NUMBER(2,5)	0.00001

2.2 INTEGER類型

INTEGER是NUMBER的子類型，它等同於NUMBER（38,0），用來儲存整數。若插入、更新的數值有小數，則會被四捨五入。

例如：

CREATE TABLE TEST

(

ID INTEGER

)

查看錶TEST的DDL定義如下所示

CREATE TABLE "SYS"."TEST"

( "ID" NUMBER(*,0)

) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "SYSTEM" ;

INSERT INTO TEST

SELECT 12.34 FROM DUAL;

INSERT INTO TEST

SELECT 12.56 FROM DUAL;

SQL> SELECT * FROM TEST;

ID

----------

12

13

2.3 浮點數

浮點數可以有一個十進位數點任何地方從第一個到最後一個數字，或者可以在所有有沒有小數點。指數可能（可選） 用於以下數量增加的範圍 （例如， 1.777e-20)。刻度值不適用於浮點數字，因為可以顯示在小數點後的位元的數量不受限制。

二進位浮點數不同數量的值由 Oracle 資料庫內部儲存的方式。使用小數精度數儲存值。完全相同號碼儲存範圍和數量由支援的精度內的所有文本。正是因為使用小數精度（數字 0 到 9） 表示文本儲存文本。使用二進位精度 （數字 0 和 1） 儲存二進位浮點數。這種儲存方案不能代表所有確切地使用小數精度的值。頻繁地，將值從十進位轉換為二進位的精度時出現的錯誤時撤消值回從二進位轉換為十進位精度。在字面 0.1 是一個這樣的例子。

Oracle 資料庫提供了專為浮點數的兩種數值資料類型：

BINARY_FLOAT

BINARY_FLOAT 是 32 位、 單精確度浮點數字資料類型。可以支援至少6位精度,每個 BINARY_FLOAT 的值需要 5 個位元組，包括長度位元組。

BINARY_DOUBLE

BINARY_DOUBLE 是為 64 位元，雙精確度浮點數字資料類型。每個 BINARY_DOUBLE 的值需要 9 個位元組，包括長度位元組。

在數位列中，浮點數有小數精度。在 BINARY_FLOAT 或 BINARY_DOUBLE 的列中，浮點數有二進位的精度。二進位浮點數支援的特殊值無窮大和 NaN （不是數字）。

您可以指定列在表 2-4 範圍內的浮點數。"數字文本"中定義了用於指定浮點數的格式。

Table 2-3 Floating Point Number Limits

Value	Binary-Float	Binary-Double
Maximum positive finite value	3.40282E+38F	1.79769313486231E+308
Minimum positive finite value	1.17549E-38F	2.22507485850720E-308

2.5 FLOAT類型

FLOAT類型也是NUMBER的子類型。

Float(n),數 n 指示位的精度，可以儲存的值的數目。N 值的範圍可以從 1 到 126。若要從二進位轉換為十進位的精度，請將 n 乘以 0.30103。要從十進位轉換為二進位的精度，請用 3.32193 乘小數精度。126 位二進位精度的最大值是大約相當於 38 位小數精度。

 

三. 日期類型

日期類型用於儲存日期資料，但是並不是使用一般的格式（2012-08-08）直接儲存到資料庫的。

3.1 DATE類型

DATE是最常用的資料類型，日期資料類型儲存日期和時間資訊。雖然可以用字元或數字類型表示日期和時間資訊，但是日期資料類型具有特殊關聯的屬性。為每個日期值，Oracle 儲存以下資訊： 世紀、 年、 月、 日期、 小時、 分鐘和秒。一般佔用7個位元組的儲存空間。

3.2 TIMESTAMP類型

這是一個7位元組或12位元組的定寬日期/時間資料類型。它與DATE資料類型不同，因為TIMESTAMP可以包含小數秒，帶小數秒的TIMESTAMP在小數點右邊最多可以保留9位

3.3 TIMESTAMP WITH TIME ZONE類型

這是TIMESTAMP類型的變種，它包含了時區位移量的值

3.4 TIMESTAMP WITH LOCAL TIME ZONE類型

3.5 INTERVAL YEAR TO MOTH

3.6 INTERVAL DAY TO SECOND

 

四. LOB類型

內建的LOB資料類型包括BLOB、CLOB、NCLOB、BFILE（外部儲存）的大型化和非結構化資料，如文本、映像、視屏、空間資料儲存。BLOB、CLOB、NCLOB類型

 

4.1 CLOB 資料類型

   它儲存單位元組和多位元組字元資料。支援固定寬度和可變寬度的字元集。CLOB對象可以儲存最多 (4 gigabytes-1) * (database block size) 大小的字元

4.2 NCLOB 資料類型

   它儲存UNICODE類型的資料，支援固定寬度和可變寬度的字元集，NCLOB對象可以儲存最多(4 gigabytes-1) * (database block size)大小的文本資料。

4.3 BLOB 資料類型

   它儲存非結構化的位元據大對象，它可以被認為是沒有字元集語義的位元流，一般是映像、聲音、視頻等檔案。BLOB對象最多儲存(4 gigabytes-1) * (database block size)的位元據。

4.4 BFILE 資料類型

   二進位檔案，儲存在資料庫外的系統檔案，唯讀，資料庫會將該檔案當二進位檔案處理

五. RAW & LONG RAW類型

 

5.1 LONG類型

 

它儲存變長字串，最多達2G的字元資料（2GB是指2千MB， 而不是2千兆字元），與VARCHAR2 或CHAR 類型一樣，儲存在LONG 類型中的文本要進行字元集轉換。ORACLE建議開發中使用CLOB替代LONG類型。支援LONG 列只是為了保證向後相容性。CLOB類型比LONG類型的限制要少得多。 LONG類型的限制如下：

1.一個表中只有一列可以為LONG型。(Why?有些不明白)

2.LONG列不能定義為主鍵或唯一約束，

3.不能建立索引

4.LONG資料不能指定Regex。

5.函數或預存程序不能接受LONG資料類型的參數。

6.LONG列不能出現在WHERE子句或完整性條件約束（除了可能會出現NULL和NOT NULL約束）

官方文檔描敘如下：

The use of LONG values is subject to these restrictions:

A table can contain only one LONG column.

You cannot create an object type with a LONG attribute.

LONG columns cannot appear in WHERE clauses or in integrity constraints (except that they can appear in NULL and NOT NULL constraints).

LONG columns cannot be indexed.

LONG data cannot be specified in regular expressions.

A stored function cannot return a LONG value.

You can declare a variable or argument of a PL/SQL program unit using the LONG datatype. However, you cannot then call the program unit from SQL.

Within a single SQL statement, all LONG columns, updated tables, and locked tables must be located on the same database.

LONG and LONG RAW columns cannot be used in distributed SQL statements and cannot be replicated.

If a table has both LONG and LOB columns, then you cannot bind more than 4000 bytes of data to both the LONG and LOB columns in the same SQL statement. However, you can bind more than 4000 bytes of data to either the LONG or the LOB column.

In addition, LONG columns cannot appear in these parts of SQL statements:

GROUP BY clauses, ORDER BY clauses, or CONNECT BY clauses or with the DISTINCT operator in SELECT statements

The UNIQUE operator of a SELECT statement

The column list of a CREATE CLUSTER statement

The CLUSTER clause of a CREATE MATERIALIZED VIEW statement

SQL built-in functions, expressions, or conditions

SELECT lists of queries containing GROUP BY clauses

SELECT lists of subqueries or queries combined by the UNION, INTERSECT, or MINUS set operators

SELECT lists of CREATE TABLE ... AS SELECT statements

ALTER TABLE ... MOVE statements

SELECT lists in subqueries in INSERT statements

5.2 LONG RAW 類型，能儲存2GB 的原始位元據（不用進行字元集轉換的資料）

5.3 RAW類型

用於儲存二進位或字元類型資料，變長位元據類型，這說明採用這種資料類型儲存的資料不會發生字元集轉換。這種類型最多可以儲存2,000位元組的資訊

 

六. ROWID & UROWID類型

在資料庫中的每一行都有一個地址。然而，一些表行的地址不是物理或永久的，或者不是ORACLE資料庫產生的。

例如，索引組織表行地址儲存在索引的葉子，可以移動。

例如,外部表格的ROWID（如通過網關訪問DB2表）不是??標準的ORACLE的rowid。

ORACLE使用通用的ROWID（UROWIDs）的儲存地址的索引組織表和外表。索引組織表有邏輯urowids的，和國外表的外urowids，。UROWID這兩種類型的儲存在ROWID偽（堆組織的表的物理行id）。

建立基於邏輯的rowid在表中的主鍵。邏輯的rowid不會改變，只要主鍵不改變。索引組織表的ROWID偽UROWID資料類型。你可以訪問這個偽列，你會堆組織表的ROWID偽（即使用一個SELECT ...ROWID語句）。如果你想儲存的rowid索引組織表，那麼你就可以定義一列的表型UROWID到列檢索值的ROWID偽。

oracle基礎資料型別 (Elementary Data Type)