oracle update 效率低 怎么办呢

Question

create or replace procedure P_CRM_UPDATE_VIP_CARD_STATE AS

CURSOR CC IS
SELECT A.SERIAL_NUMBER,
A.REMOVE_TAG,
A.VIP_CARD_END_DATE
FROM TF_F_CUST_VIP A
WHERE A.REMOVE_TAG = '0'
AND A.VIP_CARD_END_DATE < SYSDATE;
CCREC CC%ROWTYPE;
BEGIN
OPEN CC ;
LOOP
FETCH CC
INTO CCREC;
EXIT WHEN CC%NOTFOUND;

UPDATE TF_F_CUST_VIP
SET VIP_CARD_STATE = '4'
WHERE REMOVE_TAG = CCREC.REMOVE_TAG
AND VIP_CARD_END_DATE = CCREC.VIP_CARD_END_DATE;

UPDATE TF_R_CARD_VIP
SET CARD_STATE_CODE = '1',
SERIAL_NUMBER = '',
end_date = to_date('2050-12-31 23:59:59',
'yyyy-mm-dd hh24:mi:ss')
WHERE RES_NO = CCREC.SERIAL_NUMBER;
COMMIT;
END LOOP;
CLOSE CC;

end P_CRM_UPDATE_VIP_CARD_STATE;
这是我写的存储过程 ，哪位大侠给看看吧，怎么能提高一下效率呢？

Answer 1

我大概看了一下你的procedure，无非是做了两件事：
1.根据TF_F_CUST_VIP中的某些数据去update TF_R_CARD_VIP中的数据(这个可以用Merge语法)
2.update TF_F_CUST_VIP本table中的某些数据(这个就直接update)
其实这些动作没必要用游标去循环了，用游标全表循环自然效率低，可以参考下面修改的：

[code="sql"]
create or replace procedure P_CRM_UPDATE_VIP_CARD_STATE AS
BEGIN

Merge into TF_R_CARD_VIP t
using (SELECT A.SERIAL_NUMBER, A.REMOVE_TAG, A.VIP_CARD_END_DATE
FROM TF_F_CUST_VIP A
WHERE A.REMOVE_TAG = '0'
AND A.VIP_CARD_END_DATE < SYSDATE) aa
on (aa.SERIAL_NUMBER = t.RES_NO)
When matched then
update
SET CARD_STATE_CODE = '1',
SERIAL_NUMBER = '',
end_date = to_date('2050-12-31 23:59:59',
'yyyy-mm-dd hh24:mi:ss');

UPDATE TF_F_CUST_VIP
SET VIP_CARD_STATE = '4'
WHERE A.REMOVE_TAG = '0'
AND A.VIP_CARD_END_DATE < SYSDATE;

COMMIT;

end P_CRM_UPDATE_VIP_CARD_STATE;
[/code]

手写，未测试..