dba工作一定要細心:由於不細心導致的一個小問題

今天在客戶那執行修復資料sql時

SQL> @ d:\spt1322_old.sql
Started spooling to C:\vc\vc\spt1322.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_client_status a,
       tb_client 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_cashtransfer_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_cashflow_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
     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_co
 
ORA-06550: line 63, column 90:
PL/SQL: ORA-00904: "CASHTRANSFERID": invalid identifier
ORA-06550: line 63, column 7:
PL/SQL: SQL Statement ignored

一檢查發現客戶上還沒有上這個欄位的功能,而我們這邊開發和測試環境都已經加上了

解決很簡單 去掉更新這個表的欄位即可

還有一點注意 是這個pl/sql 到最後

end loop;
  commit;

才加的commit ,

所以先前出錯,導致整個事物回滾 對業務資料沒有一點影響 

要是中間某個update 語句 加了commit 語句 那就會出問題

對事物的控制語句一定要把握好