在PLSQL工具上,打开“命令窗口”工具
1、开启日志输出功能:
SQL> set serveroutput on;
2、输入SQL块内容; 下面:log_items 和 where 后面根据情形自己调整;
DECLARE
v_sql varchar2(2000);-- 定义sql
v_rows number; -- 定义非空列的数据总数
v_totleRows number; -- 定义被查询表的总数
BEGIN
select count(l.id) into v_totleRows from log_items l where trunc(l.timestamp)>=trunc(to_date('2020-01-01','yyyy-MM-dd'));
for i in (select column_name from all_tab_columns where TABLE_NAME = 'log_items ') loop
v_sql :='select count(1) from log_items where '|| i.column_name ||' is not null and trunc(timestamp)>=trunc(to_date(''2020-01-01'',''yyyy-MM-dd''))';
execute immediate v_sql into v_rows;
if v_rows = v_totleRows then
dbms_output.put_line('log_items -->【' || i.column_name || '】字段全部有值');
end if;
end loop;
END;
/