有没有一种方法可以在oracle中更快地循环光标

Question

我需要从文件中向表中插入大量数据，但需要检查数据是否会插入或更新。

我的程序像这个一样工作

PROCEDURE INSERT_OR_UPDATE
IS
[...]
BEGIN
READ_FROM_FILE_AND_INSERT_ON_SUPPORT_TABLE;
DECLARE
CURSOR C1 IS
SELECT ROWID ROW_ID, A.* FROM SUPPORT_TABLE A;
BEGIN
FOR RECORDS IN C1 LOOP
BEGIN
SELECT COLUMN1, COLUMN2, COLUMN3, COLUMN4, COLUMN5
INTO   vVARIABLE1, vVARIABLE2, vVARIABLE3, vVARIABLE4, vVARIABLE5
FROM TABLE1 A, TABLE2 B
WHERE A.COLUMN1 = B.COLUMN1
AND   A.COLUMN2 = B.COLUMN2
AND   A.COLUMN3 = B.COLUMN3
AND   A.COLUMN4 = B.COLUMN4
AND   A.COLUMN1 = RECORDS.COLUMN1
AND   A.COLUMN3 >= TO_NUMBER(TO_CHAR(SYSDATE, 'YYYYMMDD'));
IF
RECORDS.COLUMN1  = vVARIABLE1
AND  W1.COLUMN2  = vVARIABLE2
AND  W1.COLUMN3  = vVARIABLE3
AND  W1.COLUMN4 >= TO_NUMBER(TO_CHAR(SYSDATE, 'YYYYMMDD')
THEN
UPDATE SUPPORT_TABLE
SET STATUS_MESSAGE = 'TO UPDATE',
STATUS = 'PROCESSED',
WHERE ROWID = RECORDS.ROW_ID;
EXCEPTION WHEN NO_DATA_FOUND THEN
UPDATE SUPPORT_TABLE
SET STATUS_MESSAGE = 'TO INSERT',
STATUS = 'PROCESSED'
WHERE ROWID = RECORDS.ROW_ID;
END LOOP;
END;
END;

问题是，代码的性能非常糟糕。运行4小时后，它只处理了0.05%，有办法优化流程吗？。

Obs.：该文件有654.000行。

Answer 1

您似乎想要一个MERGE语句：

CREATE PROCEDURE INSERT_OR_UPDATE
IS
W1 TABLE1%ROWTYPE; -- Define W1 as something
BEGIN
READ_FROM_FILE_AND_INSERT_ON_SUPPORT_TABLE;
MERGE INTO support_table dst
USING (
SELECT s.ROWID AS rid,
a.COLUMN1,
a.COLUMN2,
a.COLUMN3,
a.COLUMN4,
COLUMN5
FROM   support_table s
LEFT OUTER JOIN (
TABLE1 A
INNER JOIN TABLE2 B
ON (   A.COLUMN1 = B.COLUMN1
AND A.COLUMN2 = B.COLUMN2
AND A.COLUMN3 = B.COLUMN3
AND A.COLUMN3 >= TO_NUMBER(TO_CHAR(SYSDATE, 'YYYYMMDD'))
AND A.COLUMN4 = B.COLUMN4
)
)
ON (A.COLUMN1 = s.COLUMN1)
) src
ON (src.rid = dst.ROWID)
WHEN MATCHED THEN
UPDATE
SET status_message = CASE
WHEN src.COLUMN1 IS NOT NULL
THEN 'TO UPDATE'
ELSE 'TO INSERT'
END,
status         = 'PROCESSED'
WHERE src.COLUMN1 IS NULL
OR    (   W1.COLUMN2  = src.COLUMN2
AND W1.COLUMN3  = src.COLUMN3
AND W1.COLUMN4 >= TO_NUMBER(TO_CHAR(SYSDATE, 'YYYYMMDD'))
);
END;
/

(注意：此代码未经测试，因为没有为您的样本数据提供CREATE TABLE或INSERT语句，也没有任何预期输出，因此不清楚您的预期行为。(

db<gt；小提琴这里

Answer 2

使用Merge，它很有效，可以满足您的需求：

MERGE INTO TABLE1 A
USING (SELECT *
FROM SUPPORT_TABLE) records
ON (
RECORDS.COLUMN1 = A.COLUMN1
)
WHEN MATCHED THEN UPDATE SET <A.COLUMN(S) = records.columns>
WHEN NOT MATCHED THEN INSERT INTO A VALUES records;

(这是伪的，因为您既没有指定目标表，也没有指定表描述(