为什么我在 Run 这一段 SQL Statement 的时候非常的慢? (table 资料量不大)
请前辈指教, 谢谢
DECLARE
CURSOR PRD_STAOLD_CURSOR IS
SELECT
TO_DATE(TO_CHAR(SYSDATE, 'MM/DD/YYYY'), 'MM/DD/YYYY') AS CHKDATE,
COMPANY,
INVOICEYEAR,
INVOICEMONTH,
BPL,
BIGCLASS,
SUMINVAMT,
SUMINVQTY,
SUMCOST
FROM
CHK_STA_OLD
ORDER BY
COMPANY, INVOICEYEAR, INVOICEMONTH;
BEGIN
FOR PRD_STAOLD_RECORD IN PRD_STAOLD_CURSOR LOOP
UPDATE CHK_STA_RSL
SET
OLDINVAMT = PRD_STAOLD_RECORD.SUMINVAMT,
OLDINVQTY = PRD_STAOLD_RECORD.SUMINVQTY,
OLDCOST = PRD_STAOLD_RECORD.SUMCOST
WHERE
TO_CHAR(CHKDATE, 'MM/DD/YYYY') = TO_CHAR(SYSDATE, 'MM/DD/YYYY')
AND
COMPANY = PRD_STAOLD_RECORD.COMPANY
AND
INVOICEYEAR = PRD_STAOLD_RECORD.INVOICEYEAR
AND
INVOICEMONTH = PRD_STAOLD_RECORD.INVOICEMONTH
AND
BPL = PRD_STAOLD_RECORD.BPL
AND
BIGCLASS = PRD_STAOLD_RECORD.BIGCLASS;
IF SQL%NOTFOUND THEN
INSERT INTO CHK_STA_RSL
(
CHKDATE, COMPANY, INVOICEYEAR,
INVOICEMONTH, BPL, BIGCLASS,
NEWINVAMT, NEWINVQTY, NEWCOST,
OLDINVAMT, OLDINVQTY, OLDCOST,
DIFFINVAMT, DIFFINVQTY, DIFFCOST
)
VALUES
(
TO_DATE(TO_CHAR(SYSDATE, 'MM/DD/YYYY'), 'MM/DD/YYYY'),
PRD_STAOLD_RECORD.COMPANY,
PRD_STAOLD_RECORD.INVOICEYEAR,
PRD_STAOLD_RECORD.INVOICEMONTH,
PRD_STAOLD_RECORD.BPL,
PRD_STAOLD_RECORD.BIGCLASS,
0,
0,
0,
PRD_STAOLD_RECORD.SUMINVAMT,
PRD_STAOLD_RECORD.SUMINVQTY,
PRD_STAOLD_RECORD.SUMCOST,
0,
0,
0
);
END IF;
END LOOP;
END; |
