就是这个列的空值占这个列的百分之多少,所有表的所有列都要查询出来
自己写了 但是给游标赋值那地方搞不明白
CREATE OR REPLACE PROCEDURE query_emp
(p_m3 OUT number)
is
cursor c1 is select DISTINCT table_name from user_tab_columns;
cursor c2(p_tanme varchar2) is select column_name from user_tab_columns where table_name=p_tname;
v_tn VARCHAR2(100);
v_ln VARCHAR2(100);
v_i VARCHAR2(1000);
v_i1 VARCHAR2(1000);
v_i2 VARCHAR2(1000);
m1 NUMBER;
m2 NUMBER;
m3 NUMBER;
BEGIN
open c1 ;
open c2 ;
for i1 in c1 loop
p_tanme:=c1;
for i in c2(p_tanme) loop
v_i1:='SELECT count(*) FROM '|| p_tanme;
execute immediate v_i1 into m2 ;
-- v_i2:='select column_name from user_tab_columns where table_name=:clon';
--v_i2:='select column_name from user_tab_columns where table_name in (select DISTINCT table_name from user_tab_columns)
--and table_name='||upper(v_tn)||'';
-- execute immediate v_i2 into ?? using v_tn ;
v_ln:=c2;
v_i:='select count(*) from '|| v_tn||' where '|| v_ln||' is null';
execute immediate v_i into m1;
m3:= m1/m2;
end loop;
end loop;
END;
自己写了 但是给游标赋值那地方搞不明白
CREATE OR REPLACE PROCEDURE query_emp
(p_m3 OUT number)
is
cursor c1 is select DISTINCT table_name from user_tab_columns;
cursor c2(p_tanme varchar2) is select column_name from user_tab_columns where table_name=p_tname;
v_tn VARCHAR2(100);
v_ln VARCHAR2(100);
v_i VARCHAR2(1000);
v_i1 VARCHAR2(1000);
v_i2 VARCHAR2(1000);
m1 NUMBER;
m2 NUMBER;
m3 NUMBER;
BEGIN
open c1 ;
open c2 ;
for i1 in c1 loop
p_tanme:=c1;
for i in c2(p_tanme) loop
v_i1:='SELECT count(*) FROM '|| p_tanme;
execute immediate v_i1 into m2 ;
-- v_i2:='select column_name from user_tab_columns where table_name=:clon';
--v_i2:='select column_name from user_tab_columns where table_name in (select DISTINCT table_name from user_tab_columns)
--and table_name='||upper(v_tn)||'';
-- execute immediate v_i2 into ?? using v_tn ;
v_ln:=c2;
v_i:='select count(*) from '|| v_tn||' where '|| v_ln||' is null';
execute immediate v_i into m1;
m3:= m1/m2;
end loop;
end loop;
END;
declare
sql1 varchar2(500);
sql2 varchar2(500);
count1 number;
count2 number;
count3 number;
var1 varchar2(100);begin
for A IN (select distinct (table_name) as tablename from user_tab_columns) LOOP
for B IN (select COLUMN_NAME
from user_tab_columns
where table_name = A.TABLENAME and data_type <> 'CLOB' and data_type <> 'BLOB') LOOP
sql1 := 'select count('||B.COLUMN_NAME||') from '||A.TABLENAME;
sql2 := 'select count('||B.COLUMN_NAME||') from '||A.TABLENAME||' where '||B.COLUMN_NAME||' is null';
execute immediate sql1 into count1 ;
execute immediate sql2 into count2 ;
if count1 !=0 and count2 !=0 then
count3 :=count2/count1;
dbms_output.put_line(A.TABLENAME||'***'||B.COLUMN_NAME||':'||count3);
end if ;
end loop;
end loop;
end;