1 create or replace procedure search(v_colname in varchar2, v_find in varchar2) as
2 v_sql varchar2(2000);
3 v_result varchar2(1000);
4 v_cursor number;
5 v_stat number;
6 begin
7 dbms_output.put_line('表名,列名,结果');
8 for a in (select table_name,column_name from user_tab_columns where column_name = v_colname) loop
9 v_cursor := dbms_sql.open_cursor;
10 v_sql := 'select ' || v_colname || ' from ' || a.table_name || ' where ' || v_colname || ' like ''%' || v_find || '%''';
11 dbms_sql.parse(v_cursor,v_sql,dbms_sql.native);
12 dbms_sql.define_column(v_cursor,1,v_result,1000);
13 v_stat := dbms_sql.execute(v_cursor);
14 loop exit when dbms_sql.fetch_rows(v_cursor)<=0;
15 dbms_sql.column_value(v_cursor,1,v_result);
16 dbms_output.put_line(a.table_name||','||a.column_name||','||v_result);
17 end loop;
18 dbms_sql.close_cursor(v_cursor);
19 end loop;
20 end search;
begin search('...','...'); end;
