- create or replace procedure STR_REPLACE_ALL(oldStr in varchar2,newStr in varchar2) is
- TABLE_NAME VARCHAR2(45);--表名
- COLUMN_NAME VARCHAR2(100);--字段名
- SQL_STR VARCHAR2(200);--动态执行的SQL
- type cur_type is ref cursor;--定义游标类型
- cursor_columns cur_type;--查询字段游标
- cursor cursor_tables is--查询表游标
- select table_name from user_tables;
- begin
- open cursor_tables;
- loop
- --遍历所有的数据库表
- fetch cursor_tables into TABLE_NAME;
- exit when cursor_tables%notfound;
- DBMS_OUTPUT.put_line(TABLE_NAME);
- --遍历当前表所有字符型字段
- SQL_STR := 'select COLUMN_NAME from user_tab_columns where TABLE_NAME='''||TABLE_NAME||''' and DATA_TYPE in (''CHAR'',''VARCHAR2'')';
- open cursor_columns for SQL_STR;
- loop
- fetch cursor_columns into COLUMN_NAME;
- exit when cursor_columns%notfound;
- --DBMS_OUTPUT.put_line('---'||COLUMN_NAME);
- --替换更新当前字段
- SQL_STR := 'update '||TABLE_NAME||' set '||COLUMN_NAME||' =replace('||COLUMN_NAME||','''||oldStr||''','''||newStr||''')';
- --DBMS_OUTPUT.put_line(SQL_STR);
- EXECUTE IMMEDIATE SQL_STR;
- commit;
- end loop;
- end loop;
- end STR_REPLACE_ALL;
- --该片段来自于http://www.codesnippet.cn/detail/301220138298.html
来源: http://www.codesnippet.cn/detail/301220138298.html