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