查找Oracle數(shù)據(jù)庫中任意字段的值

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下運(yùn)行以上代碼，執(zhí)行 exec test('要查找內(nèi)容')

服務(wù)器租用/服務(wù)器托管中國五強(qiáng)！虛擬主機(jī)域名注冊(cè)頂級(jí)提供商！15年品質(zhì)保障！--億恩科技[ENKJ.COM]