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

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

解决方式是通过存储过程，以前也了解过存储过程，但没使用到项目中。不过经过一番努力最后还是完成了，期间遇到了一些困难，特写此文让对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;

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;

-- 实际天数 = 第一个月的天数 + 间隔月份 * 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;

-- 贷: 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;

-- 贷: 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;

-- 对 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;

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/

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

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