CAST和VALIDATE_CONVERSION函数 db官方例子

创建临时表

CREATE TABLE STAGING_EMP

( "EMPNO" VARCHAR2(6),

  "ENAME" VARCHAR2(10),

  "JOB" VARCHAR2(9),

  "MGR" VARCHAR2(4),

  "HIREDATE" VARCHAR2(10),

  "SAL" VARCHAR2(7),

  "COMM" VARCHAR2(9),

  "DEPTNO" VARCHAR2(6));

向临时表中插入一些数据 

 Insert into STAGING_EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values ('GB9369','SMITH','CLERK','7902','17-DEC-80','800',null,'20');

Insert into STAGING_EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values ('9499','ALLEN','SALESMAN','7698','31-FEB-81','1600','300','30');

Insert into STAGING_EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values ('9521','WARD','SALESMAN','7698','22-FEB-81','1250','500','SALES');

Insert into STAGING_EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values ('US9566','JONES','MANAGER','7839','02-APR-81','2975',null,'20');

Insert into STAGING_EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values ('9782','CLARK','MANAGER','7839','09-JUN-81','2450',null,'10');

Insert into STAGING_EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values ('FR9788','SCOTT','ANALYST','7566','19-APR-87','3000',null,'20');

Insert into STAGING_EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values ('9839','KING','PRESIDENT',null,'17-NOV-81','5000',null,'10');

Insert into STAGING_EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values ('DE9844','TURNER','SALESMAN','7698','08-SEP-81','1500',0,'30');

Insert into STAGING_EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values ('9876','ADAMS','CLERK','7788','23-MAY-87','1100',null,'20');

创建目标表 

CREATE TABLE EMP

 ( EMPNO NUMBER(4,0),

   ENAME VARCHAR2(10 BYTE),

   JOB VARCHAR2(9 BYTE),

   MGR NUMBER(4,0),

   HIREDATE DATE,

   SAL NUMBER(7,2),

   COMM NUMBER(7,2),

   DEPTNO NUMBER(2,0),

CONSTRAINT "PK_EMP" PRIMARY KEY (EMPNO));

插入暂存数据

INSERT INTO emp SELECT * FROM staging_emp;

使用 VALIDATE_CONVERSION 函数查找恶意值

尝试发现临时表中的哪些行和列包含可能导致数据转换错误的值。

使用新的 VALIDATE_CONVERSION() 函数来标识无法转换为所需数据类型的问题数据。

SELECT

  VALIDATE_CONVERSION(empno AS NUMBER) AS is_empno,

  VALIDATE_CONVERSION(mgr AS NUMBER) AS is_mgr,

  VALIDATE_CONVERSION(hiredate AS DATE) AS is_hiredate,

  VALIDATE_CONVERSION(sal AS NUMBER) AS is_sal,

  VALIDATE_CONVERSION(comm AS NUMBER) AS is_comm,

  VALIDATE_CONVERSION(deptno AS NUMBER) AS is_deptno

FROM staging_emp;

上述查询的输出使我们能够轻松挑选出数据转换将成功（列值为 1）和失败（列值为 0）的行

使用增强型 CAST 功能

当我将数据插入到 EMP 表中时，我们可以使用这些信息来过滤临时表中的数据，或者我们可以在 INSERT INTO ..... SELECT 语句中使用增强的 CAST 和 TO_xxx 函数。

CAST 函数（以及 TO_NUMBER、TO_BINARY_FLOAT、TO_BINARY_DOUBLE、TO_DATE、TO_TIMESTAMP、TO_TIMESTAMP_TZ、TO_DSINTERVAL 和 TO_YMINTERVAL 函数）现在可以在发生数据类型转换错误时返回用户指定的值，而不是错误。这减少了数据转换和数据加载过程中的故障。

INSERT INTO emp

SELECT

  empno,

  ename,

  job,

  CAST(mgr AS NUMBER DEFAULT 9999 ON CONVERSION ERROR),

  CAST(hiredate AS DATE DEFAULT sysdate ON CONVERSION ERROR),

  CAST(sal AS NUMBER DEFAULT 0 ON CONVERSION ERROR),

  CAST(comm AS NUMBER DEFAULT null ON CONVERSION ERROR),

  CAST(deptno AS NUMBER DEFAULT 99 ON CONVERSION ERROR)

FROM staging_emp

WHERE VALIDATE_CONVERSION(empno AS NUMBER) = 1;

 

 

我们只加载了 5 行的事实显然意味着在插入过程中拒绝了 4 行。我们使用 VALIDATE_CONVERSION 函数查看暂存表中未能插入的行

SELECT * FROM staging_emp

WHERE VALIDATE_CONVERSION(empno AS NUMBER) = 0;