Oracle实战优化：INSERT ALL关键字的应用

原创文章，转载请注明出处，谢谢合作。
https://blog.csdn.net/DarianMograine/article/details/108562035

日前，笔者在优化数据库PROCEDURE的过程中遇到这样一个场景：

DECLARE
  l_header_id NUMBER;

  CURSOR cur_headers IS
    SELECT col1
          ,col2
    FROM   table_a
    WHERE  1 = 1
    AND    xxxx = xxxx;

  CURSOR cur_lines(cp_col1 VARCHAR2
                  ,cp_col2 VARCHAR2) IS
    SELECT b.*
    FROM   table_b
    WHERE  col1 = cp_col1
    AND    col2 = cp_col2;
BEGIN
  FOR rec IN cur_headers LOOP
    l_header_id := xxx_headers_s.nextval;
  
    INSERT INTO header_insert
      (header_id, col1, col2)
    VALUES
      (l_header_id, rec.col1, rec.col2);
  
    FOR line IN cur_lines(rec.col1, rec.col2) LOOP
      INSERT INTO line_insert
        (header_id, line_id, col3, col4, col5, col6)
      VALUES
        (l_header_id
        ,xxx_lines_s.nextval
        ,line.col3
        ,line.col4
        ,line.col5
        ,line.col6);
    END LOOP;
  END LOOP;
END;

这是一个非常典型的循环遍历以插入数据到数据库表的过程，
在table_a和table_b的数据量暴增时程序的运行时间会急剧上升，
而针对这个问题，我们可以通过INSERT ALL来解决。

笔者在这里希望给大家一个建议，如果遇到这样的场景，请按下面的方式来写这段SQL，一段解决所有问题，让你的代码变得更优雅。

针对上面的代码块，我们可以用下面的代码来代替，利用分析函数ROW_NUMBER来控制对头表数据的插入：

DECLARE
  FUNCTION get_hdr_seq RETURN NUMBER IS
    RETURN xxx_headers_s.nextval;
  END get_hdr_seq;

  FUNCTION get_line_seq RETURN NUMBER IS
    RETURN xxx_lines_s.nextval;
  END get_line_seq;
BEGIN
  INSERT ALL --
  WHEN rn = 1 THEN --当是每个HEADER_ID的第一行时插入头表一条数据
  INTO header_insert
    (header_id, col1, col2)
  VALUES
    (header_id, col1, col2) --
  WHEN 1 = 1 THEN --
  INTO line_insert
    (header_id, line_id, col3, col4, col5, col6)
  VALUES
    (header_id, line_id, col3, col4, col5, col6)
    SELECT h.header_id
          ,h.col1
          ,h.col2
          ,l.line_id
          ,l.col3
          ,l.col4
          ,l.col5
          ,l.col6
          ,row_number() over(PARTITION BY h.col1, h.col2 ORDER BY l.col3, l.col4) rn
    FROM   (SELECT get_hdr_seq header_id
                  ,col1
                  ,col2
            FROM   table_a h
            WHERE  xxxx = xxxx) h
          ,(SELECT get_line_seq line_id
                  ,col1
                  ,col2
                  ,col3
                  ,col4
                  ,col5
                  ,col6
            FROM   table_b l) l
    WHERE  h.col1 = l.col1
    AND    h.col2 = l.col2;
END;

以上，希望对大家有帮助，感兴趣的同学可以一试。

原创文章，转载请注明出处，谢谢合作。
https://blog.csdn.net/DarianMograine/article/details/108562035

本文地址：https://blog.csdn.net/DarianMograine/article/details/108562035