FOR v_record IN getnonlegalchildren_cur LOOP IF (exists( select 1 from AUTOGRADE_MAIN am where am.acct_nat_key = v_record.ult_prt_acct_nat_key)) THEN
SELECT am.cmmt_amt INTO v_cmmtamt FROM MAIN am WHERE am.a_key = v_record.b_key;
UPDATE MAIN am SET am.cmmt_amt = v_cmmtamt - v_record.cmmt_amt WHERE am.a_key = v_record.b_key;
END LOOP
update main am set am.cmmt_amt = (select am.cmmt_amt - x.cmmt_amt from MAIN x where am.a_key = x.b_key) where exists (select 1 from AUTOGRADE_MAIN a_m where a_m.acct_nat_key = am.ult_prt_acct_nat_key) and exists (select 1 from MAIN x where am.a_key = x.b_key and x.A_KEY <> x.B_KEY AND x.CM <> 0 AND x.FLAG = 'N') 你上面的if (exists()) 应该会报错的吧,没有这个语法
update MAIN am set cmmt_amt = 0 WHERE exists(AUTOGRADE_MAIN am where am.acct_nat_key = v_record.ult_prt_acct_nat_key) and am.A_KEY <> am.B_KEY AND am.CM <> 0 AND am.FLAG = 'N';
满足am.a_key = x.b_key条件可能出现多条,需要求和 update main am set am.cmmt_amt = (select am.cmmt_amt - nvl(sum(x.cmmt_amt),0) from MAIN x where am.a_key = x.b_key) where exists (select 1 from AUTOGRADE_MAIN a_m where a_m.acct_nat_key = am.ult_prt_acct_nat_key) and exists (select 1 from MAIN x where am.a_key = x.b_key and x.A_KEY <> x.B_KEY AND x.CM <> 0 AND x.FLAG = 'N')
对的 会报错 ,update main am set am.cmmt_amt = (select am.cmmt_amt - x.cmmt_amt from MAIN x where am.a_key = x.b_key) 要是select am.cmmt_amt - x.cmmt_amt from MAIN x where am.a_key = x.b_key X中有多条符合的,是会自动一条一条的从am.cmmt_amt减去吗?
所以,最关键的部分,LOOP里面做了什么,你没有给出
FOR v_record IN getnonlegalchildren_cur
LOOP
IF (exists( select 1 from AUTOGRADE_MAIN am where am.acct_nat_key = v_record.ult_prt_acct_nat_key))
THEN
SELECT am.cmmt_amt INTO v_cmmtamt
FROM MAIN am
WHERE am.a_key = v_record.b_key;
UPDATE MAIN am
SET am.cmmt_amt = v_cmmtamt - v_record.cmmt_amt
WHERE am.a_key = v_record.b_key;
END LOOP
set am.cmmt_amt =
(select am.cmmt_amt - x.cmmt_amt from MAIN x where am.a_key = x.b_key)
where exists (select 1
from AUTOGRADE_MAIN a_m
where a_m.acct_nat_key = am.ult_prt_acct_nat_key)
and exists (select 1
from MAIN x
where am.a_key = x.b_key
and x.A_KEY <> x.B_KEY
AND x.CM <> 0
AND x.FLAG = 'N')
你上面的if (exists()) 应该会报错的吧,没有这个语法
update MAIN am
set cmmt_amt = 0
WHERE exists(AUTOGRADE_MAIN am where
am.acct_nat_key = v_record.ult_prt_acct_nat_key)
and am.A_KEY <> am.B_KEY
AND am.CM <> 0
AND am.FLAG = 'N';
update main am
set am.cmmt_amt =
(select am.cmmt_amt - nvl(sum(x.cmmt_amt),0) from MAIN x where am.a_key = x.b_key)
where exists (select 1
from AUTOGRADE_MAIN a_m
where a_m.acct_nat_key = am.ult_prt_acct_nat_key)
and exists (select 1
from MAIN x
where am.a_key = x.b_key
and x.A_KEY <> x.B_KEY
AND x.CM <> 0
AND x.FLAG = 'N')
对的 会报错 ,update main am
set am.cmmt_amt =
(select am.cmmt_amt - x.cmmt_amt from MAIN x where am.a_key = x.b_key)
要是select am.cmmt_amt - x.cmmt_amt from MAIN x where am.a_key = x.b_key X中有多条符合的,是会自动一条一条的从am.cmmt_amt减去吗?
也就是6楼的那样