Oracle游标循环更新数据案例 

 1 declare
 2       v_XTXMBH number;
 3       v_ZJZJZJRQ  varchar2(40);
 4       cursor c_job is
 5        SELECT XT.XTXMBH AS XTXMBH, QJ.ZJZJZJRQ AS ZJZJZJRQ
 6          FROM XTXMXX XT, QJGLXX_ZQL_MID QJ
 7         WHERE XT.XTXMBH = QJ.XTXMBH
 8           AND XT.XTXMCLRQ >= '20120630'
 9           AND (QJ.ZJQHZJRQ IS NULL OR QJ.ZJZJZJRQ IS NULL);
10       c_row c_job%rowtype;
11     begin
12       for c_row in c_job loop
13         v_XTXMBH:=c_row.XTXMBH;
14         v_ZJZJZJRQ:=c_row.ZJZJZJRQ;
15         IF (v_XTXMBH IS NOT NULL OR v_XTXMBH<>'') THEN
16           if (v_ZJZJZJRQ IS NULL OR v_ZJZJZJRQ='') then
17             --UPDATE ZJ
18            UPDATE QJGLXX_ZQL_MID T
19               SET T.ZJZJZJRQ = '20000101', T.ZJZJZJJE = 0, T.LJZJZJJE = 0
20             WHERE T.XTXMBH=v_XTXMBH;
21           else
22             --UPDATE QH
23             UPDATE QJGLXX_ZQL_MID T
24               SET T.ZJQHZJRQ = '20000101', T.ZJQHZJJE = 0, T.LJQHZJJE = 0
25             WHERE T.XTXMBH=v_XTXMBH;
26           end if;
27          END IF;
28       end loop; --循环结束
29       commit; --提交处理后数据
30     end;

 

posted @ 2021-04-23 17:12  FelixWang  阅读(413)  评论(0)    收藏  举报