记一次表结构大变更带来的处理数据问题

1、旧需求

案件费用，每一个案件费用都要有审核流程，审核流程如下：

2、新需求

 客户要求一次申请可以申请多个费用明细，支付的时候，一次可以按照业务要求，支付给同一个收款人多条明细。

分析后得出的结论，申请和费用明细是一对多的关系，支付和费用明细也是一对多的关系；申请和支付无法做成一对一关系。

关系如下图所示：

基于新需求，将申请和支付分开，申请走审核流程，而支付不走流程，走完流程的费用，可以直接支付，支付时，可以根据同

一个收款人，自由选择。建立新的pdm关系如下所示：

3、生产环境更新

开发按照新需求开发，开发完成，如何安全更新到生产环境是接下来要完成的任务。

要求用户原来录入的数据导入新的结构。

列了一个基本思路如下：

3.1保护现场

这一步，要将生产环境现有数据备份出来，确保生产环境数据安全。

--1、备份原来表数据
SELECT * INTO t_case_fee_bak FROM t_case_fee ;

3.2创建新表

--2、创建新表sql1、sql2、sql3、sql4
--每个业务不一样，读者可以根据自己的业务建表
/*==============================================================*/
/* Table: t_case_fee_apply_opinion                              */
/*==============================================================*/
create table t_case_fee_apply_opinion (
   opinion_id           bigint               identity,
   apply_id             bigint               null,
   step                 int                  null,
   action               varchar(20)          null,
   opinion              varchar(1000)        null,
   createNo             varchar(20)          null,
   createName           varchar(20)          null,
   createTime           varchar(20)          null,
   constraint PK_T_CASE_FEE_APPLY_OPINION primary key (opinion_id)
)
go
/*==============================================================*/
/* Table: t_case_fee_apply                                      */
/*==============================================================*/
create table t_case_fee_apply (
   apply_id             bigint               identity,
   apply_remark         varchar(200)         null,
   apply_amount         decimal(18,2)        null,
   from_step            tinyint              null,
   step                 tinyint              null,
   status               tinyint              null,
   apply_no             varchar(30)          null,
   apply_name           varchar(50)          null,
   apply_time           varchar(30)          null,
   first_audit_opinion_id bigint               null,
   audit_opinion_id     bigint               null,
   last_audit_opinion_id bigint               null,
   cancel_reason        varchar(200)         null,
   cancel_time          varchar(30)          null,
   constraint PK_T_CASE_FEE_APPLY primary key (apply_id)
)
go
/*==============================================================*/
/* Table: t_case_fee                                            */
/*==============================================================*/
create table t_case_fee (
   fee_id               bigint               identity,
   case_id              int                  null,
   apply_id             bigint               null,
   pay_id               bigint               null,
   fee_no               varchar(30)          null,
   fee_name             varchar(50)          null,
   fee_amount           decimal(18,2)        null,
   payee_name           varchar(50)          null,
   is_public            int                  null,
   bank_name            varchar(200)         null,
   bank_no              varchar(50)          null,
   tel                  varchar(50)          null,
   remark               varchar(200)         null,
   status               tinyint              null,
   pay_time             varchar(30)          null,
   constraint PK_T_CASE_FEE primary key (fee_id)
)
go
/*==============================================================*/
/* Table: t_case_fee_pay                                        */
/*==============================================================*/
create table t_case_fee_pay (
   pay_id               bigint               identity,
   pay_remark           varchar(200)         null,
   payee_name           varchar(50)          null,
   is_public            int                  null,
   bank_name            varchar(200)         null,
   bank_no              varchar(50)          null,
   tel                  varchar(50)          null,
   payable_amount       decimal(18,2)        null,
   pay_amount           decimal(18,2)        null,
   pay_time             varchar(30)          null,
   pay_append_url       varchar(200)         null,
   payer_no             varchar(30)          null,
   payer                varchar(50)          null,
   status               tinyint              null,
   create_time          varchar(30)          null,
   cancel_reason        varchar(200)         null,
   cancel_time          varchar(30)          null,
   constraint PK_T_CASE_FEE_PAY primary key (pay_id)
)
go

3.3处理数据

考虑到业务复杂性，无法通过简单sql语句处理数据，所以决定封装一个存储过程专门处理数据。读者

也可以通过java代码完成。详细处理流程，可以参考存储过程中的注释。

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	临时处理，费用升级数据
-- =============================================
CREATE PROCEDURE p_tmp_case_fee_deal
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @fee_id INT
	DECLARE @apply_id INT
	DECLARE @max_fee_id INT
	DECLARE @pay_id INT
	DECLARE @pay_time VARCHAR(30);
	--创建一个游标
	declare my_cursor cursor for     --my_cursor为游标的名称，随便起
	select fee_id from t_case_fee_bak      --这是游标my_cursor的值，这里随便发挥看业务场景
	--打开游标
	open my_cursor                  --没什么好说的
	--循环游标
	fetch next from my_cursor into @fee_id  --获取my_cursor的下一条数据，其中为两个字段分别赋值给
	while @@FETCH_STATUS=0 --假如检索到了数据继续执行
	begin
		--1、插入申请表
		INSERT INTO t_case_fee_apply(apply_remark,apply_amount,from_step,step,STATUS,apply_no,apply_name,apply_time,cancel_reason,cancel_time)
		SELECT '',f.fee_amount,f.from_step,(case when f.step=3 THEN  4 ELSE f.step END),(CASE WHEN f.[status]=5 THEN 1 WHEN f.[status]=6 THEN 1 ELSE f.[status] END)
		,f.apply_no,f.apply_name,f.apply_time,f.cancel_reason,f.cancel_time
		  FROM t_case_fee_bak f WHERE fee_id=@fee_id;
		--获取申请表主键
		SELECT @apply_id=MAX(apply_id) FROM t_case_fee_apply;
		--2、插入审核意见表
		INSERT INTO t_case_fee_apply_opinion(apply_id,step,ACTION,opinion,createNo,createName,createTime)
		SELECT @apply_id,case when o.step=3 THEN 4 ELSE o.step END,o.[action],o.opinion,o.createNo,o.createName,o.createTime
		  FROM t_case_fee_opinion o
		WHERE fee_id=@fee_id;
		--3、插入费用明细
		INSERT INTO t_case_fee(case_id,apply_id,fee_no,fee_name,fee_amount,payee_name,is_public,bank_name,bank_no,tel,remark,[status])
		SELECT b.case_id,@apply_id,b.fee_no,b.fee_name,b.fee_amount,b.payee_name,b.is_public,b.bank_name,b.bank_no,b.tel,b.remark,
		CASE WHEN b.step=3 AND b.[status]=3 THEN 3 WHEN b.step=3 AND b.[status]=1 THEN 2 ELSE 1 end
		  FROM t_case_fee_bak b
		WHERE b.fee_id=@fee_id AND b.[status]<>4;
		--获取新生成的费用id
		SELECT @max_fee_id=MAX(fee_id) FROM t_case_fee;
		--如果满足支付，则生成支付信息
		IF EXISTS(SELECT 1 FROM t_case_fee_bak WHERE fee_id=@fee_id AND step=3 AND [status]=3)
		BEGIN
			--4、生成支付信息
			INSERT INTO t_case_fee_pay
			(
				-- pay_id -- this column value is auto-generated
				pay_remark,
				payee_name,
				is_public,
				bank_name,
				bank_no,
				tel,
				payable_amount,
				pay_amount,
				pay_time,
				pay_append_url,
				payer_no,
				payer,
				[status],
				create_time
			)
			SELECT b.remark,b.payee_name,b.is_public,b.bank_name,b.bank_no,b.tel,b.fee_amount,b.fee_amount,o.createTime,b.pay_append_url,
			o.createNo,o.createName,2,o.createTime
			  FROM t_case_fee_bak b
			  LEFT OUTER JOIN t_case_fee_opinion o ON b.pay_opinion_id=o.opinion_id
			WHERE b.fee_id=@fee_id;
			--获取payid
			SELECT @pay_id=MAX(pay_id) FROM t_case_fee_pay;
			--查询支付时间
			SELECT @pay_time=pay_time FROM t_case_fee_pay tcfp WHERE tcfp.pay_id=@pay_id;
			--5、会写信息，处理明细支付时间、支付id
			UPDATE t_case_fee SET pay_id = @pay_id,pay_time = @pay_time 
			WHERE fee_id=@max_fee_id;
		END
		
	
	
	fetch next from my_cursor into @fee_id --获取下一条数据并赋值给变量
	end--关闭释放游标
	close my_cursor
	deallocate my_cursor
END
GO