Oracle遊標迴圈更新資料案例

標籤：style   blog   color   資料   for   re   c   div   

declare      v_XTXMBH number;      v_ZJZJZJRQ  varchar2(40);      cursor c_job is       SELECT XT.XTXMBH AS XTXMBH, QJ.ZJZJZJRQ AS ZJZJZJRQ         FROM XTXMXX XT, QJGLXX_ZQL_MID QJ        WHERE XT.XTXMBH = QJ.XTXMBH          AND XT.XTXMCLRQ >= ‘20120630‘          AND (QJ.ZJQHZJRQ IS NULL OR QJ.ZJZJZJRQ IS NULL);      c_row c_job%rowtype;    begin      for c_row in c_job loop        v_XTXMBH:=c_row.XTXMBH;        v_ZJZJZJRQ:=c_row.ZJZJZJRQ;        IF (v_XTXMBH IS NOT NULL OR v_XTXMBH<>‘‘) THEN           if (v_ZJZJZJRQ IS NULL OR v_ZJZJZJRQ=‘‘) then            --UPDATE ZJ           UPDATE QJGLXX_ZQL_MID T              SET T.ZJZJZJRQ = ‘20000101‘, T.ZJZJZJJE = 0, T.LJZJZJJE = 0            WHERE T.XTXMBH=v_XTXMBH;          else            --UPDATE QH            UPDATE QJGLXX_ZQL_MID T              SET T.ZJQHZJRQ = ‘20000101‘, T.ZJQHZJJE = 0, T.LJQHZJJE = 0            WHERE T.XTXMBH=v_XTXMBH;          end if;         END IF;      end loop; --迴圈結束      commit; --提交處理後資料    end;