最近做一个项目，由于客户数据量大，为了不将数据彻底删除，于是将数据移动到历史表，原始表的数据删除。由于技术有限，想不到好的方法，于是写个存储过程
执行，为了防止执行过程中出现异常，执行不完整。用到hIbernate事务。下面是写的代码。sql存储过程写的很麻烦，希望能给点建议:
CREATE PROCEDURE [dbo].[savetohistoryDel]
--时间
@fromtime varchar(20)
AS
BEGIN
--插入作业到历史表
INSERT INTO inout_history_job
(
jobid,jobtype,status,in_out_type,jobpos,invoicetype,isaccount,priority
,createtime,readtime,finishtime,shiftid,warehouseid,snumber,traycode
,route,op_man_id,taskid,scargo_wharea_id,scargo_space_id,scargo_alley_id
,tcargo_wharea_id,tcargo_space_id,tcargo_alley_id,jobcategory,isyfj
,isseq,indate,on_line_type,oldspacestatus,injobid,isfangfj,checkstatus
)
SELECT
jobid,jobtype,status,in_out_type,jobpos,invoicetype,isaccount,priority
,createtime,readtime,finishtime,shiftid,warehouseid,snumber,traycode
,route,op_man_id,taskid,scargo_wharea_id,scargo_space_id,scargo_alley_id
,tcargo_wharea_id,tcargo_space_id,tcargo_alley_id,jobcategory,isyfj
,isseq,indate,on_line_type,oldspacestatus,injobid,isfangfj,checkstatus
FROM
inout_job
WHERE createtime < @fromtime
--插入作业详细到历史表
INSERT INTO inout_history_jobdetail
(
jobdetailid,linestatus,jobid,inventoryid,productid,packid,lotid,punit
,jobnum,assignnum,customerid,invoiceid,invoicedetailid,boxcode,boxnumber
,productcode,isinvoice,injobdetailid,reserve1,reserve2,ownerId,lotatt1
,lotatt2,lotatt3,lotatt4,lotatt5,lotatt6,lotatt7,lotatt8,lotatt9,lotatt10
,lotatt11,lotatt12,isfj,checklot,op_man_id,scantime,isneed,ifboxcount,productstatus
,checkstatus,checktime,checkwho,reviewnum,mp
)
SELECT
jobdel.jobdetailid,jobdel.linestatus,jobdel.jobid,jobdel.inventoryid,jobdel.productid,jobdel.packid,jobdel.lotid,jobdel.punit
,jobdel.jobnum,assignnum,jobdel.customerid,jobdel.invoiceid,jobdel.invoicedetailid,jobdel.boxcode,jobdel.boxnumber
,jobdel.productcode,jobdel.isinvoice,jobdel.injobdetailid,jobdel.reserve1,jobdel.reserve2,jobdel.ownerId,jobdel.lotatt1
,jobdel.lotatt2,jobdel.lotatt3,jobdel.lotatt4,jobdel.lotatt5,jobdel.lotatt6,jobdel.lotatt7,jobdel.lotatt8,jobdel.lotatt9,jobdel.lotatt10
,jobdel.lotatt11,jobdel.lotatt12,jobdel.isfj,jobdel.checklot,jobdel.op_man_id,jobdel.scantime,jobdel.isneed,jobdel.ifboxcount,jobdel.productstatus
,jobdel.checkstatus,jobdel.checktime,jobdel.checkwho,jobdel.reviewnum,jobdel.mp
FROM
inout_jobdetail jobdel,inout_job job
WHERE job.jobid=jobdel.jobid and job.createtime < @fromtime
--删除作业和作业详细
delete jobdel from inout_jobdetail jobdel,inout_job job
WHERE job.jobid=jobdel.jobid and job.createtime < @fromtime
delete inout_job where createtime < @fromtime --插入入库单到历史表
INSERT INTO inbound_history_invoice_header
(
instockid,warehouseid,invoicedate,createtime,createmanid,instatus,intype,opmode,uploadflag
,auditmanid,auditdate,confirmanid,confirmdate,departid,shiftid,on_line_type,inpos,invoicesource,customerid
)
SELECT
instockid,warehouseid,invoicedate,createtime,createmanid,instatus,intype,opmode,uploadflag
,auditmanid,auditdate,confirmanid,confirmdate,departid,shiftid,on_line_type,inpos,invoicesource,customerid
FROM
inbound_invoice_header
WHERE createtime < @fromtime --插入入库单详细到历史表
INSERT INTO inbound_history_invoice_detail
(
instockdetailid,instockid,productid,packid,eunit,boxnum,plattenum,invoicenum,puboxnum
,puplattenum,pucnum,lotid,reserve1,reserve2,providerid,linestatus,lotatt1,lotatt2,lotatt3,lotatt4,
lotatt5,lotatt6,lotatt7,lotatt8,lotatt9,lotatt10,lotatt11,lotatt12,checklot,isdown
)
SELECT
indel.instockdetailid,indel.instockid,indel.productid,indel.packid,indel.eunit,indel.boxnum,indel.plattenum,indel.invoicenum,indel.puboxnum
,indel.puplattenum,indel.pucnum,indel.lotid,indel.reserve1,indel.reserve2,indel.providerid,indel.linestatus,indel.lotatt1,indel.lotatt2,indel.lotatt3,indel.lotatt4,
indel.lotatt5,indel.lotatt6,indel.lotatt7,indel.lotatt8,indel.lotatt9,indel.lotatt10,indel.lotatt11,indel.lotatt12,indel.checklot,indel.isdown
FROM
inbound_invoice_detail indel,inbound_invoice_header inheader
WHERE inheader.instockid=indel.instockid and inheader.createtime < @fromtime
--删除入库单详细和入库单
delete indel from inbound_invoice_detail indel,inbound_invoice_header inheader
WHERE inheader.instockid=indel.instockid and inheader.createtime < @fromtime
delete inbound_invoice_header where createtime < @fromtime
--插入出库单到历史表
INSERT INTO outbound_history_invoice_header
(
outstockid,outstatus,outtype,departid,warehouseid,warehousemanid,createtime,createmanid,formdate
,outpos,vehicleno,vehiclepos,auditmanid,auditdate,confirmanid,confirmdate,opmanid,shiftid,saleno
,on_line_type,isupload,ownerid,sendplatform,setposition,shipmentstarttime,shipmentendtime,customerid
,isfbup,accountyear
)
SELECT
outstockid,outstatus,outtype,departid,warehouseid,warehousemanid,createtime,createmanid,formdate
,outpos,vehicleno,vehiclepos,auditmanid,auditdate,confirmanid,confirmdate,opmanid,shiftid,saleno
,on_line_type,isupload,ownerid,sendplatform,setposition,shipmentstarttime,shipmentendtime,customerid
,isfbup,accountyear
FROM
outbound_invoice_header
WHERE createtime < @fromtime --插入出库单详细到历史表
INSERT INTO outbound_history_invoice_detail
(
outstockdetailid,outstockid,productid,lotid,cargo_space_id,cargo_alley_id,wh_area_id,packid,pkgunit
,invoicenum,boxnum,assignnum,assignboxnum,sendnum,sendboxnum,customid,fbFlag,traycode,reserve1,reserve2
,confirmanid,confirmdate,linestatus,lotatt1,lotatt2,lotatt3,lotatt4,lotatt5,lotatt6,lotatt7,lotatt8
,lotatt9,lotatt10,lotatt11,lotatt12
)
SELECT
outdel.outstockdetailid,outdel.outstockid,outdel.productid,outdel.lotid,outdel.cargo_space_id,outdel.cargo_alley_id,outdel.wh_area_id,outdel.packid,outdel.pkgunit
,outdel.invoicenum,boxnum,outdel.assignnum,outdel.assignboxnum,outdel.sendnum,outdel.sendboxnum,outdel.customid,outdel.fbFlag,outdel.traycode,outdel.reserve1,outdel.reserve2
,outdel.confirmanid,outdel.confirmdate,outdel.linestatus,outdel.lotatt1,outdel.lotatt2,outdel.lotatt3,outdel.lotatt4,outdel.lotatt5,outdel.lotatt6,outdel.lotatt7,outdel.lotatt8
,outdel.lotatt9,outdel.lotatt10,outdel.lotatt11,outdel.lotatt12
FROM
outbound_invoice_detail outdel,outbound_invoice_header outheader
WHERE outheader.outstockid=outdel.outstockid and outheader.createtime < @fromtime
--删除出库单详细和出库单
delete outdel from outbound_invoice_detail outdel,outbound_invoice_header outheader
WHERE outheader.outstockid=outdel.outstockid and outheader.createtime < @fromtime
delete outbound_invoice_header where createtime < @fromtime
END
public void saveHistoryTabAndDelete(String time) throws Exception {
Session session = null;
Transaction tx = null;
try {
session = m_dao.getSession();
tx = session.beginTransaction();
SQLQuery query = session.createSQLQuery("{call savetohistoryDel(?)}");
query.setString(0, time);
query.executeUpdate();
tx.commit();
} catch (Exception e) {
tx.rollback();
e.printStackTrace();
}finally{
m_dao.closeSession(session);
}
}