采用建立临时表CUJNCT,每次调用时统计数据后放入到临时表,然后再从临时表中选择数据传输到网点终端
存储过程代码如下:
CREATE OR REPLACE FUNCTION "SP_OVERDAY" (
v_bank_cd_IN NUMBER,
v_sbank_cd_IN NUMBER,
v_hl_date_IN CHAR,
v_int_curr_IN NUMBER,
v_stn_IN NUMBER,
v_tran_glac_IN NUMBER,
v_teller_IN CHAR,
v_type_IN NUMBER) RETURN NUMBER AS
/*传入参数说明
v_type_IN=0为工作站扎帐
v_type_IN=1为科目扎帐
v_type_IN=2为记账员扎帐
v_type_IN=3为全部扎帐
*/
/***********************自定义变量,存储过程中使用***********/
/*单笔流水四种发生额*/
l_csh_dr_amt NUMBER(15,2);
l_csh_cr_amt NUMBER(15,2);
l_trf_dr_amt NUMBER(15,2);
l_trf_cr_amt NUMBER(15,2);
/*四种发生总笔数*/
l_csh_dr_cnt NUMBER(5);
l_csh_cr_cnt NUMBER(5);
l_trf_dr_cnt NUMBER(5);
l_trf_cr_cnt NUMBER(5);
/*四种发生总和*/
l_tot_csh_dr_amt NUMBER(15,2);
l_tot_csh_cr_amt NUMBER(15,2);
l_tot_trf_dr_amt NUMBER(15,2);
l_tot_trf_cr_amt NUMBER(15,2);
l_tran_glac NUMBER(10);
l_tran_deprd NUMBER(10);
l_tmp_glac NUMBER(10);
l_tmp_deprd NUMBER(10);
l_desc1 CHAR(50);
l_nature NUMBER(5);
l_tmp_desc1 CHAR(50);
l_tmp_nature NUMBER(5);
/*********************定义各种游标*******************/
/*按工作站扎帐*/
CURSOR sel_cutjnstn IS
SELECT a.tran_glac,a.tran_deprd,
a.csh_cr_amt,a.csh_dr_amt,a.trf_cr_amt,a.trf_dr_amt,b.desc1,b.nature
FROM CUTRJN a,CUGLAC b
WHERE a.int_curr = b.curr
AND a.tran_glac= b.gl_num
AND a.bank_cd = v_bank_cd_IN /*传入的联社号*/
AND a.sbank_cd = v_sbank_cd_IN /*传入的网点号*/
AND a.hl_date = v_hl_date_IN /*传入的处理日期*/
AND a.int_curr = v_int_curr_IN /*传入的笔种*/
AND a.tran_status not in (4,5)
AND a.other_bk_id not in ('2','4','5')
AND a.hl_stn = v_stn_IN /*传入的工作站*/
ORDER BY a.tran_glac,a.tran_deprd;
/*按科目扎帐*/
CURSOR sel_cutjnglac IS
SELECT a.tran_glac,a.tran_deprd,
a.csh_cr_amt,a.csh_dr_amt,a.trf_cr_amt,a.trf_dr_amt,b.desc1,b.nature
FROM CUTRJN a,CUGLAC b
WHERE a.int_curr = b.curr
AND a.tran_glac= b.gl_num
AND a.bank_cd = v_bank_cd_IN /*传入的联社号*/