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('要查找内容')

您可能感兴趣的文章: