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 语句 那就会出问题

对事物的控制语句一定要把握好

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7199859/viewspace-421397/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7199859/viewspace-421397/