采用建立临时表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
