我想在存储过程中返回一个游标,可是在后台程序中调用这个存储过程就出现 Cursor is closed 这个错误~请兄弟们帮忙解决。存储过程:
create or replace procedure SP_A_SUPPLIER_EVAL(IN_SUPPLIER_ID IN NUMBER, /*-------供应商ID-------*/
IN_EQUIP_CATEG IN VARCHAR2, /*-------类别标识-------*/
O_EVAL_SCORE OUT NUMBER, /*-------评分-------*/
O_EVAL_RESLUT OUT SYS_REFCURSOR, /*-------返回结果集-------*/
O_EXCEPTION_FALG OUT NUMBER, /*-------异常标识-------*/
O_EXCEPTION_MSG OUT VARCHAR2 /*-------异常信息-------*/) is... OPEN O_EVAL_RESLUT FOR SELECT V_SUPPLIER_SCORE AS SUPPLIER_SCORE, V_CONTRACT_SCORE AS CONTRACT_SCORE, V_SERVICE_RESPONSE_SCORE AS SERVICE_RESPONSE_SCORE, V_BUSINESS_TREAT_SCORE ASBUSINESS_TREAT_SCORE
, V_ARRIVE_UNSUFFICE_SCORE AS ARRIVE_UNSUFFICE_SCORE
,V_DAY_PICK_SUCCESS_SCORE AS DAY_PICK_SUCCESS_SCORE, V_QUALITY_EVALUATE_SCORE AS QUALITY_EVALUATE_SCORE,V_QUALITY AS QUALITY, V_SERVICE AS SERVICE, V_SUPPLIER AS SUPPLIER FROM DUAL;...end SP_A_SUPPLIER_EVAL;
java代码:
Connection con = this.jdbcHandle.getDataSource().getConnection();
CallableStatement cs = con.prepareCall("{call SP_A_SUPPLIER_EVAL(?,?,?,?,?,?)}");
cs.setLong(1, supplierId);
cs.setString(2, equipCateg);
cs.registerOutParameter(3, Type.DOUBLE);
//OracleTypes.CURSOR 的值为:-10
cs.registerOutParameter(4, -10);
cs.registerOutParameter(5, Types.INTEGER);
cs.registerOutParameter(6, Types.VARCHAR);
boolean isExc = cs.execute();
if(!isExc){
supplierEvalBo.setScore(cs.getDouble(3));
ResultSet rs = (ResultSet) cs.getObject(4);
if(rs.next()){
....
}
}在红色字部分就报错了,这是为什么呢?
我在测试存储过程中,游标结果集里面是有记录的,我在dbms_output.put_line(O_EVAL_RESLUT%ROWCOUNT);输出行数的时候结果是0这是为什么呢
create or replace procedure SP_A_SUPPLIER_EVAL(IN_SUPPLIER_ID IN NUMBER, /*-------供应商ID-------*/
IN_EQUIP_CATEG IN VARCHAR2, /*-------类别标识-------*/
O_EVAL_SCORE OUT NUMBER, /*-------评分-------*/
O_EVAL_RESLUT OUT SYS_REFCURSOR, /*-------返回结果集-------*/
O_EXCEPTION_FALG OUT NUMBER, /*-------异常标识-------*/
O_EXCEPTION_MSG OUT VARCHAR2 /*-------异常信息-------*/) is... OPEN O_EVAL_RESLUT FOR SELECT V_SUPPLIER_SCORE AS SUPPLIER_SCORE, V_CONTRACT_SCORE AS CONTRACT_SCORE, V_SERVICE_RESPONSE_SCORE AS SERVICE_RESPONSE_SCORE, V_BUSINESS_TREAT_SCORE ASBUSINESS_TREAT_SCORE
, V_ARRIVE_UNSUFFICE_SCORE AS ARRIVE_UNSUFFICE_SCORE
,V_DAY_PICK_SUCCESS_SCORE AS DAY_PICK_SUCCESS_SCORE, V_QUALITY_EVALUATE_SCORE AS QUALITY_EVALUATE_SCORE,V_QUALITY AS QUALITY, V_SERVICE AS SERVICE, V_SUPPLIER AS SUPPLIER FROM DUAL;...end SP_A_SUPPLIER_EVAL;
java代码:
Connection con = this.jdbcHandle.getDataSource().getConnection();
CallableStatement cs = con.prepareCall("{call SP_A_SUPPLIER_EVAL(?,?,?,?,?,?)}");
cs.setLong(1, supplierId);
cs.setString(2, equipCateg);
cs.registerOutParameter(3, Type.DOUBLE);
//OracleTypes.CURSOR 的值为:-10
cs.registerOutParameter(4, -10);
cs.registerOutParameter(5, Types.INTEGER);
cs.registerOutParameter(6, Types.VARCHAR);
boolean isExc = cs.execute();
if(!isExc){
supplierEvalBo.setScore(cs.getDouble(3));
ResultSet rs = (ResultSet) cs.getObject(4);
if(rs.next()){
....
}
}在红色字部分就报错了,这是为什么呢?
我在测试存储过程中,游标结果集里面是有记录的,我在dbms_output.put_line(O_EVAL_RESLUT%ROWCOUNT);输出行数的时候结果是0这是为什么呢
O_EVAL_RESLUT%ROWCOUNT=0说明没有查到记录阿。
你那个查询
SELECT V_SUPPLIER_SCORE ... from dual;
这个,,,是你想要得吗?
你把你的那个查询语句拷出来到sqlplus下直接执行,不能通过的。
SELECT V_SUPPLIER_SCORE AS SUPPLIER_SCORE, V_CONTRACT_SCORE AS CONTRACT_SCORE, V_SERVICE_RESPONSE_SCORE AS SERVICE_RESPONSE_SCORE, V_BUSINESS_TREAT_SCORE ASBUSINESS_TREAT_SCORE
, V_ARRIVE_UNSUFFICE_SCORE AS ARRIVE_UNSUFFICE_SCORE
,V_DAY_PICK_SUCCESS_SCORE AS DAY_PICK_SUCCESS_SCORE, V_QUALITY_EVALUATE_SCORE AS QUALITY_EVALUATE_SCORE,V_QUALITY AS QUALITY, V_SERVICE AS SERVICE, V_SUPPLIER AS SUPPLIER FROM DUAL;
, V_ARRIVE_UNSUFFICE_SCORE AS ARRIVE_UNSUFFICE_SCORE
,V_DAY_PICK_SUCCESS_SCORE AS DAY_PICK_SUCCESS_SCORE, V_QUALITY_EVALUATE_SCORE AS QUALITY_EVALUATE_SCORE,V_QUALITY AS QUALITY, V_SERVICE AS SERVICE, V_SUPPLIER AS SUPPLIER FROM DUAL;这几个是存储过程中的变量,而dual表是oracle系统的一个空表,我这句话的意思是将这几个变量组装成一条记录,然后通过游标返回到程序中。我也用其它表测试了一下,返回结果是有多条记录,但在程序中打印结果条数还是0