银行日间扎帐-ORACLE存储过程

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

阅读更多
想对作者说点什么?

博主推荐

换一批

没有更多推荐了,返回首页