oracle查询字段中值,查找Oracle数据库中任意字段的值

create or replace procedure test(str varchar) as

cursor c_cursor is

select t.TABLE_NAME, t.COLUMN_NAME

from user_tab_columns t

right join (select * from user_objects o where o.OBJECT_TYPE = 'TABLE') o1

on t.TABLE_NAME = o1.OBJECT_NAME;

v_name      varchar(10000);

active_sql varchar(10000);

v_result    varchar(10000);

tempstr varchar(10000):='shiningSearch';

type cur is ref cursor;

c cur;

begin

for v_name in c_cursor loop

active_sql := 'select ' || v_name.column_name || ' from ' ||

v_name.table_name || ' where ' || v_name.column_name ||

' like ''%' || str || '%''';

--dbms_output.put_line(v_name.table_name);--debugerror

open c for active_sql;

loop FETCH c INTO v_result;

if v_result != ' ' and tempstr !=v_name.table_name then

tempstr:=v_name.table_name;

dbms_output.put_line('select * from '||v_name.table_name|| ' where '|| v_name.column_name || ' like''%'||str||'%''');

end if;

exit when c%notfound;

v_result:='';

end loop;

CLOSE c;

end loop;

end test;

/

set serveroutput on size 100000

--使用方法在command下运行以上代码，执行 exec test('要查找内容')