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