欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-06-16 13:24:16
原创文章,转载请注明出处,谢谢合作。日前,笔者在优化数据库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 ,...

原创文章,转载请注明出处,谢谢合作。
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