plsql设置字段可为空_plsql列出包含空值的所有table.column

一種方法可以用一些動態SQL，但這需要一些PL/SQL代碼;下面只使用SQL來得到你所需要的結果：

select *

from (

select table_name,

column_name,

to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) c from '||table_name || ' where ' || column_name || ' is null')),'/ROWSET/ROW/C')) as rowcount

from user_tab_columns

where nullable='Y'

and table_name in ('myTalbe', 'myTable2')

)

where rowcount > 0

這可能是使用動態SQL的方法：

declare

type tableOfNumber is table of number;

type tableOfChar is table of varchar2(30);

--

vSQl varchar2(4000);

vListNumbers tableOfNumber;

vListTables tableOfChar;

vListColumns tableOfChar;

begin

select listagg('select ''' ||

table_name || ''' as table_name, ''' ||

column_name || ''' as column_name, count(*) as rowCount from ' ||

table_name ||

' where ' ||

column_name ||

' is null having count(*) > 0' ,

' UNION ALL '

) within group (order by table_name, column_name)

into vSQL

from user_tab_columns

where nullable='Y'

and table_name in ('myTalbe', 'myTable2');

--

dbms_output.put_line(vSQL);

/* whatever you may want to do with the query */

/* for example, fetch into some variables and print the result */

execute immediate vSQL

bulk collect into vListTables, vListColumns, vListNumbers;

--

if vListTables.count() > 0 then

for i in vListTables.first .. vListTables.last loop

dbms_output.put_line('Table ' || vListTables(i) ||

', column ' || vListColumns(i) ||

', number of nulls: ' || vListNumbers(i)

);

end loop;

end if;

end;