跟屌丝一起学习 DB2 第五课 存储过程(三) 存储过程实例

简介:

客户在进行短信服务这个业务申请时,需要填写一些基本信息,然后根据这些信息判断这个用户是否已经存在于业务系统中。因为网上服务和业务系统两个项目物理隔离,而且网上数据库保存的客户信息不全,所以判断需要把数据交换到业务系统,在业务系统中判断。

    解决方式是通过存储过程,以前也了解过存储过程,但没使用到项目中。不过经过一番努力最后还是完成了,期间遇到了一些困难,特写此文让对DB2存储过程还不熟悉的童鞋避免一些无谓的错误。

   

复制代码
DROP PROCEDURE "PLName"
@
CREATE PROCEDURE "PLName"(--存储过程名字
IN IN_ID BIGINT , --以下全是输入参数
IN IN_ENTNAME VARCHAR(200) ,
IN IN_REGNO VARCHAR(50),
IN IN_PASSWORD VARCHAR(20),
IN IN_LEREP VARCHAR(300),
IN IN_CERTYPE CHARACTER(1),
IN IN_CERNO VARCHAR(50),
IN IN_LINKMAN VARCHAR(50),
IN IN_SEX CHARACTER(1),
IN IN_MOBTEL VARCHAR(30),
IN IN_REQDATE TIMESTAMP,
IN IN_REMITEM VARCHAR(300),
IN IN_STATE CHARACTER(1),
IN IN_TIMESTAMP TIMESTAMP
)
BEGIN

declare V_RESULT BIGINT; --声明变量
DELETE FROM TableNameA WHERE ID = IN_ID;

SET V_RESULT = NULL; --为变量赋值
  --检查用户输入的信息是否合法

select b.id INTO V_RESULT from TableNameB b,TableNameC c where 正常的判断条件
if(V_RESULT IS NOT NULL) then ---如果合法,执行下面的insert语句
INSERT INTO TableNameA(ID,ENTNAME,REGNO,PASSWORD,LEREP,CERTYPE,CERNO,LINKMAN,SEX,MOBTEL,REQDATE,REMITEM,STATE,TIMESTAMP)
VALUES(IN_ID,IN_ENTNAME,IN_REGNO,IN_PASSWORD,IN_LEREP,IN_CERTYPE,IN_CERNO,IN_LINKMAN,IN_SEX,IN_MOBTEL,IN_REQDATE,IN_REMITEM,IN_STATE,IN_TIMESTAMP);
end if;
commit;
END
@
复制代码

功能说明:

调用存储过程时会传入一些值(IN输入参数),然后根据传入的值查询数据库(select语句),根据查询结果执行操作(添加、删除、更新)

有两种方式执行写好的存储过程:

    1.拷贝到DB2客户端工具中直接执行

  特别注意:执行时将

改成@,之前很多错误都和它有关,比如:“该命令被当作 SQL
语句来处理,因为它不是有效的命令行处理器命令”正
是这个问题花费了很长时间,严重影响心情

    2.将上面的语句保存为test.db2文件放到任意目录下(比如D盘根目录),然后在cmd输入db2cmd 然后输入db2 -td@ -vf  D:\test.db2即可

 执行后就可以测试存储过程写的是否正确

 直接写sql:

call PLName(存储过程名字) (IN_ID,IN_ENTNAME,IN_REGNO,IN_PASSWORD,IN_LEREP,IN_CERTYPE,IN_CERNO,IN_LINKMAN,IN_SEX,IN_MOBTEL,IN_REQDATE,IN_REMITEM,IN_STATE,IN_TIMESTAMP对应的值)

以上就是我今天所用到的存储过程,功能非常简单,比较复杂的操作也在摸索阶段,有什么疑问大家可以随时交流。

CREATE PROCEDURE  KJZB.ZQINVEST_JT(out returnCode Integer,out 
errorMsg varchar(255)) 
LANGUAGE SQL 
BEGIN 
declare sql_code integer default 0; 
    declare SQLSTATE char(5) default '00000'; 
    declare SQLCODE integer default 0; 
    declare sqlMsg varchar(200) default ''; 
    declare stmt varchar(1024); 

    declare vCurdate    varchar(20);                   -- 当天日期 
    declare vCurYMd     varchar(6);                    -- 当天所在的年月 
    declare vCurDay  varchar(2) ;                         --所在天 
    declare maxday      integer default 30;            -- 每个月按30天算 
    declare fday         integer;                      -- 实际天数 
    declare lastday      integer;                      -- 到期日的天数 
    declare firstday     integer;                      -- 购买月的天数 
    declare firstmonth   integer;                      -- 购买的月份 
    declare lastmonth    integer;                      -- 到期日的月份 
    declare firstyear    integer;                      -- 购买日的年份 
    declare lastyear     integer;                      -- 到期年份 
    declare disday       integer;                      -- 间隔天数 
    declare dismonth     integer;                      -- 间隔月份 
    declare disyear      integer;                      -- 间隔年份 
    declare vLsh         varchar(32);                  -- 流水号 
    declare vjgh         varchar(8);                    -- 机构码 
    declare ywzh       varchar(32);                     -- 业务帐号 
    declare vzh         varchar(32);                     -- 投资账号 
    declare vJgbm       varchar(8) default '32022300';   -- 投资帐号对应的机构(默认为清算中心)

   declare vDfJgbm     varchar(8);                      -- 对方机构号 
    declare gmrq        varchar(8);                      -- 购买日期 
    declare dqrq        varchar(8);                      -- 到期日期 
    declare zqpz        varchar(1);                      -- 债券品种 
    declare hth         varchar(16);                     -- 合同号 
    declare fxfs        varchar(1);                      -- 付息方式 
    declare ywlx        varchar(3) default '888';        -- 业务类型(默认业务类型 888) 
    declare tzqx        varchar(1);                      -- 投资期限 
    declare ysjlxkmkzz  varchar(7);                      -- 应收(计)利息科目控制字 
    declare tzsykmmkzz  varchar(7);                      -- 投资收益科目控制字 
    declare yjkmkzz     varchar(7) default '142112';     -- 溢价科目控制字 长期投资(溢价142112) 
    declare zjkmkzz     varchar(7) default '142113';     -- 折价科目控制字 长期投资(折价142113) 
    declare fykmkzz     varchar(7) default '142111';     -- 相关费用科目控制字 长期投资(相关费用 142111) 
    declare zid         varchar(32) default '0';         -- id 债券投资ID 
    declare vId         varchar(32);                     -- id 债券流水ID 
    declare yslx        double;                       -- 应收利息 (面值 * 票面利率 (年) /360 * 当月实际天数) 
    declare yjlx        double;                       -- 应计利息 (面值 * 票面利率 (年) /360 * 当月实际天数) 
    declare ysjlx       double;                       -- 应收计利息 
    declare pmje        double;                       -- 票面金额 
    declare pmll        double;                       -- 票面利率 
    declare tzsy        DECIMAL(12, 2);                -- 投资收益       ( 应收(计)利息 - 每月摊销的溢价 - 每月摊销相关费用 ) 
    declare txyj        double;                       -- 月摊销溢价     ( 溢价/(到期日 - 购买日 + 1) * 当月实际天数 ) 
    declare txzj        double;                       -- 月摊销折价     ( 折价/(到期日 - 购买日 + 1) * 当月实际天数 ) 
    declare txfy        double;                       -- 月摊销费用     (相关费用)/(到期日 - 购买日 + 1) * 当月实际天数 
    declare yj          double default 0.0;           -- 溢价 
    declare zj          double default 0.0;           -- 折价 
    declare fy          double default 0.0;           -- 相关费用 

 

    declare zqtz cursor for s1;   建立游标
    -- 声明异常 
    DECLARE CONTINUE HANDLER FOR NOT FOUND,SQLEXCEPTION,SQLWARNING 
    begin 
       set sql_code   = SQLCODE; 
       set returnCode = sql_code; 
    end; 

    set vCurdate = (select char(current_date) from sysibm.sysdummy1); 
    set vCurdate = replace(vCurdate,'-',''); 
    set vCurYMd  = substr(vCurdate,1,6); 
    set vCurDay  = substr(vCurdate,7,2); 

   -- 读取还没到期的长期债券投资信息 
   set  stmt = ' select JGBM,ZH,HTH,ZQPZ,PMJE,LL,GMRQ,DQR,YSLX,YJLX,YJ,ZJ,FY,FXFS,id,cast(substr(GMRQ,7,2) as integer),cast(substr(DQR,7,2) as integer),cast(substr(GMRQ,5,2) as integer),cast(substr(DQR,5,2) as integer),cast(substr(GMRQ,1,4) as integer),cast(substr(DQR,1,4) as integer)  from kjzb.zqinvest where substr(dqr,1,6) >=''' ||  vCurYMd  || ''' and tzqx = ''2'' and tzzt <> ''2'''; 
   prepare s1 from stmt; 

    open zqtz; 
    fetch zqtz into vDfJgbm,vzh,hth,zqpz,pmje,pmll,gmrq,dqrq,yslx,yjlx,yj,zj,fy,fxfs,zid,firstday,lastday,firstmonth,lastmonth,firstyear,lastyear; 
    while sql_code <> 100 do 
        -- 如果是31日购买的,第一个月不计提 
        if ( firstday = 31 and substr(gmrq,1,6) = vCurYMd ) then 
            goto EXIT; 
        end if; 

-- 如果是01日到期的,最后一个月不计提 
if ( lastday = 1 and substr(dqrq,1,6) = vCurYMd ) then 
    goto EXIT; 
end if; 

        -- 间隔年份 
        if ( firstyear = lastyear ) then 
            set disyear = 0; 
        else 
            set disyear = lastyear - firstyear - 1; 
        end if; 

        -- 同年 
        if ( disyear = 0 ) then 
            -- 下个月或同月(业务逻辑上不会发生,但程序上要判断防止业务人员误操作) 
            if ( lastmonth - firstmonth <= 1 ) then 
            set dismonth = 0; 
            else 
                set dismonth = lastmonth - firstmonth - 1; 
            end if; 
        else 
            -- 不在同一年,间隔月份 = 第一年的月份 + 中间年的月份 + 最后一年的月份 
            set dismonth = ( 12 - firstmonth ) + ( disyear * 12 ) + ( lastmonth - 1 ) ; 
        end if; 
   -- 初始化摊销溢价、摊销折价、摊销费用 
        set txyj = 0.0; 
        set txzj = 0.0; 
        set txfy = 0.0; 
     if  fxfs  = '5'  then                              -- 根据付息方式来计应计利息还是应收利息科目控制字 
      if zqpz = '1' then 
      set ysjlxkmkzz = '142107';               -- 应计利息 国债     142107 
      set tzsykmmkzz = '514111';               -- 投资收益(债券利息收入-国债利息收入)   514111 
      end if; 
      if zqpz = '2' then 
      set ysjlxkmkzz = '142108';               -- 应计利息 金融债   142108 
      set tzsykmmkzz = '514112';               -- 投资收益(债券利息收入-金融债券利息收入) 514112 
      end if; 
      if zqpz = '3' then 
      set ysjlxkmkzz = '142109';            -- 应计利息 公司债   142109 
      set tzsykmmkzz = '514113';               -- 债券利息收入-企业债券利息收入 514113 
      end if; 
      if zqpz = '4' then 
      set ysjlxkmkzz = '142110';            -- 应计利息 其他债券 142110 
      set tzsykmmkzz = '514114';               -- 债券利息收入-其他债券利息收入   514114 
      end if; 
   else 
       if zqpz = '1' then 
      set ysjlxkmkzz = '132102';               -- 应收利息 国债     132102 
      set tzsykmmkzz = '514111';               -- 投资收益(债券利息收入-国债利息收入)   514111 
      end if; 
      if zqpz = '2' then 
      set ysjlxkmkzz = '132103';               -- 应收利息 金融债   132103 
      set tzsykmmkzz = '514112';               -- 投资收益(债券利息收入-金融债券利息收入) 514112      
      end if; 
      if zqpz = '3' then 
      set ysjlxkmkzz = '132104';            -- 应收利息 公司债   132104 
   set tzsykmmkzz = '514113';               -- 债券利息收入-企业债券利息收入 514113     
      end if; 
      if zqpz = '4' then 
      set ysjlxkmkzz = '132105';            -- 应收利息 其他债券 132105 
      set tzsykmmkzz = '514114';               -- 债券利息收入-其他债券利息收入   514114 
      end if;       
   end if; 
  
   if ( substr(dqrq,1,6) = vCurYMd ) then           -- 最后一个月计提 
       set fday = lastday - 1; 
   else 
       if ( substr(gmrq,1,6) = vCurYMd ) then       -- 第一个月计提 
   set fday = 30 - firstday + 1; 
       else 
       set fday = maxday;                        -- 平常按30天算 
       end if; 
   end if; 
-- 实际天数 = 第一个月的天数 + 间隔月份 * 30 + 最后一个月的天数(到期日不算) 
        set disday =  ( 30 - firstday + 1) + ( dismonth * 30 ) + ( lastday - 1 );  
   
        --  记提公式:面值*票面利率(年)/ 360*当月实际天数 
        set ysjlx = pmje * (pmll / 100) /360 * fday; 
        -- 投资收益公式 : 应收(计)利息 ( + 月摊销折价)— 月摊销溢价 - 月摊销费用 
        set tzsy = ysjlx; 
        -- 设计思想为如果有溢价,则应该没有折价 
       if yj <> 0.0 then 
           set txyj = yj /disday * fday; 
           set tzsy = tzsy - txyj; 
       else 
           if zj <> 0.0 then 
set txzj = zj/disday* fday; 
set tzsy = tzsy + txzj;      
           end if; 
 if fy <> 0.0 then 
           set txfy = fy /disday * fday; 
           set tzsy = tzsy - txfy; 
       end if; 
        --借: 1321 应收利息[按月计提金额] 
   set vId =  kjzb.getvId(); 
   set vLsh = kjzb.getLsh(32);                       -- 获取流水号 
        set vjgh = kjzb.getJgh(vzh);                      -- 获取机构号 
        set ywzh = kjzb.getZh(vjgh,ysjlxkmkzz);           -- 应收(计)利息账号 
        if ywzh is null then 
               set sqlMsg = '债券记提走帐时找不到指定机构['||vjgh||']和科目控制字为['|| ysjlxkmkzz ||']的帐号'; 
               set errorMsg =  sqlMsg; 
               goto ERROR_RETURN; 
        end if; 
        insert into kjzb.FLLSB values(vId,vJgbm,ysjlxkmkzz,vLsh,hth,ywzh,'借',ysjlx,'','0000',vCurdate,ywlx); 
        if sql_code <> 0 then 
           set sqlMsg = '债券走帐插入会计分录出错'; 
           set errorMsg =  sqlMsg; 
           goto ERROR_RETURN; 
        end if; 
   
   -- 借: 1421 长期投资(折价)[按月计提金额] 
   if zj <> 0.0 then 
      set vId =  kjzb.getvId(); 
           set ywzh = kjzb.getZh(vjgh,zjkmkzz);          -- 长期投资(折价)账号 
   if ywzh is null then 
       set sqlMsg = '债券记提走帐时找不到指定机构['||vjgh||']和科目控制字为['|| zjkmkzz ||']的帐号'; 
       set errorMsg =  sqlMsg; 
    goto ERROR_RETURN; 
   end if; 
           insert into kjzb.FLLSB values(vId,vJgbm,zjkmkzz,vLsh,hth,ywzh,'借',txzj,'','0000',vCurdate,ywlx); 
           if sql_code <> 0 then 
             set sqlMsg = '债券走帐插入会计分录出错'; 
             set errorMsg =  sqlMsg; 
             goto ERROR_RETURN; 
           end if; 
   end if; 
   
        -- 贷: 5141 投资收益  应收(计)利息-每月摊消的溢价-每月摊消相关费用 
   set vId =  kjzb.getvId(); 
        set ywzh = kjzb.getZh(vjgh,tzsykmmkzz);           -- 投资收益账号 
if ywzh is null then 
     set sqlMsg = '债券记提走帐时找不到指定机构['||vjgh||']和科目控制字为['|| tzsykmmkzz ||']的帐号'; 
    set errorMsg =  sqlMsg; 
    goto ERROR_RETURN; 
end if; 
        insert into kjzb.FLLSB values(vId,vJgbm,tzsykmmkzz,vLsh,hth,ywzh,'贷',tzsy,'','0000',vCurdate,ywlx); 
        if sql_code <> 0 then 
           set sqlMsg = '债券走帐插入会计分录出错'; 
           set errorMsg =  sqlMsg; 
           goto ERROR_RETURN; 
        end if; 
        -- 贷: 1421 长期投资(溢价)[按月摊消]每月摊消的溢价(溢价/(到期日-购买日+1) *当月实际天数) 
        if yj <> 0.0 then 
      set vId =  kjzb.getvId(); 
        set ywzh = kjzb.getZh(vjgh,yjkmkzz);              -- 长期投资(溢价)账号 
if ywzh is null then 
    set sqlMsg = '债券记提走帐时找不到指定机构['||vjgh||']和科目控制字为['|| yjkmkzz ||']的帐号'; 
    set errorMsg =  sqlMsg; 
    goto ERROR_RETURN; 
end if; 
        insert into kjzb.FLLSB values(vId,vJgbm,yjkmkzz,vLsh,hth,ywzh,'贷',txyj,'','0000',vCurdate,ywlx); 
        if sql_code <> 0 then 
           set sqlMsg = '债券走帐插入会计分录出错'; 
           set errorMsg =  sqlMsg; 
           goto ERROR_RETURN; 
        end if; 
        end if; 
        -- 贷: 1421 长期投资(相关费用)[按月摊消金额] 每月摊消相关费用(相关费用/(到期日-购买日+1) *当月实际天数) 
        if (fy <> 0.0)   then 
        set vId =  kjzb.getvId(); 
        set ywzh = kjzb.getZh(vjgh,fykmkzz);              -- 长期投资(相关费用)账号 
if ywzh is null then 
    set sqlMsg = '债券记提走帐时找不到指定机构['||vjgh||']和科目控制字为['|| fykmkzz ||']的帐号'; 
    set errorMsg =  sqlMsg; 
    goto ERROR_RETURN; 
end if; 
        insert into kjzb.FLLSB values(vId,vJgbm,yjkmkzz,vLsh,hth,ywzh,'贷',txfy,'','0000',vCurdate,ywlx); 
        if sql_code <> 0 then 
           set sqlMsg = '债券走帐插入会计分录出错'; 
           set errorMsg =  sqlMsg; 
           goto ERROR_RETURN; 
       end if; 
        end if; 
   
   -- 对 kjzb.zqinvest 表的以下字段进行更新 
   -- yjtyjlx 【已计提应计利息】 
        -- yjtyslx 【已计提应收利息】 
        -- ytxyj   【已摊销溢价】 
        -- ytxzj   【已摊销折价】 
  if fxfs = '5' then 
            update kjzb.zqinvest set yjtyjlx = yjtyjlx + ysjlx,ytxyj = ytxyj + txyj,ytxzj = ytxzj + txzj,ytxfy = ytxfy + txfy where id = zid; 
            if sql_code <> 0 then 
        set sqlMsg = '债券走帐时更新表出错'; 
        set errorMsg =  sqlMsg; 
        goto ERROR_RETURN; 
           end if; 
        else 
            update kjzb.zqinvest set yjtyslx = yjtyslx + ysjlx,ytxyj = ytxyj + txyj,ytxzj = ytxzj + txzj,ytxfy = ytxfy + txfy where id = zid; 
            if sql_code <> 0 then 
        set sqlMsg = '债券走帐时更新表出错'; 
        set errorMsg =  sqlMsg; 
        goto ERROR_RETURN; 
           end if; 
       end if; 
   
        EXIT: 
         fetch zqtz into vDfJgbm,vzh,hth,zqpz,pmje,pmll,gmrq,dqrq,yslx,yjlx,yj,zj,fy,fxfs,zid,firstday,lastday,firstmonth,lastmonth,firstyear,lastyear; 
    end while; 
    close zqtz;                                    -- 关闭游标 
    COMMIT; 
    set sqlMsg   = '债券投资自动计提成功!'; 
    set errorMsg =  sqlMsg; 
    set sql_code = 0; 
    set returnCode = sql_code; 
    return 1; 
    ERROR_RETURN: 
        ROLLBACK; 
        return -1; 

 








====================================分割线================================



最新内容请见作者的GitHub页:http://qaseven.github.io/

目录
相关文章
|
5月前
|
存储 SQL 关系型数据库
MySQL技能完整学习列表7、存储过程和函数——1、存储过程(Stored Procedures)的创建和执行——2、函数(Functions)的创建和使用
MySQL技能完整学习列表7、存储过程和函数——1、存储过程(Stored Procedures)的创建和执行——2、函数(Functions)的创建和使用
65 0
|
4月前
|
存储
Cloudstack启动实例模板从二级存储复制到主存储过程
Cloudstack启动实例模板从二级存储复制到主存储过程
|
11月前
|
存储 关系型数据库 MySQL
MySQL存储过程学习总结
MySQL存储过程学习总结
58 0
|
11月前
|
存储 关系型数据库 MySQL
MySQL定时任务存储过程函数和动态表名实例
MySQL定时任务存储过程函数和动态表名实例
|
存储 关系型数据库 MySQL
MySQL存储过程中双层游标嵌套循环实例
MySQL存储过程中双层游标嵌套循环实例
|
存储 SQL 安全
15天学习MySQL计划-存储过程变量判断循环游标函数触发器(进阶篇)-第九天(下)
15天学习MySQL计划-存储过程变量判断循环游标函数触发器(进阶篇)-第九天
139 0
|
存储 SQL 关系型数据库
15天学习MySQL计划-存储过程变量判断循环游标函数触发器(进阶篇)-第九天(上)
15天学习MySQL计划-存储过程变量判断循环游标函数触发器(进阶篇)-第九天
135 0
|
存储 Oracle 关系型数据库
Oracle 存储过程学习总结
Oracle 存储过程学习总结
125 0
|
存储 SQL 关系型数据库
SQL 存储过程 procedure 讲解+代码实例
SQL 存储过程 procedure 讲解+代码实例
SQL 存储过程 procedure 讲解+代码实例
|
存储 数据安全/隐私保护 索引
Mysql8.0习题系列(六):存储过程(一篇学习存储过程做题,超详细)
Mysql8.0习题系列(六):存储过程(一篇学习存储过程做题,超详细)
下一篇
无影云桌面