最近做一个项目，由于客户数据量大，为了不将数据彻底删除，于是将数据移动到历史表，原始表的数据删除。由于技术有限，想不到好的方法，于是写个存储过程
执行，为了防止执行过程中出现异常，执行不完整。用到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); } }