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