dba工作一定要细心:由于不细心导致的一个小问题

今天在客户那运行修复数据sql时

SQL> @ d:\spt1322_old.sqlITPUB个人空间a-vweG Z+V
Started spooling to C:\vc\vc\spt1322.logITPUB个人空间/? Rw"S&f*L?
 
Yc|&{t[#Ww.H0declareITPUB个人空间'x)U;jD!gr:x0g*vp
   CURSOR emp_cur ISITPUB个人空间"Ue5GbxYr
  SELECT a.client_id client_id,ITPUB个人空间 x]s3L cI:D
       b.login,
%w7N7E7QK`1P0       b.login_uid,ITPUB个人空间!E6lo!q1Y+_"P3|BS0M
       c.amount  amount,ITPUB个人空间F&a&K9e,j'O%KyP
       c.created_date created_date,
y&?oXTaB'~0       c.status,
]s?'i;MQ$mbe8l@0       d.after_balance  after_balance
KZGr,jI6Du3n0  from tb_client_status a,ITPUB个人空间TM3w9jDe|0`
       tb_client b,
-rv/d:| T0       (select account_id, amount,created_date,status
sHN ow0          from (select account_id,
/n^aak3|p0                       amount,
HU9y$VJS]0                       created_date,ITPUB个人空间@1adI9A+l-H
                       status,ITPUB个人空间Q YFgR:Fk0En:c)wW
                       row_number() over(partition by account_id order by created_date desc) rnITPUB个人空间 @MmGV"f!W
                  from tb_cashtransfer_log
&\9h3^c0_bL `h)s7m0                 where account_id in (select client_id
k&}H}El0                                        from tb_client_status
mMj \Af0                                       where online_ = 'T'))ITPUB个人空间h/wkI1m
         where rn = 1) c,
OR#jry3gd!C$r0       (select client_id, after_balance
0{]1Zv_1qi0          from (select client_id,ITPUB个人空间4?5~-u A7gI&|6AJ
                       after_balance,ITPUB个人空间}3[1B1iX)d
                       row_number() over(partition by client_id order by created_date desc) rn
Jk[2?gN,k0                  from tb_cashflow_logITPUB个人空间kC9@5DI rX
                 where client_id in (select client_idITPUB个人空间W0D]8@+p9Cnp%e8R| y
                                       from tb_client_statusITPUB个人空间!A%Y2wF-g2|q(Ta
                                      where online_ = 'T')ITPUB个人空间
umaL@SB
                   and transaction_code_id = 3
w-}M3CR \,?+Xx u0                   and system_type = 2
3A,X@6?X0u'oi$J;d@0                   and status = 1)ITPUB个人空间E0{t.T/i6jF,Q0YF
         where rn = 1) d

 where a.online_ = 'T'ITPUB个人空间u3e ra-AX
   and a.client_id = b.idITPUB个人空间pJr"oD+D
   and c.account_id = b.id
cS%Gj3]g)q2[Z0   and a.client_id = d.client_id(+);
\X+xW~S]L#Es M#D0   emp_rec emp_cur%ROWTYPE;
E%o q M*fBc5qXP0   cashtransfer_date date;ITPUB个人空间wn)p/Y$i'e{F
   cashflow_date date;

BEGINITPUB个人空间 ^ou8PIU:iDs
   FOR emp_rec IN emp_cur LOOPITPUB个人空间}J
SR1BLJ
     select nvl(max(created_date),to_date('1970-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) into cashtransfer_dateITPUB个人空间8Jm-EGr%~$m
      from tb_cashtransfer_log where account_id=emp_rec.client_id;ITPUB个人空间@G%U$z1I-{M*N-o'x
     select nvl(max(created_date),to_date('1970-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) into cashflow_date
4^XZ'_JF0   from tb_cashflow_log where client_id=emp_rec.client_id and transaction_coITPUB个人空间A&RK&o8|-r[5t
 ITPUB个人空间XAlnIpd I\
ORA-06550: line 63, column 90:
Ze8{\,[*`A0PL/SQL: ORA-00904: "CASHTRANSFERID": invalid identifierITPUB个人空间)J1IT[3K;M{ K
ORA-06550: line 63, column 7:
_B(D#GJ0PL/SQL: SQL Statement ignored

一检查发现客户上还没有上这个字段的功能,而我们这边开发和测试环境都已经加上了

解决很简单 去掉更新这个表的字段即可

还有一点注意 是这个pl/sql 到最后

end loop;ITPUB个人空间4V6~R&b'| bcgiQ
  commit;

才加的commit ,

所以先前出错,导致整个事物回滚 对业务数据没有一点影响 

要是中间某个update 语句 加了commit 语句 那就会出问题

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