采用建立临时表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 /*传入的联社号*/ 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.tran_glac = v_tran_glac_IN /*科目为传入的科目*/ ORDER BY a.tran_glac,a.tran_deprd;
/*按柜员扎帐*/CURSOR sel_cutjnteller 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.teller = v_teller_IN /*传入的柜员号*/ ORDER BY a.tran_glac,a.tran_deprd; /*全部扎帐*/CURSOR sel_cutjnall 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') ORDER BY a.tran_glac,a.tran_deprd;
BEGIN
/**********<1>初始化变量***************/ /*单笔四种发生额*/ l_csh_dr_amt := 0; l_csh_cr_amt := 0; l_trf_dr_amt := 0; l_trf_cr_amt := 0;
/*四种发生总笔数*/ l_csh_dr_cnt := 0; l_csh_cr_cnt := 0; l_trf_dr_cnt := 0; l_trf_cr_cnt := 0;
/*四种发生总和*/ l_tot_csh_dr_amt := 0; l_tot_csh_cr_amt := 0; l_tot_trf_dr_amt := 0; l_tot_trf_cr_amt := 0;
l_tran_glac := 0; l_tran_deprd := 0; l_tmp_glac := 0; l_tmp_deprd := 0; l_desc1 := ''; l_nature := 0;
/**************<2>删除CUJNCT里当天该天数据***************/ IF ( v_type_IN = 3 ) THEN /*删除全部扎帐*/ DELETE FROM CUJNCT WHERE bank_cd = v_bank_cd_IN AND sbank_cd = v_sbank_cd_IN AND hl_date = v_hl_date_IN AND int_curr = v_int_curr_IN; ELSE IF ( v_type_IN = 1 ) THEN /*科目扎帐*/ DELETE FROM CUJNCT WHERE bank_cd = v_bank_cd_IN AND sbank_cd = v_sbank_cd_IN AND hl_date = v_hl_date_IN AND int_curr = v_int_curr_IN AND tran_glac= v_tran_glac_IN; ELSE IF ( v_type_IN = 2 ) THEN /*记账员扎帐*/ DELETE FROM CUJNCT WHERE bank_cd = v_bank_cd_IN AND sbank_cd = v_sbank_cd_IN AND hl_date = v_hl_date_IN AND int_curr = v_int_curr_IN AND teller = v_teller_IN; ELSE IF (v_type_IN = 0 ) THEN /*工作站扎帐*/ DELETE FROM CUJNCT WHERE bank_cd = v_bank_cd_IN AND sbank_cd = v_sbank_cd_IN AND hl_date = v_hl_date_IN AND int_curr = v_int_curr_IN AND hl_stn = v_stn_IN; END IF; /*v_type_IN = 0*/ END IF; /*v_type_IN = 2*/ END IF; /*v_type_IN = 1*/ END IF; /*v_type_IN = 3*/
/**************<3>根据v_type_IN扎帐条件选择流水记录***************/ IF ( v_type_IN = 3 ) THEN OPEN sel_cutjnall; ELSE IF ( v_type_IN = 1 ) THEN OPEN sel_cutjnglac; ELSE IF ( v_type_IN = 2 ) THEN OPEN sel_cutjnteller; ELSE IF ( v_type_IN = 0 ) THEN OPEN sel_cutjnstn; END IF; END IF; END IF; END IF; /******************<4>开始读取循环***************************/ LOOP /******************<5>根据v_type_IN 进行FETCH****************/ IF ( v_type_IN = 3 ) THEN FETCH sel_cutjnall INTO l_tran_glac, l_tran_deprd, l_csh_cr_amt, l_csh_dr_amt, l_trf_cr_amt, l_trf_dr_amt, l_desc1, l_nature; EXIT WHEN sel_cutjnall%NOTFOUND; ELSE IF ( v_type_IN = 1 ) THEN FETCH sel_cutjnglac INTO l_tran_glac, l_tran_deprd, l_csh_cr_amt, l_csh_dr_amt, l_trf_cr_amt, l_trf_dr_amt, l_desc1, l_nature; EXIT WHEN sel_cutjnglac%NOTFOUND; ELSE IF ( v_type_IN = 2 ) THEN FETCH sel_cutjnteller INTO l_tran_glac, l_tran_deprd, l_csh_cr_amt, l_csh_dr_amt, l_trf_cr_amt, l_trf_dr_amt, l_desc1, l_nature; EXIT WHEN sel_cutjnteller%NOTFOUND; ELSE IF ( v_type_IN = 0 ) THEN FETCH sel_cutjnstn INTO l_tran_glac, l_tran_deprd, l_csh_cr_amt, l_csh_dr_amt, l_trf_cr_amt, l_trf_dr_amt, l_desc1, l_nature; EXIT WHEN sel_cutjnstn%NOTFOUND; END IF; END IF; END IF; END IF; /*FETCH游标结束*/ /******************<6>判断是否是同一科目***************/ IF ( l_tmp_glac = 0 or (l_tmp_glac = l_tran_glac AND l_tmp_deprd = l_tran_deprd) ) THEN l_tmp_glac := l_tran_glac; l_tmp_deprd := l_tran_deprd; l_tmp_desc1 := l_desc1; l_tmp_nature:= l_nature;
IF ( l_csh_dr_amt != 0 ) THEN l_tot_csh_dr_amt := l_tot_csh_dr_amt + l_csh_dr_amt; l_csh_dr_cnt := l_csh_dr_cnt + 1; END IF; IF ( l_csh_cr_amt != 0 ) THEN l_tot_csh_cr_amt := l_tot_csh_cr_amt + l_csh_cr_amt; l_csh_cr_cnt := l_csh_cr_cnt + 1; END IF; IF ( l_trf_dr_amt != 0 ) THEN l_tot_trf_dr_amt := l_tot_trf_dr_amt + l_trf_dr_amt; l_trf_dr_cnt := l_trf_dr_cnt + 1; END IF; IF ( l_trf_cr_amt != 0 ) THEN l_tot_trf_cr_amt := l_tot_trf_cr_amt + l_trf_cr_amt; l_trf_cr_cnt := l_trf_cr_cnt + 1; END IF; ELSE /*一旦不相同,插入CUJNCT表*/ INSERT INTO CUJNCT VALUES (v_bank_cd_IN,v_sbank_cd_IN,substr(v_hl_date_IN,1,8),v_int_curr_IN, substr(v_teller_IN,1,6),v_stn_IN,l_tmp_glac,l_tmp_deprd,l_tmp_nature, substr(l_tmp_desc1,1,50), l_csh_dr_cnt,l_tot_csh_dr_amt, l_csh_cr_cnt,l_tot_csh_cr_amt, l_trf_dr_cnt,l_tot_trf_dr_amt, l_trf_cr_cnt,l_tot_trf_cr_amt); l_tmp_glac := l_tran_glac; l_tmp_deprd := l_tran_deprd; l_tmp_desc1 := l_desc1; l_tmp_nature:= l_nature; l_csh_dr_cnt := 0; l_csh_cr_cnt := 0; l_trf_dr_cnt := 0; l_trf_cr_cnt := 0; l_tot_csh_dr_amt := 0; l_tot_csh_cr_amt := 0; l_tot_trf_dr_amt := 0; l_tot_trf_cr_amt := 0; IF ( l_csh_dr_amt != 0 ) THEN l_tot_csh_dr_amt := l_tot_csh_dr_amt + l_csh_dr_amt; l_csh_dr_cnt := l_csh_dr_cnt + 1; END IF; IF ( l_csh_cr_amt != 0 ) THEN l_tot_csh_cr_amt := l_tot_csh_cr_amt + l_csh_cr_amt; l_csh_cr_cnt := l_csh_cr_cnt + 1; END IF; IF ( l_trf_dr_amt != 0 ) THEN l_tot_trf_dr_amt := l_tot_trf_dr_amt + l_trf_dr_amt; l_trf_dr_cnt := l_trf_dr_cnt + 1; END IF; IF ( l_trf_cr_amt != 0 ) THEN l_tot_trf_cr_amt := l_tot_trf_cr_amt + l_trf_cr_amt; l_trf_cr_cnt := l_trf_cr_cnt + 1; END IF; END IF; END LOOP; /*循环读取结束*/ IF ( l_tmp_glac != 0 ) THEN INSERT INTO CUJNCT VALUES (v_bank_cd_IN,v_sbank_cd_IN,substr(v_hl_date_IN,1,8),v_int_curr_IN, substr(v_teller_IN,1,6),v_stn_IN,l_tmp_glac,l_tmp_deprd,l_tmp_nature, substr(l_tmp_desc1,1,50), l_csh_dr_cnt,l_tot_csh_dr_amt, l_csh_cr_cnt,l_tot_csh_cr_amt, l_trf_dr_cnt,l_tot_trf_dr_amt, l_trf_cr_cnt,l_tot_trf_cr_amt); END IF; /*关闭游标*/ IF ( v_type_IN = 3 ) THEN CLOSE sel_cutjnall; ELSE IF ( v_type_IN = 1 ) THEN CLOSE sel_cutjnglac; ELSE IF ( v_type_IN = 2 ) THEN CLOSE sel_cutjnteller; ELSE IF ( v_type_IN = 0 ) THEN CLOSE sel_cutjnstn; END IF; END IF; END IF; END IF; RETURN 0;END sp_overday;/在C程序里要执行这段存储过程,发送语句execute function sp_overday(888,9999,'20051212',1,50,0,'010011',3)对于oracle中的存储过程或者函数,调用方式不同 函数 execute function function_name(arg1,arg2) 或 select function_name(arg1,arg2) from dual; 过程 call procedure_name删除使用drop function 、drop procedure + name
