一、创建存储过程
create or replace procedure mwp_jgdx_dxxm_batchcopy(srcxmid in varchar, --项目ID字符串bbjlid in varchar--新版本ID)isl_objIDs varchar(32767) := srcxmid||',';l_bbID varchar(42) := bbjlid; --版本记录IDl_xmobj_id varchar(42); --新生成项目的36位objIdl_new_objid varchar(42);--新生成的42位项目objIdl_dkxmID varchar(42);--新生成的打捆子项目42位的objIdl_dkxmobj_id varchar(42);l_obj_id varchar(42);--临时obj_id l_pos integer := 0;l_dest varchar(42);--要复制的项目的obj_idl_xmnum integer := 0;---项目序号l_dkxmnum integer :=0;type table_type is table of mw_app.mwt_ud_jgdx_dxxmk.obj_id%type;objidArray table_type;fjobjidArray table_type;beginl_pos := instr(l_objIDs,',');select mw_sys.newGuid into l_xmobj_id from dual where rownum=1;while l_pos>0 loopl_dest :=substr(l_objIDs,1,l_pos-1);---要复制的下一个项目的obj_id-- insert into mw_app.mwt_ud_dxxmk_temp(objID) values(l_dest);l_objIDs :=substr(l_objIDs,l_pos+length(','));l_pos := instr(l_objIDs,',');--复制项目l_xmnum :=l_xmnum+1;l_new_objid :=l_xmobj_id||'-'||substr('0000' || l_xmnum,-5,5);--新生成项目的42位obj_idinsert into MW_APP.MWT_UD_JGDX_DXXMK(OBJ_ID,OBJ_DISPIDX,KB,QYGS,WSGS,DSGS,BZBM,BZSJ,BZR,JHND,XMXH,XMBM,XMMC,XMLX,ZYLB,ZYXF,DXMD,DYDJ,ZTZ,ZCXZ,XMKSSJ,XMWCSJ,LXYJ,XMNR,ZYCD,TZLX,TZYY,TZQXMID,SFCBXM,CBXMJB,XMZT,KYBG,QTXMSX,XMGHSJ,SFXMJH,DKHXMID,XMLY,DYLYXMID,JLBZ,LCZT,DQLCHJ,BZ,SYBBID,BBHJLID,SFGSBB,TGDWSSNL,znhbdz,sfkqdwxm)select l_new_objid ,mw_sys.mwq_obj_dispidx.nextval,KB,QYGS,WSGS,DSGS,BZBM,BZSJ,BZR,JHND,XMXH,XMBM,XMMC,XMLX,ZYLB,ZYXF,DXMD,DYDJ,ZTZ,ZCXZ,XMKSSJ,XMWCSJ,LXYJ,XMNR,ZYCD,TZLX,TZYY,TZQXMID,SFCBXM,CBXMJB,XMZT,KYBG,QTXMSX,XMGHSJ,SFXMJH,DKHXMID,XMLY,DYLYXMID,JLBZ,LCZT,DQLCHJ,BZ,sybbid,l_bbID,'T',TGDWSSNL,znhbdz,sfkqdwxmfrom MW_APP.MWT_UD_JGDX_DXXMK where obj_id =l_dest;---将原项目的上一版本id，维护为新生成的项目的obj_idupdate mw_app.mwt_ud_jgdx_dxxmk set sybbid = l_new_objid where obj_id = l_dest;--复制流程日志select mw_sys.newGuid into l_obj_id from dual where rownum=1;--临时的日志的36位obj_idinsert into MW_APP.MWT_UD_JGDX_LCRZ(OBJ_ID,OBJ_DISPIDX,XMID,LCHJID,HJMC,CLBM,CLR,CLYJ,LCQYX,CLSJ,LCCLSM,ZXCZ,NCLR)select l_obj_id||'-'||substr('0000'||rownum,-5,5),mw_sys.mwq_obj_dispidx.nextval,l_new_objid ,--将新复制的流程日志的xmid维护为新生成的项目的obj_idLCHJID,HJMC,CLBM,CLR,CLYJ,LCQYX,CLSJ,LCCLSM,ZXCZ,NCLRfrom MW_APP.MWT_UD_JGDX_LCRZ rz where xmid=l_dest;--复制大修规模及成果select mw_sys.newGuid into l_obj_id from dual where rownum=1;--临时的大修规模的36位obj_idinsert into MW_APP.MWT_UD_JGDX_GZGM(OBJ_ID,OBJ_DISPIDX,XMID,ND,DYDJ,SBLB,SBMC,ZJJH,SL,JLDW,BZ,RLCD,GZCGFL,GZCGRLCD,GZCGJLDW,GLJLID,gzcgsl)select l_obj_id||'-'||substr('0000'||rownum,-5,5),mw_sys.mwq_obj_dispidx.nextval,l_new_objid,--将新复制的大修规模及成果的xmid维护为新生成的项目的obj_idND,DYDJ,SBLB,SBMC,ZJJH,SL,JLDW,BZ,RLCD,GZCGFL,GZCGRLCD,GZCGJLDW,GLJLID,gzcgsl--关联记录ID暂时未修改from MW_APP.MWT_UD_JGDX_GZGM gzgm where xmid=l_dest;--复制附件select mw_sys.newGuid into l_obj_id from dual where rownum=1;--临时的附件的36位obj_idinsert into MW_APP.MWT_UD_JGDX_XMFJ(OBJ_ID,OBJ_DISPIDX,XMID,FJLX,FJMC,FJ,BZ,SYBBID)select l_obj_id||'-'||substr('0000'||rownum,-5,5),mw_sys.mwq_obj_dispidx.nextval,l_new_objid,FJLX,FJMC,FJ,BZ,OBJ_IDfrom MW_APP.MWT_UD_JGDX_XMFJ fj where xmid =l_dest;--为新项目复制附件文件select obj_id bulk collect into objidArray from mw_app.mwt_ud_jgdx_xmfj where xmid = l_new_objid;for i in 1..objidArray.count loopselect mw_sys.newGuid into l_obj_id from dual where rownum=1;insert into mw_sys.mwt_is_vfile(vfile_id, vdir_id, vfile_name, vfile_dispidx, vfile_ctime, vfile_mtime, vfile_sn)select l_obj_id, vdir_id, vfile_name, vfile_dispidx, vfile_ctime, vfile_mtime, vfile_snfrom mw_sys.mwt_is_vfile where VFILE_ID = (select VFILE_ID from mw_sys.mwt_om_fsdata where obj_id = (select sybbid from mw_app.mwt_ud_jgdx_xmfj where obj_id = objidArray(i)));insert into mw_sys.mwt_om_fsdata(OBJ_ID, ATTR_ID, VFILE_ID, FSDATA_DATA)select objidArray(i),ATTR_ID, l_obj_id, FSDATA_DATAfrom mw_sys.mwt_om_fsdata fsdata where obj_id = (select sybbid from mw_app.mwt_ud_jgdx_xmfj where obj_id = objidArray(i));end loop;---复制可研报告文件select mw_sys.newGuid into l_obj_id from dual where rownum=1;insert into mw_sys.mwt_is_vfile(vfile_id, vdir_id, vfile_name, vfile_dispidx, vfile_ctime, vfile_mtime, vfile_sn)select l_obj_id, vdir_id, vfile_name, vfile_dispidx, vfile_ctime, vfile_mtime, vfile_snfrom mw_sys.mwt_is_vfile where VFILE_ID = (select VFILE_ID from mw_sys.mwt_om_fsdata where obj_id = l_dest);insert into mw_sys.mwt_om_fsdata(OBJ_ID, ATTR_ID, VFILE_ID, FSDATA_DATA)select l_new_objid,ATTR_ID, l_obj_id, FSDATA_DATAfrom mw_sys.mwt_om_fsdata fsdata where obj_id = l_dest;---复制打捆子项目select mw_sys.newGuid into l_dkxmobj_id from dual where rownum=1;l_dkxmnum := 0;select obj_id bulk collect into objidArray from mw_app.mwt_ud_jgdx_dxxmk where dkhxmid = l_dest;for i in 1..objidArray.count loopl_dkxmnum := l_dkxmnum + 1;l_dkxmID := l_dkxmobj_id||'-'||substr('0000' || l_dkxmnum,-5,5);--复制打捆子项目insert into MW_APP.MWT_UD_JGDX_DXXMK(OBJ_ID,OBJ_DISPIDX,KB,QYGS,WSGS,DSGS,BZBM,BZSJ,BZR,JHND,XMXH,XMBM,XMMC,XMLX,ZYLB,ZYXF,DXMD,DYDJ,ZTZ,ZCXZ,XMKSSJ,XMWCSJ,LXYJ,XMNR,ZYCD,TZLX,TZYY,TZQXMID,SFCBXM,CBXMJB,XMZT,KYBG,QTXMSX,XMGHSJ,SFXMJH,DKHXMID,XMLY,DYLYXMID,JLBZ,LCZT,DQLCHJ,BZ,SYBBID,BBHJLID,SFGSBB,TGDWSSNL,znhbdz,sfkqdwxm)select l_dkxmID ,mw_sys.mwq_obj_dispidx.nextval,KB,QYGS,WSGS,DSGS,BZBM,BZSJ,BZR,JHND,XMXH,XMBM,XMMC,XMLX,ZYLB,ZYXF,DXMD,DYDJ,ZTZ,ZCXZ,XMKSSJ,XMWCSJ,LXYJ,XMNR,ZYCD,TZLX,TZYY,TZQXMID,SFCBXM,CBXMJB,XMZT,KYBG,QTXMSX,XMGHSJ,SFXMJH,l_new_objid,XMLY,DYLYXMID,JLBZ,LCZT,DQLCHJ,BZ,sybbid,l_bbID,'T',TGDWSSNL,znhbdz,sfkqdwxmfrom MW_APP.MWT_UD_JGDX_DXXMK where obj_id =objidArray(i);--更新上一版本IDupdate mw_app.mwt_ud_jgdx_dxxmk set sybbid = l_dkxmID where obj_id = objidArray(i);--复制打捆子项目的流程日志select mw_sys.newGuid into l_obj_id from dual where rownum=1;--临时的打捆子项目的日志的36位obj_idinsert into MW_APP.MWT_UD_JGDX_LCRZ(OBJ_ID,OBJ_DISPIDX,XMID,LCHJID,HJMC,CLBM,CLR,CLYJ,LCQYX,CLSJ,LCCLSM,ZXCZ,NCLR)select l_obj_id||'-'||substr('0000'||rownum,-5,5),mw_sys.mwq_obj_dispidx.nextval,l_dkxmID ,--将新复制的流程日志的xmid维护为新生成的打捆子项目obj_idLCHJID,HJMC,CLBM,CLR,CLYJ,LCQYX,CLSJ,LCCLSM,ZXCZ,NCLRfrom MW_APP.MWT_UD_JGDX_LCRZ rz where xmid=objidArray(i);--复制打捆子项目的大修规模及成果select mw_sys.newGuid into l_obj_id from dual where rownum=1;--临时的大修规模的36位obj_idinsert into MW_APP.MWT_UD_JGDX_GZGM(OBJ_ID,OBJ_DISPIDX,XMID,ND,DYDJ,SBLB,SBMC,ZJJH,SL,JLDW,BZ,RLCD,GZCGFL,GZCGRLCD,GZCGJLDW,GLJLID,gzcgsl)select l_obj_id||'-'||substr('0000'||rownum,-5,5),mw_sys.mwq_obj_dispidx.nextval,l_dkxmID,--将新复制的大修规模及成果的xmid维护为新生成的打捆子项目的obj_idND,DYDJ,SBLB,SBMC,ZJJH,SL,JLDW,BZ,RLCD,GZCGFL,GZCGRLCD,GZCGJLDW,GLJLID,gzcgsl--关联记录ID暂时未修改from MW_APP.MWT_UD_JGDX_GZGM gzgm where xmid=objidArray(i);--重要：复制完打捆子计划之后批量更新大修规模相应的关联记录ID字段update MW_APP.MWT_UD_JGDX_GZGM gzgm set gzgm.GLJLID = (select SYBBID from mw_app.mwt_ud_jgdx_dxxmk where gzgm.GLJLID = obj_id)where gzgm.xmid = objidArray(i);--批量复制打捆子项目附件select mw_sys.newGuid into l_obj_id from dual where rownum=1;insert into MW_APP.MWT_UD_JGDX_XMFJ(OBJ_ID,OBJ_DISPIDX,XMID,FJLX,FJMC,FJ,BZ,SYBBID)select l_obj_id||'-'||substr('0000'||rownum,-5,5),mw_sys.mwq_obj_dispidx.nextval,l_dkxmID,FJLX,FJMC,FJ,BZ,OBJ_IDfrom MW_APP.MWT_UD_JGDX_XMFJ fj where xmid = objidArray(i);--文件处理（可研报告）select mw_sys.newGuid into l_obj_id from dual where rownum=1;insert into mw_sys.mwt_is_vfile(vfile_id, vdir_id, vfile_name, vfile_dispidx, vfile_ctime, vfile_mtime, vfile_sn)select l_obj_id, vdir_id, vfile_name, vfile_dispidx, vfile_ctime, vfile_mtime, vfile_snfrom mw_sys.mwt_is_vfile where VFILE_ID = (select VFILE_ID from mw_sys.mwt_om_fsdata where obj_id = objidArray(i) and rownum = 1) and rownum = 1;insert into mw_sys.mwt_om_fsdata(OBJ_ID, ATTR_ID, VFILE_ID, FSDATA_DATA)select l_dkxmID,ATTR_ID, l_obj_id, FSDATA_DATAfrom mw_sys.mwt_om_fsdata fsdata where obj_id = objidArray(i) and rownum = 1;--文件处理(打捆子项目附件)select obj_id bulk collect into fjobjidArray from MW_APP.MWT_UD_JGDX_XMFJ where xmid = l_dkxmID;for j in 1..fjobjidArray.count loopselect mw_sys.newGuid into l_obj_id from dual where rownum=1;insert into mw_sys.mwt_is_vfile(vfile_id, vdir_id, vfile_name, vfile_dispidx, vfile_ctime, vfile_mtime, vfile_sn)select l_obj_id, vdir_id, vfile_name, vfile_dispidx, vfile_ctime, vfile_mtime, vfile_snfrom mw_sys.mwt_is_vfile where VFILE_ID = (select VFILE_ID from mw_sys.mwt_om_fsdata where obj_id = (select sybbid from MW_APP.MWT_UD_JGDX_XMFJ where obj_id = fjobjidArray(j)));insert into mw_sys.mwt_om_fsdata(OBJ_ID, ATTR_ID, VFILE_ID, FSDATA_DATA)select objidArray(i),ATTR_ID, l_obj_id, FSDATA_DATAfrom mw_sys.mwt_om_fsdata fsdata where obj_id = (select sybbid from MW_APP.MWT_UD_JGDX_XMFJ where obj_id = fjobjidArray(j));end loop;--结束文件附件处理for循环end loop;--遍历打捆子项目完成end loop;----遍历原项目完成commit;end;
二、JDBC调用存储过程
import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class TestStoreProcedure {public static void main(String[] args) {Connection conn = null;CallableStatement cs = null;Statement stmt = null;Statement countStmt = null;ResultSet rs = null;ResultSet countRS = null;String objIDsSt="";try {Class.forName("oracle.jdbc.driver.OracleDriver");conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.43.20:1521:SPMS","mw_sys","sys");cs = conn.prepareCall("{call mw_app.mwp_jgdx_dxxm_batchcopy(?,?)}");StringBuffer stringBuffer= new StringBuffer();stringBuffer.append( " select obj_id from mw_app.mwv_jx_jgdx_dx_xmk xmk, (SELECT XMID, MAX(CLSJ) CLSJ, LCHJID");stringBuffer.append(" FROM MW_APP.MWT_UD_JGDX_LCRZWHERE LCHJID = 'F96105B0-4978-4877-9E6A-F997B3514EED'");stringBuffer.append(" AND CLBM = '63EBEC8E-E766-40D7-ACF4-FEA945102112-02679' AND NCLR = 'B99827FD-1F33-4142-B5FB-6E80BB95D3F9' GROUP BY XMID, LCHJID) LCRZ");stringBuffer.append(" where kb = '3' and nvl(SFGSBB, 'F') != 'T' and xmk.DQLCHJ = 'F96105B0-4978-4877-9E6A-F997B3514EED'");stringBuffer.append(" AND xmk.OBJ_ID = LCRZ.XMID and JHND = '' and nvl(SFKQDWXM, 'F') = 'F' and rownum<10 ");objIDsSt = "";String allObjIDs = stringBuffer.toString();String countSql = "select count(obj_id) as allobj ";int idx = allObjIDs.indexOf("from");if( idx>0){countSql += allObjIDs.substring(idx);}else{throw new RuntimeException("ＳＱＬ语句错误");}countStmt = conn.createStatement();countRS = countStmt.executeQuery(countSql);int sum = 0;if( countRS.next()){sum = countRS.getInt(1);}System.out.println("一共有："+sum+"条数据!");stmt = conn.createStatement();rs = stmt.executeQuery(stringBuffer.toString());while(rs.next()){if( objIDsSt.length()>0){objIDsSt+=","+rs.getString(1);}else{objIDsSt+=rs.getString(1);}}cs.setString(1, objIDsSt);cs.setString(2,"1220-008");cs.execute();mit();} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();}}}
