创建临时表
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;