日期:2014-05-17 浏览次数:20899 次
declare sqlText varchar2(100); countNum number; type tableNameType is table of varchar2(100) index by binary_integer; tableName tableNameType; begin select distinct table_name bulk collect into tableName from user_tab_cols where column_name='USA'; dbms_output.put_line(tableName.count()); for v_index in 1..tableName.count() loop sqlText:= 'select count(*) from ' ||tableName(v_index)|| ' where usa=to_char(2)'; execute immediate sqlText into countNum ; if(countNum>0) then dbms_output.put_line(tableName(v_index)); end if ; end loop; end;
------解决方案--------------------
-- Created on 2012/2/15 by ADMINISTRATOR
Declare
colnm Constant Varchar2(10) := 'VEHICLE_ID'; --你要找的字段名
colv Varchar2(20) := '82'; --你要找的值
isExists Integer;
Begin
For r_up In (Select table_name From user_tab_cols Where column_name = colnm) Loop
Execute Immediate 'select case when exists (select ' || colnm || ' from ' ||
r_up.table_name ||
') then 1 else 0 end isExists from dual'
Into isExists;
If isExists = 1 Then
--输出所有符合条件的表
dbms_output.put_line(r_up.table_name);
End If;
End Loop;
End;