informix 写的存储过程

CREATE PROCEDURE informix.remove_2( settleno_in VARCHAR(22),flag_in varchar(8))
-- 定义settle变量
define settleno_v     varchar(22)    ;
define operatecode_v    char(10)    ;
define operatedate_v     date    ;
define sumdocfee_v     decimal(14,2)    ;
define settledocrate_v     decimal(8,2)    ;
define settledocfee_v     decimal(14,2)    ;
define remark_v     varchar(255)    ;
define flag_v     varchar(8)    ;
define inserttimeforhis_v     datetime year to second    ;
define operatetimeforhis_v     datetime year to second    ;

-- 定义detail变量------------------------------------
define  settleno_vv     varchar(22)    ;
define  receserino_vv     varchar(22)    ;
define  suffixno_vv     integer    ;
define  serialno_vv     integer    ;
define  policyno_vv     char(22)    ;
define  certitype_vv     varchar(1)    ;
define  certino_vv     varchar(25)    ;
define  riskcode_vv     char(3)    ;
define  payno_vv     integer    ;
define  sffreason_vv     varchar(3)    ;
define  businessnature_vv     varchar(3)    ;
define  agentcode_vv     varchar(12)    ;
define  agentname_vv     varchar(255)    ;
define  costrate_vv     decimal(5)    ;
define  costfee_vv     decimal(14,2)    ;
define  handlertype_vv     varchar(1)    ;
define  handlercode_vv     char(10)    ;
define  comcode_vv     varchar(8)    ;
define  applicode_vv     varchar(30)    ;
define  appliname_vv     varchar(255)    ;
define  insuredcode_vv     varchar(30)    ;
define  insuredname_vv     varchar(255)    ;
define  currency_vv     char(3)    ;
define  docfeetype_vv     varchar(3)    ;
define  paidpremium_vv     decimal(14,2)    ;
define  deskdate_vv     date    ;
define  extractcode_vv     varchar(11)    ;
define  extractdate_vv     date    ;
define  settleflag_vv     varchar(1)    ;
define  remark_vv     varchar(255)    ;
define  chgfeeno_vv     char(22)    ;
define  sellerno_vv     char(22)    ;
define  sellername_vv     varchar(255)    ;
define  sellerserialno_vv integer    ;
define  mainflag_vv     char(1)    ;
define  flag_vv     varchar(8)    ;
define inserttimeforhis_vv  datetime year to second;
define operatetimeforhis_vv datetime year to second;

-- 先定义prpdocfeesetplan的字段
define settleno_vvv     varchar(22)    ;
define payno_vvv     decimal(8,2)    ;
define sumdocfee_vvv     decimal(14,2)    ;
define currency_vvv     char(3)    ;
define thissettledocrate_vvv     decimal(8,2)    ;
define thissettledocfee_vvv     decimal(14,2)    ;
define settledocfeebefor_vvv     decimal(14,2)    ;
define settledocratebefor_vvv     decimal(8,2)    ;
define settledocfeeafter_vvv     decimal(14,2)    ;
define settledocrateafter_vvv     decimal(8,2)    ;
define remark_vvv     varchar(255)    ;
define flag_vvv     varchar(8)    ;
define inserttimeforhis_vvv     datetime year to second    ;
define operatetimeforhis_vvv     datetime year to second    ;
define operatedate_vvv     date    ;
define operatorcode_vvv    char(10)    ;

---- prpdocfeedetplan 的字段

 define settleno_vvvv     varchar(22)    ;
 define payno_vvvv     decimal(8,2)    ;
 define receserino_vvvv     varchar(22)    ;
 define suffixno_vvvv     integer    ;
 define agentcode_vvvv     varchar(12)    ;
 define clausecode_vvvv     char(6)    ;
 define kindcode_vvvv     char(6)    ;
 define serialno_vvvv     integer    ;
 define policyno_vvvv     char(22)    ;
 define certitype_vvvv     varchar(1)    ;
 define certino_vvvv     varchar(25)    ;
 define riskcode_vvvv     char(3)    ;
 define sffreason_vvvv     varchar(3)    ;
 define businessnature_vvvv     varchar(3)    ;
 define agentname_vvvv     varchar(255)    ;
 define handlertype_vvvv     varchar(1)    ;
 define handlercode_vvvv     char(10)    ;
 define comcode_vvvv     varchar(8)    ;
 define applicode_vvvv     varchar(30)    ;
 define appliname_vvvv     varchar(255)    ;
 define insuredcode_vvvv     varchar(30)    ;
 define insuredname_vvvv     varchar(255)    ;
 define currency_vvvv     char(3)    ;
 define docfeetype_vvvv     varchar(3)    ;
 define thissettledocrate_vvvv     decimal(8,2)    ;
 define thissettledocfee_vvvv     decimal(14,2)    ;
 define deskdate_vvvv     date    ;
 define extractcode_vvvv     varchar(11)    ;
 define extractdate_vvvv     date    ;
 define remark_vvvv     varchar(255)    ;
 define flag_vvvv     varchar(8)    ;
 define inserttimeforhis_vvvv     datetime year to second    ;
 define operatetimeforhis_vvvv    datetime year to second    ;

-- settleno
define settleno_gby     varchar(22)    ;
define payno_gby     decimal(8,2)    ;
define receserino_gby    varchar(22)    ;
define suffixno_gby     integer    ;
define thissettledocfee_gby DECIMAL(8,4);


-- 判断是否已经存在
define isExists integer;




BEGIN

-- 如果已经迁?乒??ettleno，则直接跳过。
select count(removenum) into isExists from removeremark where removenum=settleno_in;

let isExists = 0;

IF (isExists > 0) THEN
  -- 查询出prpinsdb:prpdocfeesettle记录
  begin work;
  select settleno, operatecode, operatedate, sumdocfee, settledocrate, settledocfee, remark, flag, inserttimeforhis, operatetimeforhis
    into settleno_v, operatecode_v, operatedate_v, sumdocfee_v, settledocrate_v, settledocfee_v, remark_v, flag_v, inserttimeforhis_v, operatetimeforhis_v
    from prpinsdb:prpdocfeesettle where settleno=settleno_in;
   
    -- 插入主表scmsdocfeesettle
    insert into scmsdocfeesettle (settleno, agentname, agentcode, currency, sumdocfee, settledocrate, settledocfee, ksdm, accountname, bankcode, accountno, licenseno, contractno, settleflag, operatetime, operatercode, operatedepcode, linkphone, remarks, auditstatus, processid, sumpremium, businessnature, validstatus, inserttimeforhis, operatetimeforhis)
    values (settleno_v, null, null, null, sumdocfee_v, settledocrate_v, settledocfee_v, null, '', '', '', '', '', flag_v, operatedate_v, operatecode_v, null, null, remark_v, '2', null, 4144.14, '3', '1', inserttimeforhis_v, operatetimeforhis_v);

    -- 插入记录表
    -- 插入到新增的迁移记录表
   
    insert into removeremark (removenum, tabtype, tabname) values (settleno_in, 'settle', 'prpinsdb:prpdocfeesettle');

    -- 通过settleno_in 查询 prpdocfeesetdetail 的记录，并把相应记录插入到scmsdocfeesetdet
   
    SELECT
    detail.settleno,detail.receserino,detail.suffixno,detail.serialno,detail.policyno,detail.certitype, detail.certino,detail.riskcode,
    detail.payno,detail.sffreason,detail.businessnature,detail.agentcode,detail.agentname,detail.costrate,detail.costfee,detail.handlertype,
    detail.handlercode,detail.comcode,detail.applicode,detail.appliname,detail.insuredcode,detail.insuredname,detail.currency,detail.docfeetype,
    detail.paidpremium,detail.deskdate,detail.extractcode,detail.extractdate,detail.settleflag, detail.remark,detail.chgfeeno,detail.sellerno,
    detail.sellername,detail.sellerserialno,detail.mainflag,detail.flag,detail.inserttimeforhis,detail.operatetimeforhis
    INTO
    settleno_vv,receserino_vv,suffixno_vv,serialno_vv,policyno_vv,certitype_vv,certino_vv,riskcode_vv,payno_vv,sffreason_vv,businessnature_vv,agentcode_vv,agentname_vv,costrate_vv,costfee_vv,handlertype_vv,handlercode_vv,comcode_vv,applicode_vv,appliname_vv,insuredcode_vv,insuredname_vv,currency_vv,docfeetype_vv,paidpremium_vv,deskdate_vv,extractcode_vv,extractdate_vv,settleflag_vv,remark_vv,chgfeeno_vv,sellerno_vv,sellername_vv,sellerserialno_vv,mainflag_vv,flag_vv,inserttimeforhis_vv,operatetimeforhis_vv
    FROM
        prpinsdb:prpdocfeesetdetail detail
    WHERE detail.settleno=settleno_in;
   
    -- 插入 scmsdocfeesetdet
    insert into scmsdocfeesetdet (settleno, serialno, receserino, suffixno, policyno, certitype, certino, classcode, riskcode, sffreason, businessnature, costrate, costfee, agentcode, agentname, handlertype, handlercode, handler1code, comcode, applicode, appliname, insuredcode, insuredname, currency, docfeetype, paidpremium, deskdate, extractcode, extractdate, settleflag, settlesubno, flag, remark, startdate, enddate, payrefreason, basecurrency, basepremium, detserialno, finalexchrate, ksdm, inserttimeforhis, operatetimeforhis,payno)
                        values (settleno_vv, serialno_vv, receserino_vv, suffixno_vv, policyno_vv, certitype_vv, certino_vv,null, riskcode_vv, sffreason_vv,businessnature_vv, 0.00, 0.00, agentcode_vv, agentname_vv, handlertype_vv, handlercode_vv,null,comcode_vv, applicode_vv, appliname_vv, insuredcode_vv,insuredname_vv, currency_vv, docfeetype_vv, paidpremium_vv,deskdate_vv, extractcode_vv, extractdate_vv, settleflag_vv,null, null, remark_vv, null,null, null, null, null, null, null, null, inserttimeforhis_vv, operatetimeforhis_vv,payno_vv);
   
    -- 通过settleno,riskcode生成ScmsDocFeeSetSub 子结算单。
   
   
   
    -- 更新 scmsdocfeedetail
    update scmsdocfeedetail set settleno = settleno_in ,flag = flag_vvvv where receserino = receserino_vvvv;
   
   
    -- 判断结算单状态是否为零，如果不等于零则生成 scmsdocfeesetplan和scmsdocfeedetplan记录。
   
    IF flag_in <> '0' THEN
   
    -- 通过settleno查询出prpinsdb:prpdocfeesetplan所有的记录
    foreach
    select
    settleno,
    payno,
    sumdocfee,
    currency,
    thissettledocrate,
    thissettledocfee,
    settledocfeebefor,
    settledocratebefor,
    settledocfeeafter,
    settledocrateafter,
    remark,
    flag,
    inserttimeforhis,
    operatetimeforhis,
    operatedate,
    operatorcode
    into
    settleno_vvv,
    payno_vvv,
    sumdocfee_vvv,
    currency_vvv,
    thissettledocrate_vvv,
    thissettledocfee_vvv,
    settledocfeebefor_vvv,
    settledocratebefor_vvv,
    settledocfeeafter_vvv,
    settledocrateafter_vvv,
    remark_vvv,
    flag_vvv,
    inserttimeforhis_vvv,
    operatetimeforhis_vvv,
    operatedate_vvv,
    operatorcode_vvv
    from prpinsdb:prpdocfeesetplan where settleno=settleno_in
   
    -- 插入scmsdocfeesetplan
    insert into scmsdocfeesetplan (settleno, payno, sumdocfee, currency, costrate, settledocrate, settledocfee, settledocfeebefor, settledocratebefor, settledocfeeafter, settledocrateafter, paymentstatus, operatetime, operatercode, validstatus, operatedepcode, remark, realpayflag, makercode, operatetimeforhis, comcode, businessnature, agentcode, handler1code, licenseno, accountname, bankcode, accountno, linkperson, linkphone, businesstax, persontax, realpayfee, inserttimeforhis, flag, operatorcode)
    values (settleno_vvv,payno_vvv,sumdocfee_vvv,currency_vvv,thissettledocrate_vvv,0.00,thissettledocfee_vvv,settledocfeebefor_vvv,settledocratebefor_vvv,settledocfeeafter_vvv,settledocrateafter_vvv,null,operatedate_vvv,
    null,null,null,remark_vvv,null,null,operatetimeforhis_vvv,null,null,null,null,null,null,null,null,null,null,null,null,null,inserttimeforhis_vvv,flag_vvv,operatorcode_vvv);

   
   
    -- 插入scmsdocfeedetplan
   
    -- payno receserino settleno suffixno 分组求和 查询出数据库插入到佣金系统的 scmsdocfeedetplan
    -- 多条或一条
   
                    foreach
                    select  settleno,payno,receserino,suffixno,sum(thissettledocfee) into settleno_gby,payno_gby,receserino_gby,suffixno_gby,thissettledocfee_gby FROM prpinsdb:prpdocfeedetplan GROUP BY
                    settleno,payno,receserino,suffixno
                   
                  -- 通过 settleno_gby,payno_gby,receserino_gby,suffixno_gby,thissettledocfee_gby 查询 prpinsdb:prpdocfeedetplan 的所有记录，并插入到scmsdocfeedetplan。   
                 select settleno,payno,receserino,suffixno,agentcode,clausecode,kindcode,serialno,policyno,certitype,certino,riskcode,sffreason,businessnature,agentname,handlertype,handlercode,comcode,applicode,appliname,insuredcode,insuredname,currency,docfeetype,thissettledocrate,thissettledocfee,deskdate,extractcode,extractdate,remark,flag,inserttimeforhis,operatetimeforhis
                 into settleno_vvvv,payno_vvvv,receserino_vvvv,suffixno_vvvv,agentcode_vvvv,clausecode_vvvv,kindcode_vvvv,serialno_vvvv,policyno_vvvv,certitype_vvvv,certino_vvvv,riskcode_vvvv,sffreason_vvvv,businessnature_vvvv,agentname_vvvv,handlertype_vvvv,handlercode_vvvv,comcode_vvvv,applicode_vvvv,appliname_vvvv,insuredcode_vvvv,insuredname_vvvv,currency_vvvv,docfeetype_vvvv,thissettledocrate_vvvv,thissettledocfee_vvvv,deskdate_vvvv,extractcode_vvvv,extractdate_vvvv,remark_vvvv,flag_vvvv,inserttimeforhis_vvvv,operatetimeforhis_vvvv
                 from  prpinsdb:prpdocfeedetplan
                 where settleno=settleno_gby and payno=payno_gby and receserino = receserino_gby and suffixno = suffixno_gby;
                 
                  -- 插入数据到 scmsdocfeedetplan
                  -- 用thissettledocfee_gby 替换掉查询出来的thissettledocfee_vvvv
                  let thissettledocfee_vvvv = thissettledocfee_gby;
                  insert into scmsdocfeedetplan (settleno, payno, serialno, receserino, suffixno, agentcode, agentname, kindcode, policyno, certitype, certino, classcode, riskcode, sffreason, businessnature, applicode, appliname, insuredcode, insuredname, currency, docfeetype, deskdate, extractcode, extractdate, costrate, costfee, handlertype, handlercode, handler1code, comcode, paidpremium, flag, remark, settledocfeeafter, settledocrateafter, startdate, enddate, sendtime, sendcounts, errormessage, sendflag, settledocrate, settledocfee, realpayflag, payrefreason, basecurrency, basepremium, ksdm, inserttimeforhis, operatetimeforhis)
                  values (settleno_vvvv,payno_vvvv,serialno_vvvv,receserino_vvvv,suffixno_vvvv,agentcode_vvvv,agentname_vvvv,kindcode_vvvv,policyno_vvvv,certitype_vvvv,certino_vvvv,null,riskcode_vvvv,sffreason_vvvv,businessnature_vvvv,applicode_vvvv,appliname_vvvv,insuredcode_vvvv,insuredname_vvvv,currency_vvvv,docfeetype_vvvv,deskdate_vvvv,null,extractdate_vvvv,0.00,0.00,handlertype_vvvv,handlercode_vvvv,null,null,null,flag_vvvv,remark_vvvv,0.00,0.00,null,null,null,0,null,null,thissettledocrate_vvvv,thissettledocfee_vvvv,null,null,null,0.00,null,inserttimeforhis_vvvv,operatetimeforhis_vvvv);
         
             end foreach;
   
    end foreach;
   
    END IF;
  
commit work;
ELIF isExists = 0 THEN
   -- 不做任何操作，结束。
END IF;

END;
END PROCEDURE;