Oracle存储过程表锁机制及sys_refcursor直接输出结果集问题咨询

1. 存储过程是否会对其体内涉及的表加锁？ #

不同操作的锁行为不同：

• TRUNCATE TABLE：属于DDL操作，执行时会给AUDIT_TABLE加排他表锁（X锁），执行期间其他会话无法对该表执行DML、DDL操作，锁在TRUNCATE完成后立即释放。
• INSERT INTO AUDIT_TABLE：属于DML操作，会对插入的行加行级排他锁（TX锁），同时表级加意向排他锁（IX锁）。这种锁不会阻塞其他会话对该表的普通查询，但会阻塞其他会话对相同行的修改。
• 对TABLE_NAME1、TABLE_NAME2的SELECT查询：默认采用一致性读模式，不会加锁，完全不影响其他会话的读写操作。

2. 如何直接将SELECT结果输出至sys_refcursor，无需中间表？ #

可以把原循环内的查询逻辑合并为一个完整的SELECT语句，直接绑定到游标返回，彻底抛弃AUDIT_TABLE。以下是优化后的存储过程：

优化版代码（使用WITH子句简化重复逻辑） #

create or replace PROCEDURE PROCEDURE_NAME(var1 in VARCHAR2, var2 in NUMBER, prc out SYS_REFCURSOR)
IS 
begin
  if (var2 = 2) then
    open prc for
      with base_data as (
        -- 预查询基础数据，按col3分区排序生成行号
        select ROW_NUMBER() OVER(partition by col3 order by row_id) AS num_row,
               col1, v2, col2, col3, col4
        from TABLE_NAME1
        where v1 = var1 
          and v2 = var2
          and col3 in (select v3 from TABLE_NAME1 where v1 = var1 and v2 = var2)
      )
      select a.col1, a.col2, a.col3, a.col4
      from base_data a
      -- 关联同分区下的下一行数据
      inner join base_data b on b.col3 = a.col3 and b.num_row = a.num_row + 1
      inner join TABLE_NAME2 c on a.v2 = c.v2;
  else
    -- var2不等于2时返回空结果集
    open prc for select * from dual where 1=0;
  end if;
end;

逻辑说明 #

原代码通过循环遍历每个v3值执行查询，优化后用partition by col3让同一col3的行独立排序，再通过b.col3 = a.col3关联同一分组的下一行，等价于原循环的逻辑，同时避免了中间表的多用户冲突问题。

3. 存储过程调用是否会锁定涉及表，阻止其他用户访问或修改？ #

分场景判断：

• 原版本（含TRUNCATE/INSERT）：
  • TRUNCATE执行瞬间会加排他表锁，短暂阻塞其他会话对AUDIT_TABLE的DML/DDL；
  • INSERT仅锁定插入的行，不影响其他用户查询或修改AUDIT_TABLE的其他行；
  • 对TABLE_NAME1、TABLE_NAME2的查询无锁，不影响其他用户读写。
• 优化版本（仅SELECT）：
  全程采用一致性读模式，无任何锁，完全不影响其他用户对涉及表的访问和修改，同时彻底解决了多用户共享中间表的数据冲突问题。

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