存储过程4：分析函数累计求和

最新推荐文章于 2021-03-10 10:32:45 发布

有毒的苹果

最新推荐文章于 2021-03-10 10:32:45 发布

阅读量1.3k

点赞数

分类专栏： pl/sql 程序块文章标签：存储过程分析函数累计求和

本文链接：https://blog.youkuaiyun.com/aq23es/article/details/70213571

版权

pl/sql 程序块专栏收录该内容

7 篇文章

订阅专栏

本文介绍了一个用于Oracle数据库的PL/SQL过程，该过程能够批量插入记录到cfe_alm_res_sum_orgs表中，并使用窗口函数来计算累计总和。此过程通过查询已存在的表数据并进行汇总计算，适用于银行资产管理系统的数据处理。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

create or replace procedure P_cfe_alm_ResSumTotal_Orgs2
 is
begin
 insert into cfe_alm_res_sum_orgs
     (
     PK_ALM_RES_SUM_ORGS,
     ACCT_PRD,
     asst_liab,
     bl_code,
     branch_code,
     busi_type,
     COUNTERPARTY,
     CREDIT_RATING,
     CURRENCY,
     CUST_CODE,
     DATA_DATE,
      dr,
     EXCHANGE_RATE,
      IS_TOTAL,
      ISSUER,
      ITEM_ID,
     PK_COA_SET,
      PK_IR_GAP_RUN,
     PK_ORG,
    PK_PMT_RUN,
     PK_PRD_SET,
     PK_RM_COA,
     PK_RM_PRD,
     PROD_ID,
     RP_GAP,
     ts,
     prin_pmt,
     int_pmt,
     cf
     )
     select
     sys_guid(),--PK_ALM_RES_SUM_ORGS
    orgs_row.ACCT_PRD,
     orgs_row.asst_liab,
     orgs_row.bl_code,
     orgs_row.branch_code,
     orgs_row.busi_type,
     orgs_row.COUNTERPARTY,
      orgs_row.CREDIT_RATING,
      orgs_row.CURRENCY,
      orgs_row.CUST_CODE,
      orgs_row.DATA_DATE,
      0,--dr
      orgs_row.EXCHANGE_RATE,
      1,--IS_TOTAL
      orgs_row.ISSUER,
      orgs_row.ITEM_ID,
      orgs_row.PK_COA_SET,
      orgs_row.PK_IR_GAP_RUN,
     orgs_row.PK_ORG,
     orgs_row.PK_PMT_RUN,
     orgs_row.PK_PRD_SET,
     orgs_row.PK_RM_COA,
     orgs_row.PK_RM_PRD,
     orgs_row.PROD_ID,
     orgs_row.RP_GAP,
     to_char(sysdate, 'yyyymmddhh24miss'),--ts
    
  sum(prin_pmt) over (partition by orgs_row.is_total,
     orgs_row.DATA_DATE,
     orgs_row.branch_code,
     orgs_row.CURRENCY,
     orgs_row.PK_IR_GAP_RUN,
     orgs_row.PK_PRD_SET,
     orgs_row.PK_ORG,
     orgs_row.PK_PMT_RUN,
     orgs_row.PK_COA_SET,
     orgs_row.PK_RM_COA,
     orgs_row.asst_liab order by orgs_row.acct_prd 
     rows between unbounded preceding and current row )
    prin_pmt,
  sum(int_pmt) over (partition by orgs_row.is_total,
     orgs_row.DATA_DATE,
     orgs_row.branch_code,
     orgs_row.CURRENCY,
     orgs_row.PK_IR_GAP_RUN,
     orgs_row.PK_PRD_SET,
     orgs_row.PK_ORG,
     orgs_row.PK_PMT_RUN,
     orgs_row.PK_COA_SET,
     orgs_row.PK_RM_COA,
     orgs_row.asst_liab order by orgs_row.acct_prd 
     rows between unbounded preceding and current row )

    int_pmt,
  sum(cf) over (partition by orgs_row.is_total,
     orgs_row.DATA_DATE,
     orgs_row.branch_code,
     orgs_row.CURRENCY,
     orgs_row.PK_IR_GAP_RUN,
     orgs_row.PK_PRD_SET,
     orgs_row.PK_ORG,
     orgs_row.PK_PMT_RUN,
     orgs_row.PK_COA_SET,
     orgs_row.PK_RM_COA,
     orgs_row.asst_liab order by orgs_row.acct_prd 
     rows between unbounded preceding and current row )
  cf
     from cfe_alm_res_sum_orgs orgs_row
     where orgs_row.pk_alm_res_sum_orgs in
  ( select pk_alm_res_sum_orgs from cfe_alm_res_sum_orgs_pks)

   ;

  --  dbms_output.put_line(v_sql);

  commit;
end P_cfe_alm_ResSumTotal_Orgs2;