????????2016??8?·????MOORACLE?????o??????(??K)???????????????????merge SQL???????д??plsql?????????????????Ч??? ?????????????????????????????????м????????????????????????????plsql???д??????????????д??????Ч???????????й?????????????????????????????м????
?????SQL???￡?
merge into t_customer c using
(
select a.cstno?? a.amount from t_trade a??
(select cstno??max(trade_date) trade_date from t_trade
group by cstno) b
where a.cstno = b.cstno and a.trade_date=b.trade_date
) m
on(c.cstno = m.cstno)
when matched then
update set c.amount = m.amount;
???????SQL????????????????t_trade ??????????????????μ??????????t_customer???????????Σ???????merge??????
??????м????

????????????
??????????????????????д?????SQL??????????group by???????????????????????????д??????????SQL???Ч??????????
?????SQL??????????????????t_trade?????cstno??????trade_date?????????????SQL??ORA-30926 ?????????С?
????????????????м?????????????????????????????SQL????????????????÷?????????д??
??????д????1??
merge into t_customer c using
(
select a.cstno??a.amount from
(select trade_date??cstno??amount??
row_number()over(partition by cstno order by trade_date desc) RNO from t_trade)a
where RNO=1
)  m
on(c.cstno = m.cstno)
when matched then
update set c.amount = m.amount;
?????????д????????SQLЧ???????????????????cstno?????max trade_date ?????????????
?????????????????÷??????????д??????????????????????????????????SQL??д????θ????Ч??plsql??
??????д????2??
declare
vamount number;
begin
for v in (select * from t_customer )
loop
select amount into vamount from
(select amount from t_trade where cstno=v.cstno order by trade_date desc)
where rownum<2;
update t_customer set amount = vamount where cstno=v.cstno;
end loop
commit;
end;
/
?????????SQL????м???????????t_customer????????????????1000????????t_trade????1000???????????1:10000????????????????????????????????1000?????????????????????1????????????????????????????????
???????????????????????????????????￡?plsqlд????????????????д?????Ч???????д??????
??????????????????????????д???????
????1??plsql???д??????????t_customer????С??????t_customer ?? t_trade ??????????????????????￡????Ч???????????????д???Щ????????У????t_customer?????????10???????????????д?????????plsql??д????????????????
????3??plsql?????д?????????????t_trade??cstno + trade_date ????ε????????????????????????д??????κ???????????
????4??????t_trade??????????????????÷?????????д???????????????????????plsql???д?????????Ч?????????t_customer???cstno???飬????session??????С?
????????????????????????????plsql???????????????sql????????????????????SQL???????￡?
merge into t_customer c using
(
select tc.cstno??
(select amount
from t_trade td1
where td1.cstno=tc.cstno and td1.trade_date = (select max(trade_date) from t_trade td2 where tc.cstno = td2.cstno) and rownum=1 ) as amount
from t_customer tc
)  m
on(c.cstno = m.cstno)
when matched then
update set c.amount = m.amount;
??????м?????????￡?

????????д????????t_trade?????cstno+trade_date ??????????IDX_T_TRADE????????T_customer ??????????????T_trade??
??????????м???????sql?????Ч??????plsqlд????Ч??????????
???????
????SQL???????????????Ч??SQLд????????????????????????????????????plsql???д???????????????????????????????????Ч??????Щ????????????￡?Ч???????????SQL???????????·?????y????
?????????????????д???????????????η??????????SQL???Ч???????????
?????????????д???SQL???????к?????????DBA????????????????????????÷??????SQL???Чд?????????????????
???????plsql??д???SQL?????????????????????????????????????д??????????o????
??????????仰???????????????????????????????????????????????????SQL???Ч?????????