近期编写tcbs存储过程小记(1)

/**************************************************************************/
/*******20110829zxy 新增存储过程 proc_ADJUSTSUBMAMT************************/
/*******20110829zxy 调用存储过程 call proc_submavailfunds 存储过程*********/
/**************************************************************************/
procedure proc_ADJUSTSUBMAMT(in_DEDUCTSCHEDNBR in deductschedsubm.deductschednbr%type,
                             in_submnbr in deductschedsubm.submnbr%type,
                             in_debugyn in char,
                             out_errornbr out integer,
                             out_errormsg out varchar2,
                             out_oraerrormsg out varchar2)
as
/**************************************************************************************/
/*********************PROCEDURE NAME:　proc_ADJUSTSUBMAMT    **************************/
/*********************DESCRIPTION:调整某个代发协议下须调整批次号的批次内调整金额*******/
/*********************            与其它非调整批次号的批次内调整金额       ************/
/*********************CALLED FROM: 前台界面             *******************************/
/*********************MODIFICATION HISTORY:zxy          *******************************/
/**************************************************************************************/


/************************************************************************************/
/*****            GENERAL ERROR PROCESSING VARIABLES                            *****/
/************************************************************************************/
OSI_GENERAL_ERROR                          EXCEPTION;--osi通用异常





/************************************************************************************/
/*****            PROCEDURE VARIABLES                                           *****/
/************************************************************************************/
cursor cur_deductsubm
is
Select DEDUCTSCHEDNBR,submnbr
from DEDUCTSCHEDSUBM
where DEDUCTSCHEDNBR= in_DEDUCTSCHEDNBR;
lvndeductschednbr deductschedsubm.deductschednbr%type;
lvnsubmnbr deductschedsubm.submnbr%type;  

lvnUserPersNbr                        INTEGER;

lvsActvMsg                                 VARCHAR2(100);--20110829 zxy
lvsdeductschednbr deductschedsubm.deductschednbr%type;
lvssubmnbr deductschedsubm.submnbr%type;

lcsdebugproccd   constant varchar2(4):='AJSM';--跟踪标识
---下述为被调用存储过程PROC_SUBMAVAILFUNDS的输出参数
out_SUBMDEPAMT  NUMBER;                                  --批次总存款额
out_SUBMPAYROLLAMT  NUMBER;                           --批次的已占用余额
out_SUBMBALAMT  NUMBER;                                  --批次的可用余额
out_SUBMWTHAMT  NUMBER;                                  --批次已代发的金额
out_DEDUCTBALAMT  NUMBER;                                --计划号下的可用余额

lvsDEDUCTBALAMT number;
lvsSUBMBALAMT  number;
lvsoraclemsg   VARCHAR2(100);
lvsdebugyn     char;





begin


lvsdebugyn:=in_debugyn;

lvsActvMsg:='开始运行存储过程proc_adjustsubmamt';

if (lvsdebugyn='Y') then
  insert into sp_debug(seqnbr,debugdate,debugtime,procname,debugtext)
  values(sp_debugseqnbr.nextval,sysdate,to_char(sysdate,'hh24:mi:ss'),
         'proc_ADJUSTSUBMAMT','procedure;lvsactvmsg: '||lvsActvMsg);
end if;

lvsActvMsg:='打开游标cur_deductsubm开始循环提取协议号和批次号';
open  cur_deductsubm;
loop
   fetch cur_deductsubm into lvnDeductSchedNbr,lvnsubmnbr;
   exit when cur_deductsubm%notfound;
        lvsActvMsg:='提取锁定的批发协议和批次记录';
        begin
          select DEDUCTSCHEDNBR, SUBMNBR
          into   lvsDEDUCTSCHEDNBR, lvsSUBMNBR
          from DEDUCTSCHEDSUBM
          where DEDUCTSCHEDNBR= lvnDeductSchedNbr
                and submnbr=lvnsubmnbr
          for update nowait;
        exception
          when others then
          raise OSI_GENERAL_ERROR;
        end;  
        
       
       
       
        --20110905 zxy 注解
        --lvsActvMsg:='如果是代发协议下的调整批次';
        begin
              if lvssubmnbr=in_submnbr then
             
             
                           --调用PROC_SUBMAVAILFUNDS存储过程
                            PROC_SUBMAVAILFUNDS(lvsDeductSchedNbr,
                                                lvssubmnbr,
                                                lvsdebugyn,
                                                out_SUBMDEPAMT,
                                                out_SUBMPAYROLLAMT,
                                                out_SUBMBALAMT,
                                                out_SUBMWTHAMT,
                                                out_DEDUCTBALAMT,
                                                out_ERRORNBR,
                                                out_ERRORMSG,
                                                out_ORAERRORMSG);
                                               
                                               
                                   --20110906 zxy 暂时注解此代码
                            if out_errornbr<>0 then
                              lvsActvMsg:='执行补调用存储过程PROC_SUBMAVAILFUNDS出错';
                              raise OSI_GENERAL_ERROR;
                            end if;
                           
                            lvsActvMsg:='用存储过程的参数lvsDEDUCTBALAMT及lvsSUBMBALAMT存储被调用存储过程的输出参数信息';
                            lvsDEDUCTBALAMT:=out_DEDUCTBALAMT;--20110905 zxy 原sql lvsDEDUCTBALAMT:=out_DEDUCTBALAMT;
                            lvsSUBMBALAMT:=out_SUBMBALAMT; --20110905 zxy 原sqllvsSUBMBALAMT:=out_SUBMBALAMT;
                       
                           
                                  
                            update deductschedsubm
                           
                            
                            --20110905 zxy 暂把ADJUSTMENTAMT写成固定值，对比测试
                            --20110906 zxy comment below line
                           
                            set    adjustmentamt=adjustmentamt +(lvsDEDUCTBALAMT-lvsSUBMBALAMT),
                                   datelastmaint=sysdate
                            where  DEDUCTSCHEDNBR= lvsDeductSchedNbr
                                   and SUBMNBR= lvsSubmNbr;
                            
                           
                                  
                          
                            if (sql%rowcount=0) then
                                 raise OSI_GENERAL_ERROR;
                           
                            end if;
                   
            -- end if;
         --end;
        
         --20110905 zxy 注解
         --lvsActvMsg:='如果是代发协议下其它其它非调整批次';
         --begin
        
                else 
                          --调用PROC_SUBMAVAILFUNDS存储过程
                                PROC_SUBMAVAILFUNDS(lvsDeductSchedNbr,
                                                    lvssubmnbr,
                                                    lvsdebugyn,
                                                    out_SUBMDEPAMT,
                                                    out_SUBMPAYROLLAMT,
                                                    out_SUBMBALAMT,
                                                    out_SUBMWTHAMT,
                                                    out_DEDUCTBALAMT,
                                                    out_ERRORNBR,
                                                    out_ERRORMSG,
                                                    out_ORAERRORMSG);
                                /*if out_errornbr!=0 then
                                   lvsActvMsg:='执行存储过程PROC_SUBMAVAILFUNDS';
                                   raise OSI_GENERAL_ERROR;
                                end if;*/
                     
                      
                               lvsSUBMBALAMT:=out_SUBMBALAMT;--20110905 zxy 原sql        lvsSUBMBALAMT:=out_SUBMBALAMT           
                     
                     
                     
                               update deductschedsubm
                               --20110905 zxy 暂把ADJUSTMENTAMT写成固定值，对比测试
                               --20110906 zxy comment below line
                                 --set adjustmentamt=333,
                               set    adjustmentamt=adjustmentamt-lvsSUBMBALAMT,
                                       datelastmaint=sysdate
                               where  DEDUCTSCHEDNBR= lvsDeductSchedNbr
                                      and SUBMNBR= lvsSubmNbr;
                                     
                             
                               if (sql%rowcount=0) then
                                     raise OSI_GENERAL_ERROR;
                              
                               end if;
                end if;             
               
        end;
       
       
        end loop;
       
        close cur_deductsubm;
        out_errornbr:=0;
       
       
        exception
            when OSI_GENERAL_ERROR then
               lvsoraclemsg:=substr(sqlerrm,1,100);
               out_errornbr:=abs(sqlcode);
               out_errormsg:=lvsactvmsg||'_'||lcsdebugproccd;
               out_oraerrormsg:=lvsoraclemsg;
              
               if (in_debugyn='Y') then
                  insert into sp_debug(seqnbr,debugdate,debugtime,persnbr,procname,debugtext)
                  values(sp_debugseqnbr.nextval,sysdate,to_char(sysdate,'hh24:mi:ss'),lvnUserPersNbr,'proc_ADJUSTSUBMAMT',
                  '**procedure failed** '||lvsActvMsg||' *** '||lvsoraclemsg);
               END IF;
              
            when others then
               lvsoraclemsg:=substr(sqlerrm,1,100);
               out_errornbr:=abs(sqlcode);
               out_errormsg:=lvsactvmsg||'_'||lcsdebugproccd;
               out_oraerrormsg:=lvsoraclemsg;
               if (in_debugyn='Y') then
                  insert into sp_debug(seqnbr,debugdate,debugtime,persnbr,procname,debugtext)
                  values(sp_debugseqnbr.nextval,sysdate,to_char(sysdate,'hh24:mi:ss'),lvnUserPersNbr,'proc_ADJUSTSUBMAMT',
                  '**procedure failed** '||lvsActvMsg||' *** '||lvsoraclemsg);
               END IF;


end     proc_ADJUSTSUBMAMT;                        


END PACK_FILELOAD_PAYR;



小结：
   清晰理解前端界面与后端数据库的输入及输出参数的映射关系，通俗点讲就是：前端界面的信息如何与后端oracle存储过程或者函数及sql语句中的参数的对应关系．前台如何把信息传递给后端．而后端如何接受前端的信息．再进一步．后端经加工后的信息又是如何传递给前端.
  存储过程的名称命名规则一定要与整体的tcbs的存储过程相一致．作到见字识人之效
  存储过程的输入输出参数的命名规则也是同理
  存储过程体中的变量定义规则也是采用tcbs的三大块命名规范：
     异常变量定义
     过程变量定义
     过程常量定义
     变量前缀必须是：lvn,lvs等，不可自造一个，违反整体的规范
  存储过程中异常的处理一定要有始有终，每作一个select,update,insert,delete必须要通过sp_debug表来记录操作日志信息，便于后期故障分析
  要深入理解tcbs各类业务情形，有针对性的编写各类异常处理代码，不可多写也绝不能少写，不会前端业务在运行会出现无法预知的情况
   深深的理解到代码的健壮性问题是非常重要的
   在编写代码前，一定要静下心来，全方位分析思考你要实现什么功能，然后是如何实现此功能．实现此功能后，如何确认此功能是符合客户所需要，如何科学的测试.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-706900/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-706900/