Oracle执行alter table操作后带参数的select存储过程失效如何解决？

当尝试修改表结构时，仅包含select语句的存储过程可能出现失效问题，复现脚本如下：

create table t1(a number,b number);

create or replace procedure p1
is
x number;
y  number;
begin
select a,b into x,y from t1;
end;
/

create or replace procedure p2(i number)
is
x number;
y  number;
begin
select a,b into x,y from t1 where i=1;
end;
/

alter table t1 add (d number);

select object_name,status from dba_objects where object_name in ('T1','P1','P2');

-- 查询结果
T1  VALID
P1  VALID
P2  INVALID

观测到的现象为：当存储过程带有入参且在select语句中使用该参数时，表结构变更后存储过程会失效，否则不会失效。

这是Oracle数据库存储过程依赖校验的固有特性：

• 无参数、无绑定变量的纯静态SQL，Oracle做依赖校验时仅判断SQL引用的列是否被修改/删除，新增列操作不影响现有查询逻辑，因此不会标记存储过程失效，对应案例中的P1。
• 包含输入参数（绑定变量）的SQL，Oracle会执行更严格的依赖校验，只要关联表结构发生任意变更（包括新增列这类不影响现有查询的操作），都会标记关联存储过程为INVALID，对应案例中的P2。

• 使用动态SQL封装查询逻辑：动态SQL仅在存储过程运行时解析，编译阶段不会做依赖校验，表结构变更不会触发存储过程失效。改造P2的示例如下：

  create or replace procedure p2(i number)
  is
  x number;
  y  number;
  begin
  execute immediate 'select a,b from t1 where :i=1' into x,y using i;
  end;
  /
  

• 若使用Oracle 11g及以上版本，可开启*基于版本的重定义（edition-based redefinition）*特性，表结构变更操作在新版本中执行，原有版本中的存储过程完全不受影响，所有依赖对象验证完成后再切换版本即可。
• 若业务可接受极短的首次调用耗时，无需额外改造：Oracle在调用失效存储过程时会自动尝试重新编译，只要表变更没有修改/删除存储过程引用的列，编译就会成功，不会影响业务正常使用。也可以在alter操作完成后手动批量编译所有失效对象，避免首次调用的编译耗时。

如果alter操作涉及修改、删除存储过程中已经引用的列，无论使用哪种方案，存储过程运行时都会报错，该场景必须同步修改存储过程的查询逻辑。

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