使用求和功能批量收集

Question

我正在尝试在Oracle数据库中使用Bulk all和Forall：

过程的原始代码如下：

IF NVL(v_mc,0) != 0 THEN
FOR rec IN
(SELECT a.testid,
SUM(pct * NVL(cap,0))/v_mc lead1
BULK COLLECT INTO testids1, testids2
FROM testtable a
WHERE a.id      = n_id
AND a.type      =n_type
GROUP BY a.testid;
)
LOOP
UPDATE testtable
SET LEAD1    =ROUND(testids2(i),2)
WHERE tid  = n_id
AND type  = n_type
AND testid   =testids1(i);
END LOOP;
END IF;

所以在选择语句中，我在这里使用了 Sum 函数和别名。

代码，我已经编写了使用批量收集和Forall的代码如下：

PROCEDURE test
IS
TYPE test1Tab IS TABLE OF sh_rpt_temp_peer_wip.test1%TYPE;
TYPE test2Tab IS TABLE OF testtable.lead1%TYPE;
testids1 testidTab; --Error 1 and Error 2
testids2 LeadTab;
BEGIN
IF NVL(v_mc,0) != 0 THEN
SELECT testid,
SUM(pct * NVL(cap,0))/v_mc lead1
BULK COLLECT INTO testids1, testids2
FROM testtable a               --Error 3
WHERE a.id      = n_id
AND a.type      =n_type
GROUP BY a.testid ORDER BY a.testid;
FORALL i IN testids1.FIRST..testids1.LAST
UPDATE testtable
SET LEAD1    =ROUND(testids2(i),2)
WHERE tid  = n_id  --Error 3
AND type  = n_type
AND testid   =testids1(i);
END IF;
END;

但是当我编译程序时，我遇到了多个错误。我对PL/SQL非常陌生。请让我知道我是否可以将计算值作为批量收集中的列检索？ 我在程序上遇到以下错误：

• 错误 1( PL/SQL：项目被忽略
• 错误 2( 必须声明组件"LEAD">
• 错误 3( 表达式的类型错误

请让我知道这里出了什么问题

谢谢

Answer 1

由于我确定您引用的集合类型不在过程中的范围内，因此您可能已全局声明。我修改了您的代码，请尝试一次，希望它对您有用。

PROCEDURE test
IS
TYPE test1Tab IS TABLE OF testtable.testid%TYPE;
TYPE test2Tab IS TABLE OF number;
testids1 test1Tab; //Error 1 and Error 2
testids2 test2Tab;
BEGIN
IF NVL(v_mc,0) != 0 THEN
SELECT testid,
SUM(pct * NVL(cap,0))/v_mc lead
BULK COLLECT INTO testids1, testids2
FROM testtable a               //Error 3
WHERE a.id      = n_id
AND a.type      =n_type
GROUP BY a.testid ORDER BY a.testid;
FORALL i IN testids1.FIRST..testids1.LAST
UPDATE testtable
SET LEAD    = ROUND(testids2(i),2)
WHERE tid  = n_id   //Error 3
AND type  = n_type
AND testid   = testids1(i);
END IF;
END;