Oracle 批量收集错误 PLS-00201

Question

我的以下代码没有按预期工作。我收到以下错误：

Error at line 1
ORA-06550: line 20, column 18:
PLS-00201: identifier 'I' must be declared
ORA-06550: line 20, column 9:
PL/SQL: Statement ignored

我的代码：

DECLARE
process_limit  CONSTANT  SIMPLE_INTEGER := 500;
CURSOR c1 IS 
SELECT * FROM cdtx cx
WHERE EXISTS (SELECT 1 FROM clmx c
WHERE c.cid = cx.cid);
TYPE t_c1 IS TABLE OF c1%ROWTYPE;
v_c1 t_c1;
BEGIN
OPEN c1;
LOOP
FETCH c1 BULK COLLECT INTO v_c1 LIMIT process_limit;
EXIT WHEN v_c1.COUNT = 0;
BEGIN
FORALL i IN 1..v_c1.COUNT
INSERT INTO cdx
(cid,
oind,
tind,
ot_date)
VALUES (v_c1(i).cid,
v_c1(i).new_oind,
v_c1(i).new_tind,
v_c1(i).ot_date);
COMMIT;
IF  v_c1(i).new_t_amt IS NOT NULL 
AND v_c1(i).new_t_date IS NOT NULL
THEN
INSERT INTO ctx
(cid,
t_amt,
t_date,
t_id)
VALUES
(v_c1(i).cid,
v_c1(i).new_t_amt,
v_c1(i).new_t_date,
'1');
COMMIT;
END IF;
END;
END LOOP;
END;

光标返回接近 10k 条记录。我最初有没有oracleBULK COLLECT功能的工作代码，但我认为BULK COLLECT会更快。我已经在第 20 行的IF条件下尝试了几种变体，但没有成功。我需要评估列new_t_amt和new_t_date并检查它们是否NOT NULL。只有NOT NULL它们才能发生INSERT。感谢您的帮助。

Answer 1

如果您使用的是游标 for 循环，那么您可能会看到通过移动到批量收集的改进，请记住 Thorsten 在您的问题评论中提到的警告。

但是，您可以在单个插入语句中轻松执行此操作，假设您没有遇到任何限制(我希望从 ctx.cid 到 cdx.cid 没有外键)，使用多表插入如下：

INSERT ALL
WHEN new_t_amt IS NOT NULL
AND new_t_date IS NOT NULL THEN
INTO ctx (cid, t_amt, t_date, t_id) VALUES (cid, new_t_amt, new_t_date, '1')
WHEN 1 = 1 THEN INTO cdx (cid, oind, tind, ot_date) VALUES (cid, new_oind, new_tind, ot_date)
SELECT *
FROM   cdtx cx
WHERE  EXISTS (SELECT 1
FROM   clmx c
WHERE  c.cid = cx.cid);

Answer 2

PL/SQL是一种直接安装在DBMS中的编程语言。但是，PL/SQL引擎仍然必须与SQL引擎"对话"才能执行SQL语句。因此，直接运行 SQL 语句总是比调用 PL/SQL 函数更快。出于这个原因，Boneist建议使用多表插入，应该比你的方法更快。

在极少数情况下，使用单个刀片的经典方法可能会更快。这尤其适用于覆盖索引：

CREATE INDEX idx_for_cdx ON cdtx(cid, new_oind, new_tind, ot_date);
CREATE INDEX idx_for_ctx ON cdtx(cid, new_t_amt, new_t_date);

第二个索引中的列顺序可能很重要，因此您可以添加

CREATE INDEX idx_for_ctx2 ON cdtx(new_t_amt, new_t_date, cid);

并查看使用了哪个索引。

INSERT INTO cdx (cid, oind, tind, ot_date) 
SELECT cid, new_oind, new_tind, ot_date
FROM cdtx
WHERE cid IN (SELECT cid FROM clmx);
INSERT INTO ctx (cid, t_amt, t_date, t_id) 
SELECT cid, new_t_amt, new_t_date, '1'
FROM cdtx
WHERE cid IN (SELECT cid FROM clmx)
AND new_t_amt IS NOT NULL 
AND new_t_date IS NOT NULL;
COMMIT;

如果您愿意，也可以将其放在匿名PL/SQL块中：

BEGIN
INSERT INTO cdx ...
INSERT INTO ctx ...
COMMIT;
END;

唯一的区别是，从计算机到数据库计算机的往返次数更少，因为这些往返次数将移动到PL/SQL引擎和SQL引擎之间的数据库计算机内部。