create or replace procedure PROC_IS_NULL as
begin
declare
--变量
--拼接查询字段
l_value_sql varchar2(2000);
--错误结果拼接
l_error_message clob;
--拼接where语句
l_where_sql varchar2(2000);
--插入sql
l_insert_sql clob;
cursor tra_table is
select distinct table_name from all_tab_columns where table_name like '%_TRA';
begin
for tra in tra_table loop
--判断是否为空,将为空数据插入到err表
--拼接查询字段
select replace(wm_concat(column_name),'.',',') into l_value_sql from all_tab_columns where table_name=tra.table_name --and column_name<>'ERRORRESULT';
--拼接错误结果
select rtrim(replace(wm_concat('nvl2('||column_name||','||''''||column_name||'为空'''||','||''''''||'),'),',,','||'),',') into l_error_message from all_tab_columns where table_name='QY_'||tra.table_name||'_SUC' AND NULLABLE='N';
--拼接where语句
select substr(replace(wm_concat('or '||column_name||' IS NULL '),',',' '),4) into l_where_sql from all_tab_columns where table_name='QY_'||tra.table_name||'_SUC' AND NULLABLE='N';
--插入错误表为空数据
if length(l_error_message) > 0 then
l_insert_sql := 'insert into QY_'||tra.table_name||'_EOR ('||l_value_sql||',ERRORRESULT)'||' select '||l_value_sql||','||l_error_message||' from '||tra.table_name||' where '||l_where_sql;
Execute immediate l_insert_sql;
end if;
--判断数据是否合法,将不合法数据插入到err表
--拼接where条件
delete from long_to_char;
insert into long_to_char(sc,column_name) select to_lob(UC.search_condition),ucc.column_name sc from user_cons_columns ucc left join user_constraints uc on ucc.constraint_name = uc.constraint_name where ucc.table_name= 'QY_'||tra.table_name||'_SUC' and generated='USER NAME';
select replace(replace(substr(wm_concat('or '||to_char(sc)),3),'),or',') or'),'IN (','NOT IN (') into l_where_sql from long_to_char;
if length(l_where_sql)>0 then
--拼接错误语句
select replace(wm_concat('case when '||to_char(sc)||' then '||''''''||' else '||''''||column_name||'无效'||''''||' end'),',case when','|| case when') into l_error_message from long_to_char;
l_insert_sql := 'insert into QY_'||tra.table_name||'_EOR ('||l_value_sql||',ERRORRESULT)'||' select '||l_value_sql||','||l_error_message||' from '||tra.table_name||' where '||l_where_sql;
Execute immediate l_insert_sql;
end if;
--判断字符串是否超长
end loop;
--将不在错误表中的数据插入到正确表
--declare
-- cursor tra_suc_tables is
-- select distinct table_name from all_tab_columns where table_name like '%_TRA';
-- begin
-- for tra_suc_table in tra_suc_tables loop
----------
-- end loop;
-- end;
commit;
end;
end;