根据数据字典来找 select table_name from all_tab_cols where owner=upper('giapuser') and column_name=upper('sjgxr')
在giapuser用户下执行: select table_name from user_tab_columns where column_name = upper('sjgxr')
table_name from user_tab_columns
select table_name from all_tab_cols where owner=upper('giapuser') and column_name=upper('sjgxr')
create table a(a varchar2(100)); create or replace procedure pro_test as cursor c is select table_name from user_tab_columns where column_name = upper('sjgxr') and table_name not like '%$%'; table_name varchar2(100); val number(10); strsql varchar2(1000); strinsert varchar2(1000); -- strsql:='select count(*) into val from ' || table_name ||' where sjgxr is null'; begin val := 0; execute immediate 'truncate table A'; open c; loop fetch c into table_name; exit when c%notfound; strsql := 'select count(*) coun from ' || table_name || ' where sjgxr is null'; execute immediate strsql into val; if val >0 then strinsert := ' insert into a(a) values(''' || table_name || ''')'; execute immediate strinsert; commit; end if;
end loop; close c; end;
Select owner, table_name, data_type From All_Tab_Cols Where Owner = upper('giapuser') And column_name = upper('sjgxr') 正解,可以结贴了
SELECT TABLE_NAME FROM sys.dba_tab_columns WHERE owner = upper('giapuser') AND COLUMN_NAME = upper('sjgxr')
select table_name from all_tab_cols
where owner=upper('giapuser')
and column_name=upper('sjgxr')
select table_name from user_tab_columns where column_name = upper('sjgxr')
where owner=upper('giapuser')
and column_name=upper('sjgxr')
create or replace procedure pro_test as
cursor c is
select table_name
from user_tab_columns
where column_name = upper('sjgxr') and table_name not like '%$%';
table_name varchar2(100);
val number(10);
strsql varchar2(1000);
strinsert varchar2(1000);
-- strsql:='select count(*) into val from ' || table_name ||' where sjgxr is null';
begin
val := 0;
execute immediate 'truncate table A';
open c;
loop
fetch c
into table_name;
exit when c%notfound;
strsql := 'select count(*) coun from ' || table_name ||
' where sjgxr is null';
execute immediate strsql into val;
if val >0 then
strinsert := ' insert into a(a) values(''' || table_name || ''')';
execute immediate strinsert;
commit;
end if;
end loop;
close c;
end;
From All_Tab_Cols
Where Owner = upper('giapuser')
And column_name = upper('sjgxr')
正解,可以结贴了