Oracle中，取得表中所有字段都为null的个数

当表的字段个数很多的时候，取得所有字段都是null的个数
字段很少的时候,可以这么写：

select count(*) from tb where a is null and b is null ...

字段很多的时候就不是很方便了，下面用动态sql实现。

DECLARE
    TYPE myref IS REF CURSOR;
    cur     myref;
    sqlStr  VARCHAR2(200);
    tb_name VARCHAR2(20);
    TYPE arrays IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
    column_array arrays;
    null_num     INTEGER;
BEGIN
    tb_name := 'TB';
    sqlStr  := 'SELECT column_name FROM user_tab_columns WHERE table_name = ''' ||
               tb_name || '''';
    OPEN cur FOR sqlStr;
    FETCH cur BULK COLLECT
        INTO column_array;
    CLOSE cur;

    sqlStr := 'SELECT COUNT(*) FROM ' || tb_name || ' WHERE 1=1';
    FOR i IN column_array.FIRST .. column_array.LAST LOOP
        sqlStr := sqlStr || ' AND ' || column_array(i) || ' IS NULL ';
    END LOOP;

    EXECUTE IMMEDIATE sqlStr
        INTO null_num;
    dbms_output.put_line(null_num);
END;