获取表的某些列只有空值

我需要知道一张表的哪些列只有空值。我知道我应该在 user_tab_columns 中执行循环。但是如何仅检测具有空值的列呢？ 感谢并抱歉我的英语

要在事先不知道列标识的情况下执行查询，需要使用动态 SQL。假设您已经知道该表不为空，您可以执行以下操作：

declare
  l_count pls_integer;
begin
  for r in (
    select table_name, column_name
    from user_tab_columns
    where table_name = 'T42'
    and nullable = 'Y'
  )
  loop
    execute immediate 'select count(*) '
      || ' from "' || r.table_name || '"'
      || ' where "' || r.column_name || '" is not null'
    into l_count;

    if l_count = 0 then
      dbms_output.put_line('Table ' || r.table_name
        || ' column ' || r.column_name || ' only has nulls');
    end if;
  end loop;
end;
/

记得set serveroutput on或执行前您的客户的同等内容。

游标从表中获取声明为可为空的列（如果不是，则没有太多必要检查它们；尽管这不会捕获显式检查约束）。对于每一列，它都会构建一个查询来计算该列不为空的行数。如果该计数为零，则它没有找到任何不为空的值，因此它们都是空的。再次假设您在开始之前知道表不为空。

我已将表名称包含在光标选择列表和引用中，因此您只需在一个位置更改名称即可搜索不同的表，或者您可以使用该名称的变量。或者通过更改该过滤器一次检查多个表。

通过从任何非空行中选择一个虚拟值，您可以获得更好的性能rownum停止检查 - 这意味着一旦发现非空值就会停止，而不必检查每一行以获得实际计数：

declare
  l_flag pls_integer;
begin
  for r in (
    select table_name, column_name
    from user_tab_columns
    where table_name = 'T42'
    and nullable = 'Y'
  )
  loop
    begin -- inner block to allow exception trapping within loop
      execute immediate 'select 42 '
        || ' from "' || r.table_name || '"'
        || ' where "' || r.column_name || '" is not null'
        || ' and rownum < 2'
      into l_flag;
      -- if this foudn anything there is a non-null value
    exception
      when no_data_found then
        dbms_output.put_line('Table ' || r.table_name
          || ' column ' || r.column_name || ' only has nulls');
    end;
  end loop;
end;
/

或者你可以做类似的事情exists() check.

如果您不知道该表有数据，那么您可以做一个简单的操作count(*)从循环之前的表中检查它是否为空，然后报告：

...
begin
  if l_count = 0 then
    dbms_output.put_line('Table is empty');
    return;
  end if;
...

或者你可以将它与游标查询结合起来，但是如果你想一次检查多个表，这将需要一些工作，因为一旦发现任何空表它就会停止（必须留给你一些事情要做...... *8 -)

declare
  l_count_any pls_integer;
  l_count_not_null pls_integer;
begin
  for r in (
    select table_name, column_name
    from user_tab_columns
    where table_name = 'T42'
    and nullable = 'Y'
  )
  loop
    execute immediate 'select count(*),'
      || ' count(case when "' || r.column_name || '" is not null then 1 end)'
      || ' from "' || r.table_name || '"'
    into l_count_any, l_count_not_null;

    if l_count_any = 0 then
      dbms_output.put_line('Table ' || r.table_name || ' is empty');
      exit; -- only report once
    elsif l_count_not_null = 0 then
      dbms_output.put_line('Table ' || r.table_name
        || ' column ' || r.column_name || ' only has nulls');
    end if;
  end loop;
end;
/

如果您不想回复，您当然可以填充一个集合或使其成为管道函数或其他任何东西dbms_output，但我认为这是一次性检查，因此可能是可以接受的。