创建oracle的存储过程，以及通过JDBC调用该存储过程

于 2011-12-19 16:38:51 发布
阅读量627 收藏
点赞数
分类专栏： J2EE 文章标签： 存储 jdbc oracle insert integer table
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/fengyan5022/article/details/7084723
版权

一、创建存储过程

create or replace procedure mwp_jgdx_dxxm_batchcopy
(
srcxmid in varchar,   --项目ID字符串
bbjlid in varchar
	--新版本ID
)
is
l_objIDs varchar(32767) := srcxmid||',';
l_bbID varchar(42) := bbjlid; --版本记录ID
l_xmobj_id varchar(42); --新生成项目的36位objId
l_new_objid  varchar(42);--新生成的42位项目objId
l_dkxmID varchar(42);--新生成的打捆子项目42位的objId
l_dkxmobj_id varchar(42);
l_obj_id varchar(42);
	
		--临时obj_id 
l_pos integer := 0;
l_dest varchar(42);--要复制的项目的obj_id

l_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;
begin

l_pos := instr(l_objIDs,',');

select mw_sys.newGuid into l_xmobj_id from dual where rownum=1;
while l_pos>0 loop
	l_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_id
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,SFCB

XM,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,sfkqdwxm
from MW_APP.MWT_UD_JGDX_DXXMK where obj_id =l_dest;

---将原项目的上一版本id，维护为新生成的项目的obj_id
update 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_id
	insert 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_id
LCHJID,HJMC,CLBM,CLR,CLYJ,LCQYX,CLSJ,LCCLSM,ZXCZ,NCLR
from 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_id
insert 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_id
ND,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_id
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_new_objid,
FJLX,FJMC,FJ,BZ,OBJ_ID
from 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 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_sn
	from 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_DATA
	from 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_sn
	from 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_DATA
	from 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 loop
	l_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,SFCB

XM,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,sfkqdwxm
	from MW_APP.MWT_UD_JGDX_DXXMK where obj_id =objidArray(i);

	--更新上一版本ID
	update 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_id
	insert 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_id
LCHJID,HJMC,CLBM,CLR,CLYJ,LCQYX,CLSJ,LCCLSM,ZXCZ,NCLR
from 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_id
insert 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_id
ND,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_ID
from 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_sn
from 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_DATA
from 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 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_sn
	from 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_DATA
	from 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_LCRZ
				WHERE 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 = '2011'   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,"20101220-008");
			cs.execute();
			conn.commit();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

}


雁舞九天
关注
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值