数据校验脚本-检验null值

  在做数据校验，需要检验多个字段是否为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;