CallableStatement cs=con.prepareCall("{call myprocin(?)}");
cs.setString(1,"a string");
cs.registerOutParameter(1,Type.VARCHAR); //输出一个结果集,
cs.execute();
String outParam=cs.getString(1);如果你还不懂可以在CSDN中搜索前面有很多人问这样的问题
cs.setString(1,"a string");
cs.registerOutParameter(1,Type.VARCHAR); //输出一个结果集,
cs.execute();
String outParam=cs.getString(1);如果你还不懂可以在CSDN中搜索前面有很多人问这样的问题
比如:
CREATE OR REPLACE PACKAGE BODY MyTest
IS PROCEDURE zhbtest(P_CUSTOMER_ID c_well.wellno %TYPE, Re_CURSOR OUT T_CURSOR)
IS
V_CURSOR T_CURSOR;
BEGIN
OPEN V_CURSOR FOR
select wellname from c_well ;
Re_CURSOR := V_CURSOR;
END;
END;
public class Protest {
private static Connection conn = null;
private static oracle.jdbc.OracleCallableStatement call = null;
private static ResultSet rs = null;
private static String url = "jdbc:oracle:thin:@192.168.100.145:1521:kdc";
private static String name = "liuyi";
private static int cout = 0; public static void main(String[] args){
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(url,"kdcerp2","123");
call = (oracle.jdbc.OracleCallableStatement)conn.prepareCall("{call mytest.zhbtest(?,?)}");
call.setString(1, "4050608006");
call.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);
call.execute();
rs = call.getCursor(2);
while(rs.next()){
System.out.println(rs.getString(1));
cout++;
}
System.out.println(cout);
}catch(java.lang.ClassNotFoundException e){
e.printStackTrace();
}catch(SQLException e){
System.out.println(e.toString());
}
}
}
CallableStatement cs=con.prepareCall("{call myprocin(?)}");
cs.setString(1,"a string");
ResultSet rs = cs.executeQuery();
cs.close();
要返回rs最好用SQL语句,不用存储过程
OracleCallableStatement类中有 getCursor(2)方法吗
OracleCallableStatement call=null;
String sql=null;
ResultSet rs =null;
OA_Share_ObjectList dataStateList=this.getGradeStatByMonthNO(userInfo,date,strCanEdit);
OA_Share_ObjectList objectList=new OA_Share_ObjectList();
String date1=date.substring(0,8);//取出时间2003-02-
String maxTime=date1+"25 23:59:59";
String minTime=date1+"26 00:00:00";
String sqlMinTime=null;
String sqlMaxTime=null;
String userNO=null;
if(dataStateList.GetItemCount()<=0){ ///没有数据,查询当月的预考核
sqlMinTime="ADD_MONTHS(TO_DATE('"+minTime+"','YYYY-MM-DD HH24:MI:SS'),-1)";
sqlMaxTime="TO_DATE('"+maxTime+"','YYYY-MM-DD HH24:MI:SS')";
if (strCanEdit.equals("1")) { ///有权限获取所有人的
userNO="%";
}// end strCanEdit=1
else { ///没权限个人
userNO=userInfo.getEmployeeNo();
}//end strCanEdit=2
}else {///有数据查询下一个月的预统计
sqlMinTime="TO_DATE('"+minTime+"','YYYY-MM-DD HH24:MI:SS')";
sqlMaxTime="ADD_MONTHS(TO_DATE('"+maxTime+"','YYYY-MM-DD HH24:MI:SS'),1)";
if (strCanEdit.equals("1")) { ///有权限获取所有人的
userNO="%";
} ///end strCanEdit=1
else { ///没权限个人
userNO=userInfo.getEmployeeNo();
}///end strCanEdit=2
} //end dataStateList.GetItemCount()>=0
call = (OracleCallableStatement)conn.prepareCall("{call package_GradeStat.p_GradeStat(?,?,?,?)}");
call.setString(1,sqlMinTime);
call.setString(2,sqlMaxTime);
call.setString(3,userNO);
call.registerOutParameter(4,oracle.jdbc.OracleTypes.CURSOR);
call.execute();
rs=call.getCursor(4);
while (rs.next()) {
OA_Grade_DataGradeStat dataGradeStat = new OA_Grade_DataGradeStat();
this.setDataQueryGradeStat(userInfo,dataGradeStat,rs);
if(dataGradeStat.getEmployeeNO()!=null){
objectList.AddItem(dataGradeStat);
}
}
rs.close();
call.close();
return objectList;
}我的包是
////包
CREATE OR REPLACE PACKAGE package_GradeStat AS
Type T_CURSOR IS REF CURSOR;
PROCEDURE p_GradeStat(v_minTime in varchar2,v_maxTime in varchar2,v_employeeNO in varchar2,Re_CURSOR OUT T_CURSOR);
END package_GradeStat;///包体
CREATE OR REPLACE PACKAGE BODY package_GradeStat
IS
PROCEDURE p_GradeStat(v_minTime in varchar2,v_maxTime in varchar2,v_employeeNO in varchar2,Re_CURSOR OUT T_CURSOR)
IS
V_CURSOR T_CURSOR ;
BEGIN
open V_CURSOR for
select TASKNUM,100-removeGradenum TRUENUM,TASKFINISHNUM,notfinishTaskcount,employeeNO,round((TASKFINISHNUM/TASKNUM)*100,2)||'%' percentum
from (---从集合中取出想要的结果
select sum(NVL(allTaskCount,0)) TASKNUM, sum(NVL(removeGradenum,0)) removeGradenum,sum(NVL(endTaskCount,0)) TASKFINISHNUM ,
sum(NVL(notfinishTaskcount,0)) notfinishTaskcount,employeeNO
from (----所有监督人评分 select allTask.allTaskCount, NVL(notTask.removeGradenum_not,0) removeGradenum, endTask.endTaskCount,NVL(notTask.notTaskCount,0) notfinishTaskcount,
allTask.custodian employeeNO
from ( --所有的任务
select worktask.custodian,count(worktask.complete) allTaskCount from v_all_custodian worktask ,OA_GRADEMEMBER gm
where worktask.GRADENO=gm.GRADENO and (worktask.workendtime>=v_minTime and worktask.workendtime<=v_maxTime)
and (worktask.custodian in (select Groupno from OA_GRADEPRIVILEGE where Groupno like v_employeeNO ))
group by worktask.CUSTODIAN
) allTask
left Join(--已经完成的任务
select count(worktask.complete) endTaskCount,worktask.custodian
from v_all_custodian worktask ,OA_GRADEMEMBER gm
where worktask.GRADENO=gm.GRADENO and (worktask.workendtime>=v_minTime and worktask.workendtime<=v_maxTime)
and (worktask.custodian in (select Groupno from OA_GRADEPRIVILEGE where Groupno like v_employeeNO ))
group by worktask.CUSTODIAN,worktask.complete
having worktask.complete=1 --用已完成的标识来统计
) endTask on allTask.CUSTODIAN=endTask.CUSTODIAN --取出已完成的任务数left Join (---未完成的任务
select sum(NVL(gm.gradenum,0)) removeGradenum_not,count(worktask.complete) notTaskCount,worktask.custodian
from v_all_custodian worktask ,OA_GRADEMEMBER gm
where worktask.GRADENO=gm.GRADENO and (worktask.workendtime>=v_minTime and worktask.workendtime<=v_maxTime)
and (worktask.custodian in (select Groupno from OA_GRADEPRIVILEGE where Groupno like v_employeeNO ))
group by worktask.CUSTODIAN,worktask.complete
having worktask.complete=0 or worktask.complete=2 --用未完成的标识来统计
) notTask on allTask.CUSTODIAN=notTask.CUSTODIAN --取出要扣的分数,未完成的任务数UNION ALLselect ----------所有的负责人评分
allTask.allTaskCount,NVL(notTask.removeGradenum_not,0) removeGradenum,endTask.endTaskCount,
NVL(notTask.notTaskCount,0) notfinishTaskcount,allTask.employeeNO
from (--所有的任务
select worktask.employeeNO,count(worktask.complete) allTaskCount
from v_all_employee worktask ,OA_GRADEMEMBER gm
where worktask.GRADENO=gm.GRADENO and (worktask.workendtime>=v_minTime and worktask.workendtime<=v_maxTime)
and (worktask.employeeNO in (select Groupno from OA_GRADEPRIVILEGE where Groupno like v_employeeNO ))
group by worktask.employeeNO
) allTask ---取出所有的工作数,员工的编号
left Join(--已经完成的任务select worktask.employeeNO,count(worktask.complete) endTaskCount
from v_all_employee worktask ,OA_GRADEMEMBER gm
where worktask.GRADENO=gm.GRADENO and (worktask.workendtime>=v_minTime and worktask.workendtime<=v_maxTime)
and (worktask.employeeNO in (select Groupno from OA_GRADEPRIVILEGE where Groupno like v_employeeNO ))
group by worktask.employeeNO,worktask.complete
having worktask.complete=1 --用也完成的标识来统计 ) endTask on allTask.employeeNO=endTask.employeeNO ---取出完成的工作数left Join (---未完成的任务
select worktask.employeeNO,sum(NVL(gm.gradenum,0)) removeGradenum_not,count(worktask.complete) notTaskCount
from v_all_employee worktask ,OA_GRADEMEMBER gm
where worktask.GRADENO=gm.GRADENO and (worktask.workendtime>=v_minTime and worktask.workendtime<=v_maxTime)
and (worktask.employeeNO in (select Groupno from OA_GRADEPRIVILEGE where Groupno like v_employeeNO ))
group by worktask.employeeNO,worktask.complete
having worktask.complete=0 or worktask.complete=2 --用未完成的标识来统计) notTask on allTask.employeeNO=notTask.employeeNO --取出未完成的工作数,要扣的分数
UNION ALL
select 0 allTaskCount,sum(nvl(removeGradenum,0)) removeGradenum,0 endTaskCount,
0 notfinishTaskcount,employeeNo
from (
select (trunc(INPUTTIME)-trunc(MEETINGDATE)-1)*3 as removeGradenum,COMPEREMAN employeeNO,MEETINGDATE startTime from OA_SUMMARYMAIN where (trunc(INPUTTIME)-trunc(MEETINGDATE))>1
union all
select (trunc(INPUTTIME)-trunc(PLANPUTINTIME))*3 as removeGradenum,PRINCIPALNO employeeNO,PLANPUTINTIME startTime from OA_WORKPLANMAIN where (trunc(INPUTTIME)-trunc(PLANPUTINTIME))>0
)
where startTime>=v_minTime and startTime<=v_maxTime
and (employeeNO in (select Groupno from OA_GRADEPRIVILEGE where Groupno like v_employeeNO ))
group by employeeNo
)
group by employeeNO) ------end 从集合中取出想要的结果
;
Re_CURSOR := V_CURSOR;
close V_CURSOR;
END;---过程结束
END;---包体结束编译的时候没有错误,执行的时候,返回oracle 错误,输入的是数值型,而过程中返回的是游标,我不知怎样set游标对象,我在java 中怎样声明游标对象
也就是说我怎样能把游标对象set到过程中??????很急的,谢谢各位大侠了!!!
ORA-01858: 在要求输入数字处找到非数字字符 ORA-06512: 在"WGDB.P_GRADESTAT1", line 10 ORA-06512: 在line 1
怎样解决啊????
急????????????