oracle 根据列值查表

 set serveroutput on
declare
  v_sql  varchar2(1000);
  data_count number;
begin
  for cur_table in (select distinct  table_name
                      from user_tab_columns a) loop
    for cur_column in (select distinct column_name
                         from user_tab_columns a
                        where a.TABLE_NAME = cur_table.table_name) loop
      v_sql := 'select count(*) from ' || cur_table.table_name ||
               '  where instr(' || cur_column.column_name ||
               ',''lijie'')>0';
      execute immediate v_sql into data_count;
      if (data_count > 0) then
        dbms_output.put_line(cur_table.table_name);
        dbms_output.put_line(cur_column.column_name);
        dbms_output.put_line(data_count || ':' || v_sql);
      end if;
    end loop;
  end loop;
  end;
  /



SET SERVEROUTPUT ON SIZE 100000
 
    DECLARE
      match_count INTEGER;
    BEGIN
      FOR t IN (SELECT owner, table_name, column_name
                  FROM all_tab_columns
                  WHERE owner = 'SCOTT' and (data_type LIKE '%CHAR%' or data_type like '%VARCHAR%') ) LOOP
 
        EXECUTE IMMEDIATE
          'SELECT COUNT(*) FROM ' || t.owner || '.' || t.table_name ||
          ' WHERE '||t.column_name||' = :1'
          INTO match_count
          USING 'lijie';
 
        IF match_count > 0 THEN
          dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count );
        END IF;
 
      END LOOP;
 
    END;
    /
 
--USING 后面是要查的字符串