ORACLE隱式類型轉換

隱式類型轉換簡介

通常ORACLE資料庫存在顯式類型轉換（Explicit Datatype Conversion）和隱式類型轉換（Implicit Datatype Conversion）兩種類型轉換方式。如果進行比較或運算的兩個值的數據類型不同時（源數據的類型與目標數據的類型），而且此時又沒有轉換函數時，那麼ORACLE必須將其中一個值進行類型轉換，使其能夠運算。這就是所謂的隱式類型轉換。其中隱式類型轉換是自動進行的，當然，只有在這種轉換是有意義的時候，才會自動進行。

Data Conversion

Generally an expression cannot contain values of different datatypes. For example, an expression cannot multiply 5 by 10 and then add 'JAMES'. However, Oracle supports both implicit and explicit conversion of values from one datatype to another.

關於隱式類型轉換，建議翻看官方文檔“Data Type Comparison Rules”章節，下麵是官方文檔中的隱式類型轉換矩陣。從下麵這個表格，我們就能對哪些數據類型能進行轉換一目瞭然。

隱式轉換的規則：

其實隱式類型轉換髮生在很多地方，只是我們很多時候沒有留意罷了，不打算一一舉例，自行翻閱官方文檔的介紹，摘抄隱式類型轉換的一些常見的規則如下：

The following rules govern implicit data type conversions:

• During INSERT and UPDATE operations, Oracle converts the value to the data type of the affected column.
• During SELECT FROM operations, Oracle converts the data from the column to the type of the target variable.
• When manipulating numeric values, Oracle usually adjusts precision and scale to allow for maximum capacity. In such cases, the numeric data type resulting from such operations can differ from the numeric data type found in the underlying tables.
• When comparing a character value with a numeric value, Oracle converts the character data to a numeric value.
• Conversions between character values or NUMBER values and floating-point number values can be inexact, because the character types and NUMBER use decimal precision to represent the numeric value, and the floating-point numbers use binary precision.
• When converting a CLOB value into a character data type such as VARCHAR2, or converting BLOB to RAW data, if the data to be converted is larger than the target data type, then the database returns an error.
• During conversion from a timestamp value to a DATE value, the fractional seconds portion of the timestamp value is truncated. This behavior differs from earlier releases of Oracle Database, when the fractional seconds portion of the timestamp value was rounded.
• Conversions from BINARY_FLOAT to BINARY_DOUBLE are exact.
• Conversions from BINARY_DOUBLE to BINARY_FLOAT are inexact if the BINARY_DOUBLE value uses more bits of precision that supported by the BINARY_FLOAT.
• When comparing a character value with a DATE value, Oracle converts the character data to DATE.
• When you use a SQL function or operator with an argument of a data type other than the one it accepts, Oracle converts the argument to the accepted data type.
• When making assignments, Oracle converts the value on the right side of the equal sign (=) to the data type of the target of the assignment on the left side.
• During concatenation operations, Oracle converts from noncharacter data types to CHAR or NCHAR.
• During arithmetic operations on and comparisons between character and noncharacter data types, Oracle converts from any character data type to a numeric, date, or rowid, as appropriate. In arithmetic operations between CHAR/VARCHAR2 and NCHAR/NVARCHAR2, Oracle converts to a NUMBER.
• Most SQL character functions are enabled to accept CLOBs as parameters, and Oracle performs implicit conversions between CLOB and character types. Therefore, functions that are not yet enabled for CLOBs can accept CLOBs through implicit conversion. In such cases, Oracle converts the CLOBs to CHAR or VARCHAR2 before the function is invoked. If the CLOB is larger than 4000 bytes, then Oracle converts only the first 4000 bytes to CHAR.
• When converting RAW or LONG RAW data to or from character data, the binary data is represented in hexadecimal form, with one hexadecimal character representing every four bits of RAW data. Refer to "RAW and LONG RAW Data Types" for more information.
• Comparisons between CHAR and VARCHAR2 and between NCHAR and NVARCHAR2 types may entail different character sets. The default direction of conversion in such cases is from the database character set to the national character set. Table 2-9 shows the direction of implicit conversions between different character types.

對上面官方文檔資料的翻譯如下，如有不對或不夠確切的地方，敬請指出

1.  對於INSERT和UPDATE操作，ORACLE會把插入值或者更新值隱式轉換為對應欄位的數據類型。

2.  對於SELECT語句，ORACLE會把欄位的數據類型隱式轉換為變數的數據類型。

3.  當處理數值時，ORACLE通常會調整精度和小數位，以實現最大容量。在這種情況下，由此類操作產生的數字數據類型可能與在基礎表中找到的數字數據類型不同。

4.  當比較一個字元型和數值型的值時，ORACLE會把字元型的值隱式轉換為數值型。

5.  字元值或NUMBER值與浮點數值之間的轉換可能不准確，因為字元類型和NUMBER使用十進位精度表示數字值，而浮點數則使用二進位精度。

6.  將CLOB值轉換為字元數據類型（例如VARCHAR2）或將BLOB轉換為RAW數據時，如果要轉換的數據大於目標數據類型，則資料庫將返回錯誤。

7.   當timestamp類型轉換為DATE時（按照第三條，隱式轉換不應該把timestamp轉換為date，除非insert這樣的），timestamp後幾位會被truncated忽略，至於忽略幾位，取決於資料庫版本。

8.  從BINARY_FLOAT到BINARY_DOUBLE的轉換是準確的。

9.  從BINARY_DOUBLE到BINARY_FLOAT的轉換是不精確的，因為BINARY_DOUBLE精度更高。

10.  當比較字元型和日期型的數據時，ORACLE會把字元型轉換為日期型。

11. 如果調用函數（過程）或運算符操作時，如果輸入參數的數據類型與函數（存儲過程）定義的參數數據類型不一致或不是可接受的數據類型時，則ORACLE會把輸入參數的數據類型轉換為函數或者過程定義的數據類型。

12. 當使用賦值符號（等號）時，右邊的類型轉換為左邊的類型

13. 當連接操作（concatenation，一般為||）時，ORACLE會隱式轉換非字元型到字元型

14. 如果字元類型的數據和非字元類型的數據(如number、date、rowid等)作算術運算，則ORACLE會將字元類型的數據轉換為合適的數據類型，這些數據類型可能是number、date、rowid等。

  如果CHAR/VARCHAR2 和NCHAR/NVARCHAR2之間作算術運算，則ORACLE會將她們都轉換為number類型的數據再做比較。

15. 比較CHAR/VARCHAR2 和NCHAR/NVARCHAR2時，如果兩者字元集不一樣，則預設的轉換方式是將數據編碼從資料庫字元集轉換為國家字元集

下麵簡單舉兩個例子，看看隱式轉換髮生的場景：

例子：

SQL> create table test(object_id varchar2(12), object_name varchar2(64));

Table created.

SQL> insert into test

  2  select object_id, object_name from dba_objects;

63426 rows created.

SQL> commit;