`

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;                                                                                                                                                                    

分享到:
评论

相关推荐

    INFORMIX存储过程手册

    ### INFORMIX存储过程手册概览 #### 存储过程概念与作用 存储过程是一种预编译的SQL脚本或程序,它驻留在数据库中,能够执行一系列复杂的数据库操作,如数据检索、更新、事务处理等。在Informix环境下,存储过程...

    informix_存储过程

    本文将深入探讨INFORMIX存储过程的建立、语法结构、控制语句、运行以及调试。 ### 存储过程的建立 在INFORMIX中,创建存储过程通常涉及以下步骤: 1. 使用`DROP PROCEDURE`语句删除已存在的同名存储过程,确保...

    Informix存储过程笔记

    【Informix存储过程笔记】 Informix存储过程是数据库中一种重要的程序设计元素,它允许用户在数据库中存储一系列SQL语句和SPL(Stored Procedure Language)命令,以便于执行重复的任务或实现复杂的业务逻辑。存储...

    INFORMIX存储过程编写指导书

    ### INFORMIX存储过程编写指导书 #### 编写存储过程基础 在开始编写Informix存储过程之前,了解基础的数据类型、操作语句以及函数是至关重要的。这将帮助开发者构建高效、健壮且易于维护的存储过程。 ##### 常用...

    INFORMIX最新实用存储过程编写.doc

    Informix存储过程是数据库管理系统中的一种重要特性,它允许开发者编写一系列的SQL和控制流程语句,打包成一个可重复使用的单元,以便在需要时调用。在INFORMIX环境下,存储过程的编写涉及多个方面,包括环境配置、...

    INFORMIX存储过程编写[归类].pdf

    本指南主要针对INFORMIX存储过程的编写,涵盖了从环境准备到实际编写过程的基础知识。 首先,进行开发前的环境准备至关重要。确保你有一个正常运行的INFORMIX数据库实例,这意味着数据库服务已经启动并且能够接受...

    达梦数据库6与sinoregal ds/informix存储过程对比

    ### 达梦数据库6与Sinoregal DS/Informix存储过程对比分析 #### 概述 本文档旨在对比达梦数据库6(以下简称“DM”)与Sinoregal DS(以下简称“Sinoregal”)存储过程的异同点,通过详细阐述两者的差异,帮助读者更...

    INFORMIX与ORACLE存储过程语言之间的异同分析.pdf

    INFORMIX存储过程语言使用双连字符(--)作为单行注释的开头,多行注释以/*开头,以*/结束。ORACLE存储过程语言也使用双连字符(--)作为单行注释的开头,多行注释以/*开头,以*/结束。 二、变量定义 INFORMIX存储...

    INFORMIX和ORACLE存储过程的异同.doc

    INFORMIX和ORACLE存储过程的异同.doc

    informix procedure&trigger写法教程

    《Informix 存储过程与触发器写法详解》 Informix 数据库系统提供了一种强大的功能,即存储过程和触发器,它们是数据库管理、数据处理和业务逻辑实现的关键部分。本文将深入探讨 Informix 中存储过程和触发器的编写...

    informix数据库的文档

    指南中的内容可能涵盖DML(Data Manipulation Language)如SELECT、INSERT、UPDATE和DELETE,以及DDL(Data Definition Language)如CREATE、ALTER和DROP等语句,同时还会涉及事务处理、视图、索引和存储过程等内容...

    jave调用anywhere存储过程

    当你需要在Java应用程序中调用Anyware存储过程时,这是一个常见的任务,特别是在分布式系统或者需要与数据库进行交互的应用中。下面将详细解释如何在MyEclipse开发环境中,利用Java来调用Anywhere 9的存储过程。 ...

    Informix11.5数据压缩和存储优化

    Informix 11.5 数据压缩和存储优化的技术知识涵盖了数据库管理系统(DBMS)的存储成本削减、数据压缩技术的细节以及数据库管理的优化策略。以下详细知识点是基于给定文件的信息进行深入解析: 1. 存储成本不断上升...

    Informix数据库sqlcode出错信息

    当SQL语句成功执行时,Informix数据库服务器会将特定的SQLCODE值返回给应用程序,帮助开发人员或维护人员判断SQL语句是否正确执行,或者执行过程中发生了什么问题。下面将详细介绍SQLCODE的一些常见值及其含义,并...

    一些常用数据库存储过程

    ### 一些常用数据库存储过程 在软件开发过程中,数据库存储过程是提高应用程序性能和安全性的关键技术之一。存储过程是一组预编译好的SQL语句和控制流语句的集合,可以实现复杂的业务逻辑处理,同时减少网络通信...

    Informix 动态服务器错误代码中文详解.docx

    《Informix动态服务器错误代码中文详解》 Informix动态服务器是IBM公司的一款高性能的关系型数据库管理系统,它在处理大量数据和高并发事务方面表现出色。然而,在使用过程中,难免会遇到各种错误,这些错误通常以...

    informix学习使用手册

    书中会详细讲解这些过程,确保读者能顺利搭建Informix环境。 三、SQL语言基础 SQL是Structured Query Language的缩写,是用于管理关系数据库的标准语言。书中会详细介绍SQL的基本语法,如数据查询、插入、更新和...

    informix for windows数据库安装配置

    Informix 是 IBM 公司开发的一款关系数据库管理系统,提供了强大且灵活的数据存储和管理功能。在本文中,我们将详细介绍 Informix for Windows 数据库的安装配置过程,帮助初学者快速入门。 安装 Informix for ...

    oracle存储过程培训材料.ppt

    下面将详细阐述存储过程的建立、语法结构、控制语句、开发、运行、调试以及与Informix的差异。 1. **存储过程的建立** 创建存储过程通常使用`CREATE OR REPLACE PROCEDURE`语句。例如: ```sql CREATE OR ...

Global site tag (gtag.js) - Google Analytics