Oracle 当游标不返回数据时更新行

Purchase_Mas包含购买主数据，payment_amt包含从方收到的付款数据。

游标c2不会返回任何值时，找不到付款，但我仍然想进行一些计算，甚至在付款表中找不到数据。

运行以下脚本

当您触发查询 “从purchase_mas_tmp中选择 *;”
查看 (ID = 5和当事人代码 = 12) 和 (ID = 6和当事人代码 = 14) 当在光标c2中找不到付款金额时，但我想将os_amt显示为5的10000，并20000为6的付款id。
那么它怎么可能

create table PURCHASE_MAS
(
  id            NUMBER,
  party_code    NUMBER,
  total_pcs     NUMBER,
  total_amt     NUMBER,
  purchase_date DATE,
  reg_flg       CHAR(1),
  discount_amt  NUMBER
);


create table PAYMENT
(
  id           NUMBER,
  party_code   NUMBER,
  payment_date DATE,
  payment_amt  NUMBER
);

create global temporary table PURCHASE_MAS_TMP
(
  id            NUMBER,
  party_code    NUMBER,
  total_pcs     NUMBER,
  total_amt     NUMBER,
  purchase_date DATE,
  reg_flg       CHAR(1),
  payment_date  DATE,
  payment_amt   NUMBER,
  os_amt        NUMBER,
  discount_amt  NUMBER
)
on commit preserve rows;
 
insert into PURCHASE_MAS (ID, PARTY_CODE, TOTAL_PCS, TOTAL_AMT, PURCHASE_DATE, REG_FLG, DISCOUNT_AMT)
values (5, 12, 456, 10000, to_date('01-08-2018', 'dd-mm-yyyy'), 'Y', 100);

insert into PURCHASE_MAS (ID, PARTY_CODE, TOTAL_PCS, TOTAL_AMT, PURCHASE_DATE, REG_FLG, DISCOUNT_AMT)
values (6, 14, 878, 20000, to_date('21-08-2018', 'dd-mm-yyyy'), 'N', 200);

insert into PURCHASE_MAS (ID, PARTY_CODE, TOTAL_PCS, TOTAL_AMT, PURCHASE_DATE, REG_FLG, DISCOUNT_AMT)
values (1, 11, 457, 30000, to_date('11-08-2018', 'dd-mm-yyyy'), 'Y', 300);

insert into PURCHASE_MAS (ID, PARTY_CODE, TOTAL_PCS, TOTAL_AMT, PURCHASE_DATE, REG_FLG, DISCOUNT_AMT)
values (2, 12, 658, 40000, to_date('10-08-2018', 'dd-mm-yyyy'), 'Y', 400);

insert into PURCHASE_MAS (ID, PARTY_CODE, TOTAL_PCS, TOTAL_AMT, PURCHASE_DATE, REG_FLG, DISCOUNT_AMT)
values (3, 11, 1454, 50000, to_date('07-08-2018', 'dd-mm-yyyy'), 'Y', 500);

insert into PURCHASE_MAS (ID, PARTY_CODE, TOTAL_PCS, TOTAL_AMT, PURCHASE_DATE, REG_FLG, DISCOUNT_AMT)
values (4, 13, 1254, 60000, to_date('18-08-2018', 'dd-mm-yyyy'), 'N', 600);
 
 insert into PAYMENT (ID, PARTY_CODE, PAYMENT_DATE, PAYMENT_AMT)
values (1, 11, to_date('01-09-2018', 'dd-mm-yyyy'), 2500);

insert into PAYMENT (ID, PARTY_CODE, PAYMENT_DATE, PAYMENT_AMT)
values (2, 12, to_date('12-09-2018', 'dd-mm-yyyy'), 3000);

insert into PAYMENT (ID, PARTY_CODE, PAYMENT_DATE, PAYMENT_AMT)
values (3, 11, to_date('11-09-2018', 'dd-mm-yyyy'), 30000);

insert into PAYMENT (ID, PARTY_CODE, PAYMENT_DATE, PAYMENT_AMT)
values (4, 13, to_date('21-09-2018', 'dd-mm-yyyy'), 400);
 
declare
  cursor c1 is
    select id, party_code, total_pcs, total_amt, purchase_date, reg_flg , discount_amt
      from purchase_mas;
  cursor c2 is
    select id, party_code, sum(payment_amt) payment_amt  from payment group by id, party_code ;

begin

  for i in c1 loop
    insert into purchase_mas_tmp
      (id, party_code, total_pcs, total_amt, purchase_date, reg_flg,discount_amt)
    values
      (i.id,
       i.party_code,
       i.total_pcs,
       i.total_amt,
       i.purchase_date,
       i.reg_flg,
       i.discount_amt);  
  end loop;
  
  for i in c2 loop
    update purchase_mas_tmp tbl
           set payment_amt = nvl(i.payment_amt,0),
           os_amt = tbl.total_amt - nvl(tbl.discount_amt,0) - nvl(i.payment_amt,0)
     where id = i.id and
           party_code = i.party_code ;
    
  end loop;

end;

-- select * from purchase_mas_tmp ; 

/*

  drop table PURCHASE_MAS purge ;
  drop table PAYMENT purge ;
  drop table purchase_mas_tmp purge ;
  
  */

因此，您想在没有匹配的付款行时设置付款和操作系统金额吗？

使用循环来做到这一点的问题是没有源行。因此，对于缺少的行，没有循环的迭代

使用循环的另一个问题是它是slooooooowwwwwwwww。

您可以通过以下方式解决这些问题:

-有一个外部将付款连接到purchase_mas的单一查询
-使用分析函数来计算付款金额 (假设从购买到付款的1:1映射; 如果购买可以有很多付款，则需要进行分组)

进行这些更改，您需要做的就是插入此查询的结果:

select m.id, m.party_code, m.total_pcs, m.total_amt, m.purchase_date, reg_flg , discount_amt,
       nvl ( sum(payment_amt) over ( partition by m.id, m.party_code), 0 ) payment,
       total_amt - nvl(discount_amt,0) - nvl ( sum(payment_amt) over ( partition by m.id, m.party_code), 0 ) os_amt
from   purchase_mas m
left join payment p
on     m.id = p.id
and    m.party_code = p.party_code;

ID    PARTY_CODE   TOTAL_PCS   TOTAL_AMT PURCHASE_DATE          REG_FLG   DISCOUNT_AMT   PAYMENT   OS_AMT   
    1         11         457       30000 11-AUG-2018 00:00:00   Y                  300      2500    27200 
    2         12         658       40000 10-AUG-2018 00:00:00   Y                  400      3000    36600 
    3         11        1454       50000 07-AUG-2018 00:00:00   Y                  500     30000    19500 
    4         13        1254       60000 18-AUG-2018 00:00:00   N                  600       400    59000 
    5         12         456       10000 01-AUG-2018 00:00:00   Y                  100         0     9900 
    6         14         878       20000 21-AUG-2018 00:00:00   N                  200         0    19800

不需要任何明确的客户!