有这样一个过程,请教如何进行一下优化:
CURSOR CUR1 IS SELECT * FROM CARD_CCD_CLAIM_TYPE;
myRow TABLE2%ROWTYPE;
BEGIN OPEN CUR1; LOOP
Fetch CUR1 into myRow;
EXIT WHEN CUR1%NOTFOUND;
UPDATE TABLE1 SET ROW=myRow
WHERE KEY = myRow.KEY;
IF sql%rowcount = 0 THEN
INSERT INTO TABLE1 VALUES myRow;
END IF; IF MOD (icount, 10000) = 0
THEN
COMMIT;
END IF; END LOOP;
CLOSE CUR1;
COMMIT;
END;
CURSOR CUR1 IS SELECT * FROM CARD_CCD_CLAIM_TYPE;
myRow TABLE2%ROWTYPE;
BEGIN OPEN CUR1; LOOP
Fetch CUR1 into myRow;
EXIT WHEN CUR1%NOTFOUND;
UPDATE TABLE1 SET ROW=myRow
WHERE KEY = myRow.KEY;
IF sql%rowcount = 0 THEN
INSERT INTO TABLE1 VALUES myRow;
END IF; IF MOD (icount, 10000) = 0
THEN
COMMIT;
END IF; END LOOP;
CLOSE CUR1;
COMMIT;
END;
merge into
...
when mathed then
else
end
using tj_test tt
on (tt1.id=tt.id)
when matched then
update set
tt1.name=tt.name,
tt1.age=tt.age
when not matched then
insert values(
tt.id,
tt.name,
tt.age) --example
表table1,table2的结构是完全一样的,只是表名不一样而已,其中key是索引
一定要反回所有的吗?KEY; 如果是主键的话,完全可以select KEY from ....这样可以快速扫描索引了.cost少得多.然后走key到key的update