Oracle包迁移至PostgreSQL的PL/pgSQL嵌套子程序与结构咨询

PL/pgSQL 目前不支持在存储过程或函数内部嵌套定义其他存储过程、函数，Oracle PL/SQL 中嵌套子程序的语法无法直接复用，所有的存储过程、函数都必须定义为 schema 级别的全局对象，你可以通过权限控制、命名约定来实现原嵌套子程序的作用域隔离效果：

• 仅被单个主存储过程调用的子程序，命名添加下划线前缀+主过程名标识，例如主过程名为proc_settle_account，对应的内部子程序可命名为_proc_settle_account_calc_tax，明确标识为内部使用不对外暴露
• 原嵌套子程序用到的主过程变量，统一调整为子程序的入参传递，耦合度更低也更易排查问题
• 原存储过程中定义的游标可以直接迁移，PL/pgSQL 对游标语法的兼容度很高，仅需少量调整即可正常运行

结合你正在拆分Oracle包的场景，推荐按以下规则组织代码文件：

• 根目录按业务域拆分文件夹，例如finance、operation、user，不同业务线的存储过程分开存放
• 每个业务域目录下设置三个子目录：
  • main_procs：存放主存储过程文件，每个主过程对应一个独立SQL文件，命名为{主过程名}.sql，文件头部标注对应原Oracle包名、原过程位置方便追溯
  • internal_procs：存放仅当前业务域使用的内部子程序，单个主过程专用的子程序按_{主过程名}_{子过程标识}.sql规则命名，多个主过程复用的公共子程序去掉主过程名前缀即可
  • auxiliary：存放配套的自定义类型、常量定义、数据校验脚本等辅助内容
• 数量较多的嵌套子程序迁移前先做一轮梳理，重复逻辑尽量合并抽成公共子程序，避免冗余代码

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