存储过程批量修改数据库中部分表中的ip信息

一、背景

项目中涉及到环境迁移，迁移过程中，原来的数据库（oracle）中应用ip和数据库ip地址均未变更，每次都是手动修改，想通过存储过程快捷执行

二、修改目标：

把相关几个表格的ip地址变更为其他ip地址

三、脚本参考：

create or replace procedure reset_choice_env(service_host in varchar, db_host in varchar)
as
    db_host_suf varchar2(100);
begin
    db_host_suf := substr(db_host, instr(db_host, '.', 1, 3) + 1);
    update APP_CENTER.APP_SYS
    set value=regexp_replace(value, '172.16.3.(25[0-5]|2[0-4]\d|((1\d\d)|([1-9]?\d)))', service_host)
    where key = 'APP_VISIT_URL';

    update APP_CENTER.APPLICATION_INFO
    set URL = regexp_replace(URL, '172.16.3.(25[0-5]|2[0-4]\d|((1\d\d)|([1-9]?\d)))', service_host);

    update APP_CENTER.DATASOURCE
    set UNIQUEDB=db_host_suf, URL = regexp_replace(URL, '172.16.3.(25[0-5]|2[0-4]\d|((1\d\d)|([1-9]?\d)))', db_host);

    update APP_CENTER.DATA_SQL set UNIQUEDB=db_host_suf;

end;

四、调用存储过程

call model.RESET_CHOICE_ENV('192.168.3.99', '192.168.3.100');

五、参考知识点

1、存储过程的使用：存储过程这一篇就够了 - 知乎 (zhihu.com)

2、字符串截取：oracle操作字符串：拼接、截取、查找、替换_oracle拼接字符串函数_叶孤崖的博客-CSDN博客

3、 正则ip匹配：IP地址的正则表达式写法 - 一抹橄榄 - 博客园 (cnblogs.com)

六、出现问题处理：

1、存储过程语法错误：PL/SQL: ORA-00933: SQL 命令未正确结束,

由于：

update CONFIG.O_DY_CONF_THIRD_DATASOURCE
set UNIQUEDB=db_host_suf and URL = regexp_replace(URL, '172.16.3.(25[0-5]|2[0-4]\d|((1\d\d)|([1-9]?\d)))', db_host);

上方使用了and连接，语法错误，修改为逗号“,”连接多个待修改的列即可

2、存储过程定义变量未设置长度：String length constraints must be in range (1 .. 32767)

定义局部变量时为设置长度：

db_host_suf varchar2;

修改为以下即可

db_host_suf varchar2(100);