DB2 拉链表存储过程的实现过程描述

此文章主要向大家描述的DB2 拉链表存储过程的实际操作过程，在实际操作中我们大家根据 T_depbal 账户余额表以及交易流水表，来生成拉链表，在拉链表中，开始日期为2006-1-1 结束日期为2050-12-31日。

建表

createtableT_depchg ( ACCOUNT_NOint, START_DATEDATE, ENT_DATEDATE, BALANCEdouble, AVG_BALdouble ) CREATETABLET_DEPBAL ( ACCOUNT_NOint, BALANCEdouble ) 

——————————————————————————

insertintot_journalvalues(date('2006-1-4'),2,200,1) createtablet_journal ( TRANS_DATEDATE, ACCOUNT_NOint, AMTdouble, FLAGdouble ) 

—————————————————————- -DB2 拉链表存储过程 —————————————————————-

createprocedurebal RESULTSETS1 MODIFIESSQLDATA begin declarev_accountint; declarev_balanceintdefault0; declaresqlcodeintdefault0; declarev_daysintdefault0; declarevar_balanceintdefault0; declarev_allintdefault0; declarev_accint; declarev_start_datedate; declarev_end_datedate; declarev_trans_datedate; declarev_max_datedate; declarev_bt_daysintdefault0; declarev_inint; declarev_deint; declarev_amtintdefault0; declarev_avg_balintdefault0; declarev_balint; declarecur_accountcursorwithreturnforselect*fromt_depbal; --cur_journaldefinition declarecur_journalcursorfor selectaccount_no,trans_date,COALESCE(sum(Out),0)asOut,COALESCE(sum(de),0)asde,COALESCE(sum(de),0)-COALESCE(sum(Out),0)asrefrom ( selecttrans_date,account_no, casewhenflag=0thenAmtendasOut, casewhenflag=1thenAmtendasde fromt_journalwhereaccount_no=v_accountOrderBytrans_Date )t1 groupbytrans_date,account_no orderbyaccount_no,trans_date; --max_date_cur declaremax_date_curcursorfor selectmax(trans_date)fromt_journal whereaccount_no=v_account; opencur_account deletefromt_depchg; opencur_account; out_loop: loop fetchcur_accountintov_account,v_balance; IFSQLCODE=100thenleaveout_loop; endif; INSERTINTODEMOVALUES(V_ACCOUNT,V_BALANCE); setvar_balance=v_balance; setv_all=0; setv_start_date=date('2006-1-1'); --opencur_journal opencur_journal; fetch_loop: loop fetchcur_journalintov_acc,v_trans_date,v_in,v_de,v_amt; IFSQLCODE=100thenleavefetch_loop; endif; setv_bal=var_balance; setv_end_date=v_trans_date; setv_days=days(v_end_date)-days(date('2006-1-1')); setv_bt_days=(days(v_end_date)-days(v_start_date)); setv_allv_all=v_all+(var_balance*v_bt_days); setv_avg_bal=v_all/v_days; insertintot_depchgvalues(v_acc,v_start_date,v_end_date,v_bal,v_avg_bal); setvar_balancevar_balance=var_balance+v_amt; setv_start_date=v_end_date; --processingdeaddate ifv_start_date=v_max_datethen setv_days=days('2050-12-31')-days(date('2006-1-1')); setv_bt_days=(days('2050-12-31')-days(v_start_date)); setv_allv_all=v_all+(var_balance*v_bt_days); setv_avg_bal=v_all/v_days; insertintot_depchgvalues(v_acc,v_start_date,date('2050-12-31'),var_balance,v_avg_bal); endif; openmax_date_cur; date_loop: loop fetchmax_date_curintov_max_date; IFSQLCODE=100thenleavedate_loop; endif; endloop; closemax_date_cur; endloop; closecur_journal; endloop; closecur_account; end@ dropprocedurebal callbal select*fromt_depchg 

以上的相关内容就是对DB2 拉链表存储过程的介绍，望你能有所收获。

【编辑推荐】

DB2数据库的部分日常实用操作的阐述DB2数据库为单个会话的锁定技巧如何其作用？DB2数据库SQL编码优化与性能描述对DB2数据库编目概念正确理解DB2未使用索引的查找方案描述