Oracle存储过程更新所有记录ID值相同，需实现反向序号赋值

原存储过程的问题 #

1. 变量未声明：v_sql未在存储过程中声明，编译会报错（若实际运行时临时声明，仍存在逻辑错误）
2. 循环更新逻辑错误：每次循环都会将test_main中所有行的id设置为固定值(select max(rownum) from test_staging) - 1（即4-1=3），循环四次后所有行的id仍为3，完全无法实现反向赋值的需求。

正确实现方案 #

无需先插入再更新，直接在插入时计算反向id，既高效又避免逻辑错误。核心思路是利用rownum获取记录的行号，结合总记录数计算反向ID：

create or replace procedure p_test
as
begin
    -- 清空目标表（无需动态SQL，直接执行）
    TRUNCATE TABLE test_main;

    -- 插入时直接计算反向ID
    insert into test_main(id, value)
    select 
        (select count(*) from test_staging) - rownum + 1 as id,
        value
    from test_staging;

    -- 按需提交事务
    commit;
end;
/

逻辑说明 #

• (select count(*) from test_staging)获取源表总记录数（此处为4）
• rownum会按源表的查询顺序（即插入顺序）为每条记录分配行号（A→1，B→2，C→3，D→4）
• 通过总记录数 - rownum + 1计算反向ID：
  • A: 4-1+1=4
  • B:4-2+1=3
  • C:4-3+1=2
  • D:4-4+1=1

执行该存储过程后，test_main表数据将完全符合预期：

+----+-------+
| id | value |
+----+-------+
|  4 | A     |
|  3 | B     |
|  2 | C     |
|  1 | D     |
+----+-------+

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