执行Oracle存储过程时遭遇PLS-00103错误，请求协助排查

问题描述 #

执行存储过程时触发如下错误：

Error(17,3): PLS-00103: Encountered the symbol "FOR" when expecting one of the following: ( - + case mod new not null select with continue avg count current exists max min prior sql stddev sum variance execute fora

原存储过程代码：

PROCEDURE DeactiveUsers (
      P_DeactiveUsers_OUT      OUT SYS_REFCURSOR ) AS
  BEGIN
       
       
   OPEN P_DeactiveUsers_OUT FOR
  for P_DeactiveUsers_Lst in(
    select * from (    
        select username, MAX(TRANSACTION_DATE) As last_login_date 
        from r4g_application_activity_log
        Group By username
    ) where last_login_date <= sysdate-90
    order by 2 desc
  )
  
  update r4g_application_activity_log
  set ISACTIVE = 1
  where USERNAME = P_DeactiveUsers_OUT.username;
         
    EXCEPTION 
    WHEN no_data_found THEN 
    INS_UMS_ERRORLOG(SQLCODE||' : '||SUBSTR(SQLERRM, 1, 200),null,'DeactiveUsers',null,null,null,'DB : DeactiveUsers','Scheduler - UMS_DeactiveUser');
    WHEN others THEN 
    INS_UMS_ERRORLOG(SQLCODE||' : '||SUBSTR(SQLERRM, 1, 200),null,'DeactiveUsers',null,null,null,'DB : DeactiveUsers','Scheduler - UMS_DeactiveUser');
  END DeactiveUsers;

错误原因 #

1. 语法混用冲突：同时使用OPEN ... FOR打开输出游标和FOR ... IN循环语法，两者不能嵌套使用。OPEN P_DeactiveUsers_OUT FOR后应直接跟查询语句，而非循环结构。
2. 游标引用错误：更新语句中错误引用输出游标P_DeactiveUsers_OUT.username，输出游标无法直接访问行数据，需用循环变量或批量查询逻辑。
3. 循环结构不完整：FOR循环缺少对应的LOOP和END LOOP语句，语法不闭合。

修正方案 #

方案1：批量更新+输出结果（推荐，性能更优） #

通过批量更新处理状态，同时打开游标返回符合条件的用户列表：

PROCEDURE DeactiveUsers (
      P_DeactiveUsers_OUT      OUT SYS_REFCURSOR ) AS
BEGIN
    -- 打开游标，输出90天未登录的用户列表
    OPEN P_DeactiveUsers_OUT FOR
        SELECT username, MAX(TRANSACTION_DATE) AS last_login_date 
        FROM r4g_application_activity_log
        GROUP BY username
        HAVING MAX(TRANSACTION_DATE) <= SYSDATE - 90
        ORDER BY last_login_date DESC;

    -- 批量更新所有符合条件用户的ISACTIVE状态
    UPDATE r4g_application_activity_log
    SET ISACTIVE = 1
    WHERE username IN (
        SELECT username 
        FROM r4g_application_activity_log
        GROUP BY username
        HAVING MAX(TRANSACTION_DATE) <= SYSDATE - 90
    );

EXCEPTION 
    WHEN NO_DATA_FOUND THEN 
        INS_UMS_ERRORLOG(SQLCODE||' : '||SUBSTR(SQLERRM, 1, 200),null,'DeactiveUsers',null,null,null,'DB : DeactiveUsers','Scheduler - UMS_DeactiveUser');
    WHEN OTHERS THEN 
        INS_UMS_ERRORLOG(SQLCODE||' : '||SUBSTR(SQLERRM, 1, 200),null,'DeactiveUsers',null,null,null,'DB : DeactiveUsers','Scheduler - UMS_DeactiveUser');
END DeactiveUsers;

方案2：逐行循环处理（适用于需额外业务逻辑的场景） #

如果需要逐行处理用户（比如添加额外校验或日志），可使用显式游标循环：

PROCEDURE DeactiveUsers (
      P_DeactiveUsers_OUT      OUT SYS_REFCURSOR ) AS
    -- 定义显式游标
    CURSOR c_inactive_users IS
        SELECT username, MAX(TRANSACTION_DATE) AS last_login_date 
        FROM r4g_application_activity_log
        GROUP BY username
        HAVING MAX(TRANSACTION_DATE) <= SYSDATE - 90
        ORDER BY last_login_date DESC;
    v_user c_inactive_users%ROWTYPE;
BEGIN
    -- 打开输出游标返回结果
    OPEN P_DeactiveUsers_OUT FOR SELECT * FROM c_inactive_users;

    -- 逐行循环更新用户状态
    OPEN c_inactive_users;
    LOOP
        FETCH c_inactive_users INTO v_user;
        EXIT WHEN c_inactive_users%NOTFOUND;
        
        UPDATE r4g_application_activity_log
        SET ISACTIVE = 1
        WHERE username = v_user.username;
    END LOOP;
    CLOSE c_inactive_users;

EXCEPTION 
    WHEN NO_DATA_FOUND THEN 
        INS_UMS_ERRORLOG(SQLCODE||' : '||SUBSTR(SQLERRM, 1, 200),null,'DeactiveUsers',null,null,null,'DB : DeactiveUsers','Scheduler - UMS_DeactiveUser');
    WHEN OTHERS THEN 
        INS_UMS_ERRORLOG(SQLCODE||' : '||SUBSTR(SQLERRM, 1, 200),null,'DeactiveUsers',null,null,null,'DB : DeactiveUsers','Scheduler - UMS_DeactiveUser');
END DeactiveUsers;

修正说明 #

• 拆分了游标输出与更新逻辑，避免语法冲突；
• 方案1使用批量更新替代逐行循环，大幅提升执行效率；
• 优化查询结构，用HAVING子句替代嵌套查询，简化逻辑；
• 正确引用变量/游标数据，避免无效引用错误。

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