éè¦ç¨å°å¾ªç¯åå¨æsqlã
å¦test表ä¸æå¦ä¸æ°æ®ï¼å
¶ä¸idånameåæ空å¼ã
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/d833c895d143ad4ba38000e685025aafa40f066d?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)
æ§è¡ä»¥ä¸å
容ï¼
declare
v_count int;--å®ä¹åé
v_col varchar2(20);--å®ä¹åé
v_sql varchar2(2000);--å®ä¹åé
v_last_col varchar2(20);--å®ä¹åé
cursor cur_col is select column_name from user_tab_cols where table_name='TEST' order by column_id;--å®ä¹
游æ begin
select column_name into v_last_col from user_tab_cols where table_name='TEST' and column_id=(select max(column_id) from user_tab_cols where table_name='TEST');--ååºè¡¨ä¸æåä¸ä¸ªå段æ¾å
¥v_last_col
open cur_col;--æå¼æ¸¸æ
loop --æ§è¡å¾ªç¯
fetch cur_col into v_col;--ååºæ¸¸æ å
容å°åév_col
v_sql:='select count(*) from TEST where '||v_col||' is null';
execute immediate v_sql into v_count;--æ§è¡å¨æsql
if v_count>0--å¦ææ¥è¯¢æ空å¼çå段ä¸ä¸ºç©ºï¼åè¾åºæ¤å段å
then
dbms_output.put_line(v_col);
end if;
exit when v_col=v_last_col; --éåºå¾ªç¯æ¡ä»¶
end loop;--ç»æ循ç¯
close cur_col;--å
³é游æ
end;
æ§è¡ç»æï¼
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/55e736d12f2eb93893cd3bd4d2628535e5dd6f77?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)