在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.
在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>
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>