Oracle存储过程设置依赖其他入参的默认值报PLS-00227错误如何解决

报错根因 #

Oracle PL/SQL语法不允许子程序的入参DEFAULT默认值子句，引用同个子程序中定义的其他入参，这就是出现PLS-00227错误的直接原因。

可行规避方案 #

方案1：入参默认设为NULL，过程内计算实际值（最常用） #

无需修改存储过程对外的调用约定，仅在内部新增局部变量处理逻辑即可：

create or replace procedure xxx (
  i_start_date in date,
  i_end_date IN DATE DEFAULT NULL, 
  i_interval IN PLS_INTEGER DEFAULT 10
) as
  -- 声明局部变量存储实际使用的结束日期
  l_end_date DATE;
BEGIN
  -- 空值判断走默认计算逻辑
  IF i_end_date IS NULL THEN
    l_end_date := TRUNC(i_start_date) + 1;
  ELSE
    l_end_date := i_end_date;
  END IF;

  INSERT INTO access_history (employee_id, card_num, location_id, access_date)
  WITH date_rows ( start_date, end_date ) AS (
   SELECT i_start_date,
          l_end_date
   FROM   DUAL
  UNION ALL
   SELECT start_date + NUMTODSINTERVAL(i_interval, 'MINUTE'),
          end_date
   FROM   date_rows
   WHERE  start_date + NUMTODSINTERVAL(i_interval, 'MINUTE') < end_date
  )
  SELECT e.employee_id
        ,e.card_num
        ,l.location_id
        ,d.start_date
  FROM   employees e
  CROSS JOIN locations l
  CROSS JOIN date_rows d;
END;
/

该方案优点：原有调用逻辑完全不需要改动，传入end_date就用传入值，不传就自动计算，完全符合需求。

方案2：重载存储过程 #

如果不想修改原有三参数存储过程的逻辑，可以新增一个两参数的重载版本：

-- 原有三参数存储过程保留，删除i_end_date的默认值即可
create or replace procedure xxx (
  i_start_date in date,
  i_end_date IN DATE, 
  i_interval IN PLS_INTEGER DEFAULT 10
) as
BEGIN
  INSERT INTO access_history (employee_id, card_num, location_id, access_date)
  WITH date_rows ( start_date, end_date ) AS (
   SELECT i_start_date,
          i_end_date
   FROM   DUAL
  UNION ALL
   SELECT start_date + NUMTODSINTERVAL(i_interval, 'MINUTE'),
          end_date
   FROM   date_rows
   WHERE  start_date + NUMTODSINTERVAL(i_interval, 'MINUTE') < end_date
  )
  SELECT e.employee_id
        ,e.card_num
        ,l.location_id
        ,d.start_date
  FROM   employees e
  CROSS JOIN locations l
  CROSS JOIN date_rows d;
END;
/

-- 新增重载版本，仅接收i_start_date和i_interval，内部计算end_date后调用三参数版本
create or replace procedure xxx (
  i_start_date in date,
  i_interval IN PLS_INTEGER DEFAULT 10
) as
BEGIN
  xxx(i_start_date, TRUNC(i_start_date)+1, i_interval);
END;
/

该方案适合需要兼容多套调用逻辑的场景。

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