简单的存储过程.检查表的每一个字段的count.

create or replace procedure test(ownername in varchar2,tablename in varchar2) is
type vref is ref cursor;
cur_col_cnt vref;
col_name varchar2(50);
sqltext varchar2(200);
colname varchar2(50);
cnt varchar2(50);
cursor cur_colname is
  select COLUMN_NAME from all_tab_columns where owner=ownername AND TABLE_NAME=tablename;
begin
open cur_colname;
loop
  fetch cur_colname into col_name;
  exit when cur_colname%notfound;
  sqltext:=’select ‘||col_name||’, count(1) from ‘||ownername||’.’||tablename||’ group by ‘||col_name;
  dbms_output.put_line(sqltext);
   open cur_col_cnt for sqltext;
   loop
     fetch cur_col_cnt into colname,cnt ;
     exit when cur_col_cnt%notfound;
     dbms_output.put_line(colname||’:’||cnt);
   end loop;
   close cur_col_cnt;
  
end loop;
close cur_colname;
end test;

发表评论

电子邮件地址不会被公开。 必填项已用*标注

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据