- 浏览: 957128 次
- 性别:
- 来自: 北京
文章分类
- 全部博客 (385)
- 搜索引擎学习 (62)
- 算法 (1)
- 数据库 (15)
- web开发 (38)
- solr开发 (17)
- nutch 1.2 系统学习 (8)
- cms (1)
- 系统架构 (11)
- linux 与 unix 编程 (16)
- android (15)
- maven (1)
- 关注物流 (1)
- 网址收集 (1)
- 分布式,集群 (1)
- mysql (5)
- apache (1)
- 资料文档备份 (7)
- 上班有感 (0)
- 工作流 (15)
- javascript (1)
- weblogic (1)
- eclipse 集成 (1)
- JMS (7)
- Hibernate (1)
- 性能测试 (1)
- spring (6)
- 缓存cache (1)
- mongodb (2)
- webservice (1)
- HTML5 COCOS2D-HTML5 (1)
- BrowserQuest (2)
最新评论
-
avi9111:
内陷到android, ios, winphone里面也是随便 ...
【HTML5游戏开发】二次开发 BrowserQuest 第一集 -
avi9111:
呵呵,做不下去了吧,没有第二集了吧,游戏是个深坑,谨慎进入,其 ...
【HTML5游戏开发】二次开发 BrowserQuest 第一集 -
excaliburace:
方案3亲测完全可用,顺便解决了我其他方面的一些疑问,非常感谢
spring security 2添加用户验证码 -
yuanliangding:
Spring太强大了。
Spring Data JPA 简单介绍 -
小高你好:
什么是hibernate懒加载?什么时候用懒加载?为什么要用懒加载?
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的事务、并发控制、锁机制、隔离级别
2012-04-11 17:10 54171、事务 事 ... -
用MySQL-Proxy实现读写分离【转】
2011-10-14 15:32 1270MySQL-Proxy, 6月份发布的MySQL-Proxy是 ... -
分布式数据库拆表拆库的常用策略
2011-10-12 18:21 1047分布式数据库拆表拆库 ... -
【转】 MYSQL 时间查询的范围
2011-09-09 23:31 1808对于每个类型拥有的值范围以及并且指定日期何时间值的有效格式的描 ... -
mongodb 的安装使用
2011-01-25 10:21 1591安装 OS X 32-bit ... -
linux 数据库安装
2009-12-17 21:36 32241、查看要求的安装包(和RHEL4有点小区别)rpm -q b ... -
oracle 10g 数据库 导入
2009-10-06 17:21 1870今天,把公司的数据库导出了,通过pl/sql导出来了,格式为 ... -
Oracle创建表空间、创建用户以及授权、查看权限
2009-10-06 15:55 1260创建临时表空间CREATE TEMPORARY TA ... -
产品分类设计
2009-09-20 00:02 1144网页树形结构问题的一种解决办法 在一个标准的树形 ... -
linux 操作 oracle
2009-08-20 14:58 1522linux 连接oracle [test@local ... -
linux 数据库导入 从windows一个用户导出,由系统dba导入
2009-07-31 09:08 1855linux下导入:进入linux下 ... -
oracle 日期处理
2009-06-08 14:35 1824oracle当月、当年、本周数据 当月数据 ... -
接触oracle存储过程
2009-06-04 16:16 1219这是我写的第一个存储 ... -
oracle 10g 数据库数据导入dmp
2009-05-25 21:25 1771今天从公司服务器里把数据导出来.本想导进自己的电脑里,弄了半天 ...
相关推荐
### INFORMIX存储过程手册概览 #### 存储过程概念与作用 存储过程是一种预编译的SQL脚本或程序,它驻留在数据库中,能够执行一系列复杂的数据库操作,如数据检索、更新、事务处理等。在Informix环境下,存储过程...
本文将深入探讨INFORMIX存储过程的建立、语法结构、控制语句、运行以及调试。 ### 存储过程的建立 在INFORMIX中,创建存储过程通常涉及以下步骤: 1. 使用`DROP PROCEDURE`语句删除已存在的同名存储过程,确保...
【Informix存储过程笔记】 Informix存储过程是数据库中一种重要的程序设计元素,它允许用户在数据库中存储一系列SQL语句和SPL(Stored Procedure Language)命令,以便于执行重复的任务或实现复杂的业务逻辑。存储...
### INFORMIX存储过程编写指导书 #### 编写存储过程基础 在开始编写Informix存储过程之前,了解基础的数据类型、操作语句以及函数是至关重要的。这将帮助开发者构建高效、健壮且易于维护的存储过程。 ##### 常用...
Informix存储过程是数据库管理系统中的一种重要特性,它允许开发者编写一系列的SQL和控制流程语句,打包成一个可重复使用的单元,以便在需要时调用。在INFORMIX环境下,存储过程的编写涉及多个方面,包括环境配置、...
本指南主要针对INFORMIX存储过程的编写,涵盖了从环境准备到实际编写过程的基础知识。 首先,进行开发前的环境准备至关重要。确保你有一个正常运行的INFORMIX数据库实例,这意味着数据库服务已经启动并且能够接受...
### 达梦数据库6与Sinoregal DS/Informix存储过程对比分析 #### 概述 本文档旨在对比达梦数据库6(以下简称“DM”)与Sinoregal DS(以下简称“Sinoregal”)存储过程的异同点,通过详细阐述两者的差异,帮助读者更...
INFORMIX存储过程语言使用双连字符(--)作为单行注释的开头,多行注释以/*开头,以*/结束。ORACLE存储过程语言也使用双连字符(--)作为单行注释的开头,多行注释以/*开头,以*/结束。 二、变量定义 INFORMIX存储...
INFORMIX和ORACLE存储过程的异同.doc
《Informix 存储过程与触发器写法详解》 Informix 数据库系统提供了一种强大的功能,即存储过程和触发器,它们是数据库管理、数据处理和业务逻辑实现的关键部分。本文将深入探讨 Informix 中存储过程和触发器的编写...
指南中的内容可能涵盖DML(Data Manipulation Language)如SELECT、INSERT、UPDATE和DELETE,以及DDL(Data Definition Language)如CREATE、ALTER和DROP等语句,同时还会涉及事务处理、视图、索引和存储过程等内容...
当你需要在Java应用程序中调用Anyware存储过程时,这是一个常见的任务,特别是在分布式系统或者需要与数据库进行交互的应用中。下面将详细解释如何在MyEclipse开发环境中,利用Java来调用Anywhere 9的存储过程。 ...
Informix 11.5 数据压缩和存储优化的技术知识涵盖了数据库管理系统(DBMS)的存储成本削减、数据压缩技术的细节以及数据库管理的优化策略。以下详细知识点是基于给定文件的信息进行深入解析: 1. 存储成本不断上升...
当SQL语句成功执行时,Informix数据库服务器会将特定的SQLCODE值返回给应用程序,帮助开发人员或维护人员判断SQL语句是否正确执行,或者执行过程中发生了什么问题。下面将详细介绍SQLCODE的一些常见值及其含义,并...
### 一些常用数据库存储过程 在软件开发过程中,数据库存储过程是提高应用程序性能和安全性的关键技术之一。存储过程是一组预编译好的SQL语句和控制流语句的集合,可以实现复杂的业务逻辑处理,同时减少网络通信...
《Informix动态服务器错误代码中文详解》 Informix动态服务器是IBM公司的一款高性能的关系型数据库管理系统,它在处理大量数据和高并发事务方面表现出色。然而,在使用过程中,难免会遇到各种错误,这些错误通常以...
书中会详细讲解这些过程,确保读者能顺利搭建Informix环境。 三、SQL语言基础 SQL是Structured Query Language的缩写,是用于管理关系数据库的标准语言。书中会详细介绍SQL的基本语法,如数据查询、插入、更新和...
Informix 是 IBM 公司开发的一款关系数据库管理系统,提供了强大且灵活的数据存储和管理功能。在本文中,我们将详细介绍 Informix for Windows 数据库的安装配置过程,帮助初学者快速入门。 安装 Informix for ...
下面将详细阐述存储过程的建立、语法结构、控制语句、开发、运行、调试以及与Informix的差异。 1. **存储过程的建立** 创建存储过程通常使用`CREATE OR REPLACE PROCEDURE`语句。例如: ```sql CREATE OR ...