今天修復資料寫了個pl/sql

以下這個pl/sql 是針對業務資料出了問題

而進行修復的,涉及到一些判斷

spool test.log
declare
   CURSOR emp_cur IS
  SELECT a.client_id client_id,
       b.login,
       b.login_uid,
       c.amount  amount,
       c.created_date created_date,
       c.status,
       d.after_balance  after_balance
  from tb_test_status a,
       tb_test b,
       (select account_id, amount,created_date,status
          from (select account_id,
                       amount,
                       created_date,
                       status,
                       row_number() over(partition by account_id order by created_date desc) rn
                  from tb_test_log
                 where account_id in (select client_id
                                        from tb_client_status
                                       where online_ = 'T'))
         where rn = 1) c,
       (select client_id, after_balance
          from (select client_id,
                       after_balance,
                       row_number() over(partition by client_id order by created_date desc) rn
                  from tb_test2_log
                 where client_id in (select client_id
                                       from tb_client_status
                                      where online_ = 'T')
                   and transaction_code_id = 3
                   and system_type = 2
                   and status = 1)
         where rn = 1) d

 where a.online_ = 'T'
   and a.client_id = b.id
   and c.account_id = b.id
   and a.client_id = d.client_id(+);

      ---以上是定義個遊標
   emp_rec emp_cur%ROWTYPE;
   cashtransfer_date date;
   cashflow_date date;
 
BEGIN
   FOR emp_rec IN emp_cur LOOP     --for迴圈
     select nvl(max(created_date),to_date('1970-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) into cashtransfer_date
      from tb_cashtransfer_log where account_id=emp_rec.client_id;
     select nvl(max(created_date),to_date('1970-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) into cashflow_date
   from tb_cashflow_log where client_id=emp_rec.client_id and transaction_code_id=3 and system_type=2 and status=1;
    if cashtransfer_date>cashflow_date then     --判斷
       update tb_test_log set tb_cashtransfer_log.status=0 where account_id=emp_rec.client_id and created_date=emp_rec.created_date;
       update tb_test set credit_amount=emp_rec.AMOUNT,LAST_MODIFIED_DATE=sysdate Where id=emp_rec.client_id;
       update tb_test_status set LOCATION_TYPE=0,ONLINE_='F',LAST_MODIFIED_DATE=sysdate Where client_id=emp_rec.client_id;
      
    
    else
      update tb_test_log set status=2 where account_id=emp_rec.client_id and created_date=emp_rec.created_date;
      insert into tb_test_log
      (transfer_type,account_type,account_id,amount,login_uid,status,created_date,last_modified_date,ref_no)
      values
      (2,1,emp_rec.client_id,emp_rec.after_balance,emp_rec.login_uid,2,sysdate,sysdate,' ');
      update tb_test_status set LOCATION_TYPE=0,ONLINE_='F',LAST_MODIFIED_DATE=sysdate,Cashtransferid=S_TB_CASHTRANSFER_LOG.currval Where client_id=emp_rec.client_id;
      update tb_test set credit_amount=emp_rec.after_balance,LAST_MODIFIED_DATE=sysdate Where id=emp_rec.client_id;
    
   
    end if;
  end loop;
  commit;

END;
/
spool off;
exit;