求条查询语句,该怎么处理

求条查询语句
select table_name from user_tab_cols where column_name='test';
这样查出某用户所有tabel里有test列的tabel！
但我很多个tabel里都有test这个列，我要查出所有tabel里test列的某一条数据，和在那一个tabel，具体怎么写。。。???

------解决方案--------------------

SQL code

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;

求条查询语句,该怎么处理

相关推荐