如何在Stored Procedure中使用Substitution 避免编译时输入弹窗

核心原因 #

替换变量（以&为前缀标识）是**SQL客户端工具（SQL*Plus、Oracle SQL Developer、PL/SQL Developer等）**的前置解析功能，会在你把SQL语句提交到数据库执行/编译前就完成变量替换，所以默认写在存储过程里的&标识会在编译提交阶段触发客户端的输入弹窗，不属于数据库层面的编译逻辑。 #

可用解决方案 #

• 方案1：临时关闭客户端替换变量解析功能
  编译存储过程前，在当前会话执行命令：

  SET DEFINE OFF
  

  编译完成后如果需要恢复替换变量功能，可执行SET DEFINE ON。
• 方案2：转义存储过程内的&符号
  如果你的存储过程代码中本身需要把&作为普通字符使用（比如拼接字符串、硬编码特殊标识），可以通过两种方式转义避免被识别为替换变量：
  1. 拆分字符串拼接：把包含&的内容拆成多段拼接，例如把'https://xxx.com?a=1&b=2'改写为'https://xxx.com?a=1'||'&'||'b=2'
  2. 用ASCII码生成：调用CHR(38)生成&符号，例如上述字符串可改写为'https://xxx.com?a=1'||CHR(38)||'b=2'
• 方案3：用数据库原生传参逻辑替代替换变量
  如果你原本的需求是让存储过程在执行阶段才接收传入值，不要使用客户端层面的替换变量，改为以下两种数据库原生的传参方式：
  1. 把需要动态传入的值定义为存储过程的正式入参
  2. 执行阶段使用以:为前缀的绑定变量传参

以上方案均不需要修改存储过程的核心业务逻辑，即可避免编译阶段的弹窗 #

内容的提问来源于stack exchange，提问作者Caroline