Oracle存储过程为request_details表相同e_num记录生成行号列方案

原有存储过程执行报错、无法实现需求的核心原因有2点：

• SQL语法错误：select s*, rownum from request_details写法非法，SQL中不存在别名s
• 逻辑错误：直接使用rownum获取的是全局行号，无法实现按e_num分组后的组内递增序列需求

Oracle针对分组内排序递增的需求，直接使用窗口函数ROW_NUMBER()即可实现，同时如果需要获取同条件下新增记录的顺延行号，统计现有符合条件的记录数+1即可。

1. 基础查询实现（带组内行号） #

直接执行以下SQL即可获取符合过滤条件、且按e_num分组生成1开始递增行号的结果：

SELECT 
  req_id,
  e_num,
  e_name,
  e_env,
  e_member,
  ROW_NUMBER() OVER(PARTITION BY e_num ORDER BY req_id) AS row_count
FROM request_details
WHERE e_env = 'SIT' -- 对应输入参数iv_env
  AND e_member = 'SAP1' -- 对应输入参数iv_team
  AND e_num = 1; -- 对应输入参数iv_enum

说明：按主键req_id排序保证行号稳定，新增记录req_id更大，行号会自动顺延。

2. 完整存储过程实现 #

create or replace procedure sp_request_details(
  iv_env IN varchar2,
  iv_team IN varchar2,
  iv_enum IN number,
  ov_err_msg OUT varchar2,
  ov_next_row_num OUT number, -- 输出：同条件下新增记录对应的顺延行号
  cur_result OUT sys_refcursor -- 输出：带组内行号的查询结果集
)
AS
BEGIN
  ov_err_msg := '';
  -- 计算新增记录的顺延行号
  SELECT COUNT(1) + 1 INTO ov_next_row_num
  FROM request_details
  WHERE e_env = iv_env
    AND e_member = iv_team
    AND e_num = iv_enum;

  -- 返回带行号的结果集
  OPEN cur_result FOR
  SELECT 
    req_id,
    e_num,
    e_name,
    e_env,
    e_member,
    ROW_NUMBER() OVER(PARTITION BY e_num ORDER BY req_id) AS row_count
  FROM request_details
  WHERE e_env = iv_env
    AND e_member = iv_team
    AND e_num = iv_enum;

EXCEPTION
  WHEN OTHERS THEN
    ov_err_msg := '执行错误：' || SQLERRM;
END sp_request_details;

你提供的测试数据传入参数iv_env='SIT'、iv_team='SAP1'、iv_enum=1调用存储过程：

• 返回的结果集中row_count列值为1、2、3、4
• ov_next_row_num输出值为5，正好对应新增1条记录后的行号
  插入新测试数据insert into request_details values(5,1,'A','SIT','SAP1');后再次调用，row_count会自动更新为1到5，ov_next_row_num输出为6，完全满足顺延需求。

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