Oracle存储过程：如何引用触发器生成的前两次插入操作ID？

嘿，我来帮你捋清楚这个问题！你现在的核心需求是：在基于游标的存储过程里插入数据后，要引用触发器自动生成的Value和Calculation表的ID，同时还要实现按日汇总的计算。先给你两种靠谱的方案，再聊聊按日汇总的优化思路：

方案1：插入时直接捕获生成的ID（最推荐，无并发风险） #

不管你用的是Oracle、SQL Server还是其他主流数据库，都支持在插入语句里直接返回触发器生成的主键ID——这种方式比事后查询最新ID靠谱太多，不会因为其他会话同时插入数据而拿到错误的ID。

以Oracle为例（适配你的存储过程） #

create or replace PROCEDURE TEST_PROC IS 
  Cursor c1 is 
    SELECT SUM(v.value_tx) AS sum_of_values 
           , e.entity_id AS entity_id 
           , e.entity_name_tx AS entity_name 
           , e.ei_id_tx AS ei_id 
           , v.create_dt AS create_dt 
           , v.hr_num AS hr_num 
           , v.utc_offset 
           , v.data_date 
           , v.hr_utc 
           , v.hr 
           , ff.form_field_id
           , s.survey_respondent_id
           , v.data_code 
           , 'N' AS processed 
    FROM value v 
    join submission_value sv ON v.value_id = sv.value_id 
    join form_field ff ON sv.form_field_id = ff.form_field_id 
    join submission s ON sv.submission_id = s.submission_id 
    join survey_respondent sr ON s.survey_respondent_id = sr.survey_respondent_id 
    join entity e ON sr.entity_id = e.entity_id 
    WHERE ei_id_tx IN ('ABC', 'ZYX', 'ADA', 'AZL', 'BAN', 'CAB') 
      AND ff.form_field_id IN ('55', '77') 
    GROUP BY e.entity_id, e.entity_name_tx, e.ei_id_tx, v.create_dt, v.hr_num, v.utc_offset, v.data_date, v.hr_utc, v.hr, v.data_code; 
  l_var c1%ROWTYPE;
  l_new_value_id NUMBER; -- 存储刚插入Value表的ID
BEGIN 
  OPEN c1; 
  LOOP 
    FETCH c1 into l_var; 
    EXIT WHEN c1%NOTFOUND; 
    
    -- 插入Value表并通过RETURNING子句捕获触发器生成的ID
    INSERT INTO value (sum_of_values, entity_id, entity_name_tx, ei_id_tx, create_dt, hr_num, utc_offset, data_date, hr_utc, hr, data_code, processed)
    VALUES (l_var.sum_of_values, l_var.entity_id, l_var.entity_name, l_var.ei_id, l_var.create_dt, l_var.hr_num, l_var.utc_offset, l_var.data_date, l_var.hr_utc, l_var.hr, l_var.data_code, l_var.processed)
    RETURNING value_id INTO l_new_value_id; -- 关键：直接拿触发器生成的ID
    
    -- 现在用这个ID关联Calculation表，实现按日汇总（存在则更新，不存在则插入）
    DECLARE
      l_existing_calc_id NUMBER;
    BEGIN
      -- 先查当天是否已有该实体+数据编码的汇总记录
      SELECT calculation_id INTO l_existing_calc_id
      FROM calculation
      WHERE entity_id = l_var.entity_id
        AND data_code = l_var.data_code
        AND calculation_date = TRUNC(l_var.data_date);
        
      -- 有就更新汇总值
      UPDATE calculation
      SET daily_sum = daily_sum + l_var.sum_of_values,
          last_updated_dt = SYSDATE
      WHERE calculation_id = l_existing_calc_id;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        -- 没有就插入新的汇总记录
        INSERT INTO calculation (value_id, daily_sum, calculation_date, entity_id, data_code)
        VALUES (l_new_value_id, l_var.sum_of_values, TRUNC(l_var.data_date), l_var.entity_id, l_var.data_code);
    END;
    
  END LOOP; 
  CLOSE c1;
END TEST_PROC;
/

如果是SQL Server，语法调整如下 #

CREATE PROCEDURE TEST_PROC
AS
BEGIN
  SET NOCOUNT ON;
  
  DECLARE @c1 CURSOR;
  DECLARE @sum_of_values DECIMAL(18,2), @entity_id INT, @entity_name_tx VARCHAR(100), @ei_id_tx VARCHAR(50),
          @create_dt DATETIME, @hr_num INT, @utc_offset INT, @data_date DATE, @hr_utc INT, @hr INT,
          @form_field_id VARCHAR(10), @survey_respondent_id INT, @data_code VARCHAR(50), @processed CHAR(1),
          @new_value_id INT;

  SET @c1 = CURSOR FOR
    SELECT SUM(v.value_tx) AS sum_of_values 
           , e.entity_id AS entity_id 
           , e.entity_name_tx AS entity_name 
           , e.ei_id_tx AS ei_id 
           , v.create_dt AS create_dt 
           , v.hr_num AS hr_num 
           , v.utc_offset 
           , v.data_date 
           , v.hr_utc 
           , v.hr 
           , ff.form_field_id
           , s.survey_respondent_id
           , v.data_code 
           , 'N' AS processed 
    FROM value v 
    join submission_value sv ON v.value_id = sv.value_id 
    join form_field ff ON sv.form_field_id = ff.form_field_id 
    join submission s ON sv.submission_id = s.submission_id 
    join survey_respondent sr ON s.survey_respondent_id = sr.survey_respondent_id 
    join entity e ON sr.entity_id = e.entity_id 
    WHERE ei_id_tx IN ('ABC', 'ZYX', 'ADA', 'AZL', 'BAN', 'CAB') 
      AND ff.form_field_id IN ('55', '77') 
    GROUP BY e.entity_id, e.entity_name_tx, e.ei_id_tx, v.create_dt, v.hr_num, v.utc_offset, v.data_date, v.hr_utc, v.hr, v.data_code;

  OPEN @c1;
  FETCH NEXT FROM @c1 INTO @sum_of_values, @entity_id, @entity_name_tx, @ei_id_tx, @create_dt, @hr_num, @utc_offset, @data_date, @hr_utc, @hr, @form_field_id, @survey_respondent_id, @data_code, @processed;

  WHILE @@FETCH_STATUS = 0
  BEGIN
    -- 插入Value表并通过OUTPUT子句获取ID
    INSERT INTO value (sum_of_values, entity_id, entity_name_tx, ei_id_tx, create_dt, hr_num, utc_offset, data_date, hr_utc, hr, data_code, processed)
    OUTPUT INSERTED.value_id INTO @new_value_id
    VALUES (@sum_of_values, @entity_id, @entity_name_tx, @ei_id_tx, @create_dt, @hr_num, @utc_offset, @data_date, @hr_utc, @hr, @data_code, @processed);

    -- 处理按日汇总：先查是否存在当天的汇总记录
    IF EXISTS (SELECT 1 FROM calculation WHERE entity_id = @entity_id AND data_code = @data_code AND calculation_date = CAST(@data_date AS DATE))
    BEGIN
      UPDATE calculation
      SET daily_sum = daily_sum + @sum_of_values,
          last_updated_dt = GETDATE()
      WHERE entity_id = @entity_id AND data_code = @data_code AND calculation_date = CAST(@data_date AS DATE);
    END
    ELSE
    BEGIN
      INSERT INTO calculation (value_id, daily_sum, calculation_date, entity_id, data_code)
      VALUES (@new_value_id, @sum_of_values, CAST(@data_date AS DATE), @entity_id, @data_code);
    END

    FETCH NEXT FROM @c1 INTO @sum_of_values, @entity_id, @entity_name_tx, @ei_id_tx, @create_dt, @hr_num, @utc_offset, @data_date, @hr_utc, @hr, @form_field_id, @survey_respondent_id, @data_code, @processed;
  END;

  CLOSE @c1;
  DEALLOCATE @c1;
END;
GO

方案2：通过查询获取历史ID（不推荐，有并发风险） #

如果你确实想用查询的方式获取ID，一定要加精准的过滤条件，绝对不能只查MAX(value_id)——因为并发场景下，其他会话的插入会让你拿到错误的ID。比如：

-- 插入Value表后，用唯一的业务组合条件查询ID
SELECT value_id INTO l_new_value_id
FROM value
WHERE entity_id = l_var.entity_id
  AND data_date = l_var.data_date
  AND data_code = l_var.data_code
  AND create_dt = l_var.create_dt; -- 用这些唯一标识过滤，确保拿到自己插入的那条记录

这种方式的问题是，如果你的表没有为这些过滤字段建立唯一索引，可能会查到多条记录，而且高并发下容易出错，所以优先用方案1。

关于按日汇总的额外建议 #

如果你的按日汇总数据量比较大，建议给calculation表的entity_id + data_code + calculation_date建立唯一索引——这样不仅能避免重复插入，还能加快查询和更新的速度。

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