oracle 如何使用 PL/SQL 在表中查找空列的数量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6291112/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to find Number of null column in table using PL/SQL
提问by subrat
A database has a lot of columns (more than 100). Some of these columns have null entries. How can I find out how many columns have null entries in at least one row, without manually testing each and every column?
一个数据库有很多列（超过 100 个）。其中一些列具有空条目。如何在不手动测试每一列的情况下，找出至少一行中有多少列具有空条目？
回答by Tony Andrews
Try:
尝试：
declare
l_count integer;
begin
for col in (select table_name, column_name
from user_tab_columns where table_name='EMP')
loop
execute immediate 'select count(*) from '||col.table_name
||' where '||col.column_name
||' is not null and rownum=1'
into l_count;
if l_count = 0 then
dbms_output.put_line ('Column '||col.column_name||' contains only nulls');
end if;
end loop;
end;
回答by tbone
Try analyzingyour table (compute statistics, don't estimate) and then (immediately) do:
尝试分析您的表格（计算统计数据，不要估计），然后（立即）执行以下操作：
select column_name, num_nulls
from all_tab_columns
where table_name = 'SOME_TABLENAME'
and owner = 'SOME_OWNER';
Of course as data later changes, this will become slightly more incorrect. If you need to get more fancy and do a field population count (fieldpop), then you'll need to loop through all rows and check for nulls explicitly (and exclude any other values you deem "not populated", perhaps a default of 0 for a number field for example).
当然，随着数据后来的变化，这会变得稍微不正确。如果您需要更花哨并进行字段人口计数（fieldpop），那么您需要遍历所有行并明确检查空值（并排除您认为“未填充”的任何其他值，可能默认值为 0例如，对于数字字段）。
回答by Hasan Fahim
I can give you the direction in which to research:
我可以给你研究的方向：
Check "user_tab_columns" through which you can get information related to columns in a table. E.g.
检查“user_tab_columns”，您可以通过它获取与表中列相关的信息。例如
select count(*) from user_tab_columns where table_name = 'YOURTABLENAME'
This gives you the number of columns in that table.
这为您提供了该表中的列数。
Together with this you would need to use a cursor, i think, to check each column for null values rather than adding a null check in WHERE clause for each column.
与此一起，我认为您需要使用游标来检查每列的空值，而不是在每列的 WHERE 子句中添加空检查。
回答by Phil
This will give you the number of NULL column values per row of data:
这将为您提供每行数据的 NULL 列值数：
declare
TYPE refc IS REF CURSOR;
col_cv refc;
l_query varchar(3999);
v_rownum number;
v_count number;
begin
l_query := 'select rownum, ';
for col in (select table_name, column_name
from user_tab_columns where table_name='EMP')
loop
l_query := l_query ||'DECODE('||col.column_name||',NULL,1,0)+';
end loop;
l_query := l_query||'+0 as no_of_null_values from EMP';
DBMS_OUTPUT.PUT_LINE(l_query);
OPEN col_cv FOR l_query;
LOOP
FETCH col_cv into v_rownum, v_count;
EXIT WHEN col_cv%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_rownum || ' ' || v_count);
END LOOP;
CLOSE col_cv;
end;
I feel dirty even writing it! (It won't work when the number of columns in the table is very large and l_query overflows).
连写都觉得脏！（当表中的列数非常大并且 l_query 溢出时，它不起作用）。
You just need to change the table name (EMP above).
您只需要更改表名（上面的 EMP）。
