将 BULK COLLECT 与 rownum 一起使用

Question

下面的查询会批量选择，然后在循环中更新记录。我想在这里使用 BULK COLLECT INTO 和 FORALL 构造来提高性能。但查询使用 rownum 来更新循环中的列。无论如何，我也可以在获得 rownum 的同时使用批量收集吗？

FOR rec IN
(SELECT rownum rn,
b.*
FROM
(SELECT *
FROM temp_final a
WHERE reid  = 1
AND retype  = 9
AND sid         = 'r123'
AND pid = 2191
ORDER BY a.horder DESC nulls last,
sname                         ,
rowgroup                           ,
dpct DESC nulls last       ,
name
) b
)
LOOP
UPDATE temp_final
SET horder=rec.rn
WHERE reid  = 1
AND retype  = 9
AND sid         = 'r123'
AND pid = 2191
AND mid   =rec.mid;
END LOOP;

谢谢

Answer 1

您不需要批量收集，您的 pl/sql 更新尝试执行的操作可以重写为单个MERGE INTO语句，这将比使用FORALL高效得多。如果你仍然坚持使用 forall，你可以将这个 MERGE 转换为一个 forall 块。

MERGE INTO temp_final tgt USING (
SELECT rowid,
ROW_NUMBER() OVER(
ORDER BY
horder DESC NULLS LAST,sname,rowgroup,dpct DESC NULLS LAST,name
) rn
FROM
temp_final
WHERE
reid = 1
AND retype = 9
AND sid = 'r123'
AND pid = 2191
)
src ON ( tgt.rowid = src.rowid )
WHEN MATCHED THEN UPDATE SET tgt.horder = src.rn;

Answer 2

您可以获取 rowid 以及初始选择，将批次转储到数组中，然后在您的 FORALL 中使用它，例如

declare
l_num_list sys.odcinumberlist := sys.odcinumberlist();
l_char_list sys.odcivarchar2list := sys.odcivarchar2list();
begin  
SELECT rownum rn,rowidtochar(rid)
bulk collect into l_num_list, l_char_list
FROM
(SELECT a.*, rowid rid
FROM temp_final a
WHERE reid  = 1
AND retype  = 9
AND sid         = 'r123'
AND pid = 2191
ORDER BY a.horder DESC nulls last,
sname                         ,
rowgroup                           ,
dpct DESC nulls last       ,
name
) b;
forall i in 1 .. l_num_list.count
UPDATE temp_final
SET horder=l_num_list(i)
WHERE rowid = chartorowid(l_char_list(i));
end;
/