调试了很长时间,菜就一个字 注意个问题,需要先确定字段类型,比如字符还是数字。 declare sReturn int; v_stmt varchar2(500); type cursor_type is ref cursor; c1 cursor_type; c2 cursor_type; v_table_name varchar2(500); v_column_name varchar2(500); data_type varchar2(20); begin --data_type; data_type:='VARCHAR2'; open c1 for 'select table_name,column_name from USER_TAB_COLUMNS '||' where data_type='''||data_type||''''; dbms_output.put_line('select table_name,column_name from USER_TAB_COLUMNS '||' where data_type='''||data_type||'''');
loop
fetch c1 into v_table_name,v_column_name; exit when c1%notfound; --dbms_output.put_line(v_table_name||'---'||v_column_name);
v_stmt:='select count(*) from '||v_table_name||' where '||v_column_name||'=''SMITH'''; --dbms_output.put_line(v_stmt); open c2 for v_stmt; loop fetch c2 into sReturn; exit when c2%notfound; if sReturn=1 then dbms_output.put_line(v_table_name||'---'||v_column_name); end if; end loop;
end loop; exception when others then dbms_output.put_line(sqlerrm); raise;
注意个问题,需要先确定字段类型,比如字符还是数字。
declare
sReturn int;
v_stmt varchar2(500);
type cursor_type is ref cursor;
c1 cursor_type;
c2 cursor_type;
v_table_name varchar2(500);
v_column_name varchar2(500);
data_type varchar2(20);
begin
--data_type;
data_type:='VARCHAR2';
open c1 for 'select table_name,column_name from USER_TAB_COLUMNS '||' where data_type='''||data_type||'''';
dbms_output.put_line('select table_name,column_name from USER_TAB_COLUMNS '||' where data_type='''||data_type||'''');
loop
fetch c1 into v_table_name,v_column_name;
exit when c1%notfound;
--dbms_output.put_line(v_table_name||'---'||v_column_name);
v_stmt:='select count(*) from '||v_table_name||' where '||v_column_name||'=''SMITH''';
--dbms_output.put_line(v_stmt);
open c2 for v_stmt;
loop
fetch c2 into sReturn;
exit when c2%notfound;
if sReturn=1 then
dbms_output.put_line(v_table_name||'---'||v_column_name);
end if;
end loop;
end loop;
exception
when others then
dbms_output.put_line(sqlerrm);
raise;
end;