Oracle 12c中的轉換功能增強

Oracle Database 12c Release 2（12.2）包括對資料型別轉換函式的許多增強，從而更容易處理轉換錯誤。

建立測試表及初始化資料

本文中的示例所用的表：

CREATE TABLE t1 (
  data VARCHAR2(20)
);

INSERT INTO t1 VALUES ('11111');
INSERT INTO t1 VALUES ('01-JAN-2016');
INSERT INTO t1 VALUES ('AAAAA');
COMMIT;

CAST 和 TO_* 轉換函式

在以前的資料庫版本中，資料型別轉換期間失敗將導致錯誤。

SELECT TO_NUMBER(data) FROM   t1;
ERROR:
ORA-01722: invalid number

no rows selected

SQL>

在Oracle資料庫12.2中，CAST函式和幾個TO_*函式已修改為包括錯誤處理功能，允許它們在發生轉換錯誤時返回預設值。

SELECT TO_NUMBER(data DEFAULT -1 ON CONVERSION ERROR)
FROM   t1;
                 *
TO_NUMBER(DATADEFAULT-1ONCONVERSIONERROR)
----------------------------------------
                                   11111
                                      -1
                                      -1

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>
SELECT CAST(data AS TIMESTAMP DEFAULT NULL ON CONVERSION ERROR, 'DD-MON-YYYY')
FROM   t1;

CAST(DATAASTIMESTAMPDEFAULTNULLONCONVERSIONERROR,'DD-MON-YYYY')
-----------------------------------------------------------------
01-JAN-16 12.00.00.000000000 AM

SQL>

VALIDATE_CONVERSION 函式

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

SELECT data FROM   t1 WHERE  VALIDATE_CONVERSION(data AS NUMBER) = 1;

DATA
--------------------
11111

SQL>
SELECT data FROM   t1
WHERE  VALIDATE_CONVERSION(data AS DATE, 'DD-MON-YYYY') = 1;

DATA
--------------------
01-JAN-2016

SQL>