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