DECLARE
CURSOR cv_tables
IS
SELECT table_name,column_name
FROM user_tab_columns t
WHERE data_type='CHAR'
OR data_type ='VARCHAR2'
OR data_type ='VARCHAR';
v_sql LONG;
v_update_sql LONG;
v_count NUMBER;
v_table_name VARCHAR2(30);
v_column_name VARCHAR2(30);
BEGIN
dbms_output.put_line('开始...');
OPEN cv_tables;
LOOP
FETCH cv_tables INTO v_table_name,v_column_name;
EXIT
WHEN cv_tables%notfound;
v_sql := 'select count(*) from '||v_table_name||' where '||v_column_name||' like '||'''%关键字%''' ;
EXECUTE IMMEDIATE v_sql INTO v_count;
IF v_count >= 1 THEN
dbms_output.put_line('表['||v_table_name||'] 列['||v_column_name || ']');
v_update_sql := 'update ' || v_table_name ||' set '||v_column_name||' = replace('||v_column_name||', ''关键字'', ''替换后的字符串'') where '||v_column_name||
' like ''%关键字%''';
EXECUTE IMMEDIATE v_update_sql;
END IF;
END LOOP;
CLOSE cv_tables;
dbms_output.put_line('结束.');
END;
转载于:https://my.oschina.net/gxchan/blog/549495