数据校验脚本-检验null值

青旅半醒 2022-07-29 06:21 57阅读 0赞

在做数据校验，需要检验多个字段是否为Null值，那怎么可以写的高效，如下例子:

drop table T1 purge;

create table T1  
(  
  ID          NUMBER,  
  OWNER       VARCHAR2(30),  
  OBJECT\_NAME VARCHAR2(128),  
  OBJECT\_TYPE VARCHAR2(19),  
  STATUS      VARCHAR2(7)  
);  
insert into t1 (ID, OWNER, OBJECT\_NAME, OBJECT\_TYPE, STATUS)  
values (1, 'SYS', 'ICOL$', 'INDEX', 'VALID');  
insert into t1 (ID, OWNER, OBJECT\_NAME, OBJECT\_TYPE, STATUS)  
values (2, '', 'I\_USER1', 'INDEX', 'VALID');  
insert into t1 (ID, OWNER, OBJECT\_NAME, OBJECT\_TYPE, STATUS)  
values (3, 'SYS', 'CON$', '', '');  
insert into t1 (ID, OWNER, OBJECT\_NAME, OBJECT\_TYPE, STATUS)  
values (4, 'SYS', 'UNDO$', 'TABLE', '');  
insert into t1 (ID, OWNER, OBJECT\_NAME, OBJECT\_TYPE, STATUS)  
values (5, 'SYS', '', '', 'VALID');  
commit;  
  
  
col ID format a5;  
col OWNER format a15;  
col OBJECT\_NAME format a15;  
col OBJECT\_TYPE format a15;  
col STATUS format a10;  
select to\_char(ID) id, OWNER, OBJECT\_NAME,  
  OBJECT\_TYPE, STATUS from T1;  
ID    OWNER   OBJECT\_NAME   OBJECT\_TYPE  STATUS         
\----- ------- ------------- ------------ -------    
1     SYS     ICOL$         INDEX        VALID          
2             I\_USER1       INDEX        VALID          
3     SYS     CON$                                      
4     SYS     UNDO$         TABLE                       
5     SYS                                VALID          
  
**select id, 'OWNER' null\_col from T1 where owner is null  
union all  
select id, 'OBJECT\_NAME' null\_col from T1 where OBJECT\_NAME is null  
union all  
select id, 'OBJECT\_TYPE' null\_col from T1 where OBJECT\_TYPE is null  
union all  
select id, 'STATUS' null\_col from T1 where STATUS is null;**  
ID  null\_col  
\--  --------  
2   owner  
3   OBJECT\_TYPE  
3   STATUS  
4   STATUS  
5   OBJECT\_NAME  
5   OBJECT\_TYPE  
  
**with T2 as(  
  select 'OWNER' colname from dual  
  union all  
  select 'OBJECT\_TYPE' colname from dual  
  union all  
  select 'STATUS' colname from dual  
  union all  
  select 'OBJECT\_NAME' colname from dual  
) ,TT as(select id,  
       decode(OWNER, null, 'OWNER') OWNER,  
       decode(OBJECT\_NAME, null, 'OBJECT\_NAME') OBJECT\_NAME,  
       decode(OBJECT\_TYPE, null, 'OBJECT\_TYPE') OBJECT\_TYPE,  
       decode(STATUS, null, 'STATUS') STATUS  
  from T1)  
 select tt.id, t2.colname  
   from tt, t2  
  where tt.OWNER = t2.colname  
     or tt.OBJECT\_NAME = t2.colname  
     or tt.OBJECT\_TYPE = t2.colname  
     or tt.STATUS = t2.colname;**  
ID  null\_col  
\--  --------  
2   owner  
3   OBJECT\_TYPE  
3   STATUS  
4   STATUS  
5   OBJECT\_NAME

5   OBJECT\_TYPE

**网上牛人是这么写的,unpivot可以过滤null的数据：**

select id,col  
  from ( select id  
               ,nvl2(owner,null,1) owner  
               ,nvl2(object\_name,null,1) object\_name  
               ,nvl2(object\_type,null,1) object\_type  
               ,nvl2(status,null,1) status  
           from t1  
       )  
unpivot (val for col in (owner,object\_name,object\_type,status));  
  
select id,col  
  from t1  
unpivot INCLUDE NULLS (val for col in (owner,object\_name,object\_type,status))  
where val is null;