在存储过程中使用WITH子句

499

阿勒马克

我想使用该WITH子句填充存储过程中的表。我正在阅读说，子句在会话处于活动状态时会将数据保留在内存中。

有一个表包含我需要更新的信息，称为sprlink，但是该表具有数百万条记录。目前，我的更新如下，但速度很慢：

open act_rpt_list_cdo;
loop
  fetch act_rpt_list_cdo bulk collect into v_act_rpt_list_cdo limit 100;
  for i in 1 .. v_act_rpt_list_cdo.count loop
    update RPT_LIST_CDO set name_cdo=(select trim(sln.linkvalue) from sprlink sln where sln.objectid=v_act_rpt_list_cdo(i).id  and SLN.LINKID = 164 and logidto=0),
                            date_cdo=(select trim(sln.linkvalue) from sprlink sln where sln.objectid=v_act_rpt_list_cdo(i).id  and SLN.LINKID = 165 and logidto=0),
                            cod_cdo=(select trim(sln.linkvalue) from sprlink sln where sln.objectid=v_act_rpt_list_cdo(i).id  and SLN.LINKID = 166 and logidto=0),
                            log_cdo=(select trim(sln.linkvalue) from sprlink sln where sln.objectid=v_act_rpt_list_cdo(i).id  and SLN.LINKID = 167 and logidto=0),
                            data_cdo=(select trim(sln.linkvalue) from sprlink sln where sln.objectid=v_act_rpt_list_cdo(i).id  and SLN.LINKID = 168 and logidto=0)

    where rowid=v_act_rpt_list_cdo(i).ri;
  end loop;
  exit when act_rpt_list_cdo%notfound;
end loop;
close act_rpt_list_cdo

我想在这种情况下使用该子句来限制记录，但是我不知道如何在过程中使用它：

WITH tt_sprlink AS (select sln.objectid as objectid , sln.linkid as linkid, trim(sln.linkvalue) linkvalue
   from sprlinks sln join RPT_LIST_CDO rpt on (sln.objectid=rpt.id and sln.logidto=0)
  where sln.linkid in (164,165,166,167,168))
select *
from tt_sprlink

由于tt_srplink只能以必要的记录进行更新，因此能够以以下方式进行操作。

act_rpt_list_cdo
loop
  fetch act_rpt_list_cdo bulk collect into v_act_rpt_list_cdo limit 100;
  for i in 1 .. v_act_rpt_list_cdo.count loop
    update RPT_LIST_CDO set name_cdo=(select trim(sln.linkvalue) from tt_sprlink sln where sln.objectid=v_act_rpt_list_cdo(i).id  and SLN.LINKID = 164),
                            date_cdo=(select trim(sln.linkvalue) from tt_sprlink sln where sln.objectid=v_act_rpt_list_cdo(i).id  and SLN.LINKID = 165),
                            cod_cdo=(select trim(sln.linkvalue) from tt_sprlink sln where sln.objectid=v_act_rpt_list_cdo(i).id  and SLN.LINKID = 166),
                            log_cdo=(select trim(sln.linkvalue) from tt_sprlink sln where sln.objectid=v_act_rpt_list_cdo(i).id  and SLN.LINKID = 167),
                            data_cdo=(select trim(sln.linkvalue) from tt_sprlink sln where sln.objectid=v_act_rpt_list_cdo(i).id  and SLN.LINKID = 168)

    where rowid=v_act_rpt_list_cdo(i).ri;
  end loop;
  exit when act_rpt_list_cdo%notfound;
end loop;
close act_rpt_list_cdo

有什么建议或想法吗？

马修·麦克佩克（Matthew McPeak）

首先：语句WITH“会话处于活动状态时保留内存中的数据”的声明为false。一个WITH cursor_var AS (SELECT ...) LOOP结构只是一个游标循环-一样的OPEN..FETCH..CLOSE循环，你已经有了。就是说：关于WITH游标循环，有很多令人喜欢的地方，但是神奇的数据缓存并不是其中之一。

第二：除非您省略了某些内容，否则在这种情况下似乎不需要PL / SQL。正如您所拥有的，逐行游标处理将比单个SQL语句慢得多。（您的情况更糟，因为您还要为每个更新执行5个标量子查询）。

这是您使用一条UPDATE语句即可实现目标的方式：

UPDATE rpt_list_cdo u
SET ( name_cdo, date_cdo, cod_cdo, log_cdo, data_cdo ) = 
(
SELECT MAX(DECODE(sln.linkid,164,trim(sln.linkvalue),null)) name_cdo,
       MAX(DECODE(sln.linkid,165,trim(sln.linkvalue),null)) date_cdo,
       MAX(DECODE(sln.linkid,166,trim(sln.linkvalue),null)) cod_cdo,
       MAX(DECODE(sln.linkid,167,trim(sln.linkvalue),null)) log_cdo,
       MAX(DECODE(sln.linkid,168,trim(sln.linkvalue),null)) data_cdo
FROM   sprlinks sln
WHERE  sln.objectid = u.id
AND    sln.linkid in (164,165,166,167,168))
WHERE 1=1
AND -- whatever other conditions you have in your act_rpt_list_cdo cursor

MERGE也可以，但是UPDATE在这种情况下，我更喜欢，因为在act_rpt_list_cdo光标中添加其他条件会更容易。

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。