Oracle 12C R2-新特性-轉換函式的增強

在12.2中對資料型別轉換函式的許多增強，從而更容易處理轉換錯誤。從下面例子中看更清楚，

1  建立表

建立一個表，只有一個date型別的欄位，並插入三條資料，其中兩條是不符合date型別。

SQL> CREATE TABLE t1 (

  data VARCHAR2(20)

);  2    3  

Table created.

SQL> INSERT INTO t1 VALUES ('11111')

  2  ;

1 row created.

SQL> INSERT INTO t1 VALUES ('01-JAN-2016');

1 row created.

SQL> INSERT INTO t1 VALUES ('AAAAA');

1 row created.

SQL> COMMIT;

Commit complete.

2  使用轉換函式轉換

在12.2之前，使用轉換函式會報錯。例如：

SQL> SELECT TO_NUMBER(data)

FROM   t1;  2  

ERROR:

ORA-01722: invalid number


no rows selected


SQL>

在12.2中，在一些TO_*的轉換函式中，已經被新增了一個處理錯誤的功能，這就允許如果某條記錄轉換錯誤，那麼就會返回一個預設值。

如：使用TO_NUMBER函式轉換，只有第一條記錄是可以轉換的。其他兩條都是不符合要求的，所以不會轉換成功。那麼就會返回一個定義好的一個預設值(這裡是-1，當然可以改成其他)

SQL> SELECT TO_NUMBER(data DEFAULT -1 ON CONVERSION ERROR)

FROM   t1;  2  

TO_NUMBER(DATADEFAULT-1ONCONVERSIONERROR)

-----------------------------------------

    11111

       -1

       -1

再如：使用TO_DATE轉換，只有第二條記錄滿足條件,其他返回預設值’01-JAN-00’

SQL> SELECT TO_DATE(data DEFAULT '01-JAN-2000' ON CONVERSION ERROR, 'DD-MON-YYYY' )

FROM   t1;

TO_DATE(D

---------

01-JAN-00

01-JAN-16

01-JAN-00

SQL>

3  VALIDATE_CONVERSION函式

VALIDATE_CONVERSION函式用於測試轉換是否成功，從而可以排除在操作期間導致問題的資料。 如果轉換成功，則函式返回值“1”，如果轉換失敗，則返回“0”。

如：還是那個表，檢驗哪些資料符合轉換成NUMBER型別的。

SQL> SELECT data

FROM   t1

 	WHERE  VALIDATE_CONVERSION(data AS NUMBER) = 1;

DATA

--------------------

11111

SQL>

再如：檢驗哪些資料符合轉換成DATE型別的。

SQL> SELECT data

FROM   t1

WHERE  VALIDATE_CONVERSION(data AS DATE, 'DD-MON-YYYY') = 1;

DATA

--------------------

01-JAN-2016

SQL>

更多的函式詳細資訊請檢視官方文件：http://docs.oracle.com/database/122/SQLRF/Functions.htm#SQLRF006