Oracle隱式類型轉換

標籤：

詳細串連：http://blog.163.com/lsj_start/blog/static/17826443920111112103716524/

http://blog.itpub.net/29324876/viewspace-1096741/   （用explain查看隱式轉換）

oracle有三種最基本的資料類型，即字元型、數值型、日期型。同時提供類型轉換函式

1）to_char
    數值、日期->字元型
   
文法：to_char(num|date,[format
mask],[nls_parameters])
    參數：num|date
待轉換的數值或者日期
            format
mask:選擇性參數

 

2）to_date

 

字元值->日期值 文法：to_date (string,[format mask],[nls_parameters]) 參數：string 待轉換的字元值             format mask:選擇性參數

 

      格式掩碼同to_char轉換為date時相同。

 

備忘：轉換時要根據給定的string設定正確的格式掩碼，否則

 

      Ora_01840:input value is not long enough for date format.

 

      Ora_01862:the numeric value does not match the length of the format item.

 

3） to_number

 

字元值->數字值 文法：to_number (string,[format mask],[nls_parameters]) 參數：string 待轉換的字元值             format mask:選擇性參數

 

      格式掩碼同to_char轉換為number時相同。

 

備忘：如果使用較短的格式掩碼就會返回錯誤。

 

       例如: to_number(123.56,’999.9’)返回錯誤。

 

 

在oracle中，如果不同的資料類型之間關聯，如果不顯式轉換資料，則它會根據以下規則對資料進行隱式轉換

 

1) 對於INSERT和UPDATE操作，oracle會把插入值或者更新值隱式轉換為欄位的資料類型。

   假如id列的資料類型為number

   update t set id=‘1‘; -> 相當於 update t set id=to_number(‘1‘);

   insert into t(id) values(‘1‘) -> insert into t values(to_number(‘1‘));

 

2) 對於SELECT語句，oracle會把欄位的資料類型隱式轉換為變數的資料類型。    如假設id列的資料類型為varchar2

   select * from t where id=1; -> select * from t where to_number(id)=1;

   但如果id列的資料類型為number，則

   select * from t where id=‘1‘; -> select * from t where id=to_number(‘1‘);(參考下文)

 

3) 當比較一個字元型和數值型的值時，oracle會把字元型的值隱式轉換為數值型。

    如假設id列的資料類型為number

    select * from t where id=‘1‘; -> select * from t where id=to_number(‘1‘);

 

4) 當比較字元型和日期型的資料時，oracle會把字元型轉換為日期型。

    如假設create_date為字元型，

    select * from t where create_date>sysdate; -> select * from t where to_date(create_date)>sysdate;(注意，此時session的nls_date_format需要與字串格式相符)

    假設create_date為date型，

    select * from t where create_date>‘2006-11-11 11:11:11‘; -> select * from t where   create_date>to_date(‘2006-11-11 11:11:11‘); (注意，此時session的nls_date_format需要與字串格式相符)

 

5) 如果調用函數或過程等時，如果輸入參數的資料類型與函數或者流程定義的參數資料類型不一直，則oracle會把輸入參數的資料類型轉換為函數或者流程定義的資料類型。

   如假設過程如下定義 p(p_1 number)

   exec p(‘1‘); -> exec p(to_number(‘1‘)); 6)

   賦值時，oracle會把等號右邊的資料類型轉換為左邊的資料類型。

   如 var a number a:=‘1‘; - > a:=to_number(‘1‘);

 

7) 用串連操作符(||)時，oracle會把非字元類型的資料轉換為字元類型。

    select 1||‘2‘ from dual; -> select to_char(1)||‘2‘ from dual;

8) 如果字元類型的資料和非字元類型的資料(如number、date、rowid等)作算術運算，則oracle會將字元類型的資料轉換為合適的資料類型，這些資料類型可能是number、date、rowid等。

    如果CHAR/VARCHAR2 和NCHAR/NVARCHAR2之間作算術運算，

    則oracle會將她們都轉換為number類型的資料再做比較。

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

 

簡單總結：

比較時，一般是字元型轉換為數值型，字元型轉換為日期型

算術運算時，一般把字元型轉換為數值型，字元型轉換為日期型

串連時(||)，一般是把數值型轉換為字元型,日期型轉換為字元型

賦值、調用函數時，以定義的變數類型為準。

Oracle隱式類型轉換