现在我做了一个java访问存储过程的。。
java:
ArrayList aa=new ArrayList();
for (int k = 0; k < newSalesChanceIds.length; k++) {
aa.add(k,userCodes[k]);
}
ArrayList bb=new ArrayList();
for (int k = 0; k < newSalesChanceIds.length; k++) {
bb.add(k,newSalesChanceIds[k]);
}
String comCode = request.getParameter("comCode");
String activityId = request.getParameter("activityId");// 活动ID
// bb=['00145CF47AD82134F7132ABABACF096573BF','0014AF4FB9B56B74AF3833862D1D15A6374A', '0014370B71F58A322C490D8B29C583488FFF','00142076543600E3D9C1A79F9CEAB64A5001']
// aa=[0001, 007]
//上面的aa，bb都可以得到值的。。只要看后面的。。
DbProcedure dbProcedure = new DbProcedure();
dbProcedure.doDataAgent(aa, bb, comCode,activityId);
public void doDataAgent(ArrayList userCodes, ArrayList newSalesChanceIds, String comCode,
String activityID) throws Exception {
CallableStatement proc = null;
DBManager dbManager = new DBManager();
Connection conn = null;
try {
dbManager.open("platformDataSource");
conn = dbManager.getConnection();
proc = conn
.prepareCall("{ call ccicdb.doDataToAgent(?,?,?,?) }");
System.out.println("userCodes=" + userCodes);
System.out.println("newSalesChanceIds=" + newSalesChanceIds);
System.out.println("comCode=" + comCode);
System.out.println("activityID=" + activityID);
proc.setArray(1, (Array) userCodes);
proc.setArray(2, (Array) newSalesChanceIds);
proc.setString(3, comCode);
proc.setString(4, activityID);
proc.execute();
} catch (Exception ex) {
ex.printStackTrace();
} finally {
if (conn != null)
conn.close();
if (dbManager != null)
dbManager.close();
}
}
存储过程：
create or replace procedure doDataToAgent(userCodes in myvarray_usercodelist,salesChanceIds in myvarray_list, v_comCodeIn varchar2,v_activityId varchar2) is
v_count number;
activityname varchar2(40);
activitySort varchar2(40);
activitytype varchar2(40);
activityid varchar2(40);
begin
for h in 1..salesChanceIds.count loop
UPDATE Crm_sales_chance SET UserCode =userCodes(h),Reserve3='agent', ComCode = v_comCodeIn WHERE SALESCHANCEID in (salesChanceIds(h));
EXECUTE IMMEDIATE 'select distinct(t.Activityname) from Crm_sales_chance t where t.SalesChanceId in''%'||salesChanceIds('||h||')||'%''' into activityname;
EXECUTE IMMEDIATE 'select distinct(t.ActivitySort) from Crm_sales_chance t where t.SalesChanceId in''%'||salesChanceIds('||h||')||'%''' into activitySort;
EXECUTE IMMEDIATE 'select distinct(t.Activitytype) from Crm_sales_chance t where t.SalesChanceId in''%'||salesChanceIds('||h||')||'%''' into activitytype;
EXECUTE IMMEDIATE 'select distinct(t.Activityid) from Crm_sales_chance t where t.SalesChanceId in''%'||salesChanceIds('||h||')||'%''' into activityid;
select count(t.Autoid) into v_count from Cc_activityhandler t where t.Activityid = v_activityId and t.Handlerid = userCodes(h);
if (v_count=0) then
insert into Cc_activityhandler (Autoid,Activityname,Activitysort,Activitytype,Activityid,Createtime,Handlerid,Reserve1,Reserve2,Reserve3,Reserve4 )
VALUES(Cc_activityhandlerSequence.Nextval ,activityname,activitysort,activitytype,activityid,sysdate,userCodes(h),'','1','','');
end if;
end loop;
end doDataToAgent;
问题================================：
现在我感觉这块有点问题。。。
proc.setArray(1, (Array) userCodes);
proc.setArray(2, (Array) newSalesChanceIds);
另外各位大虾们看看我写的存储过程有问题没有啊。。。主要的传的数组的参数。。都不知道怎么弄了~！
分不够还可以在加，问题解决为主~！
