java调用mysql存储过程1328错误

Question

CREATE DEFINER=`mysql`@`%` PROCEDURE `PLAN_SEND_ALLSTUANNOUN`(IN id1 VARCHAR(10),IN loginId VARCHAR(10),IN objectnames VARCHAR(10))
BEGIN
DECLARE temp VARCHAR(50);
DECLARE course_temp_cur VARCHAR(2000);
DECLARE course_tempID_cur VARCHAR(10);
DECLARE course_tempName_cur VARCHAR(100);
DECLARE student_tmp_cur VARCHAR(2000);
DECLARE student_tmpSyscategoryId_cur VARCHAR(10);
DECLARE student_tmpUsId_cur VARCHAR(10);
DECLARE student_tmpCategoryname_cur VARCHAR(100);

DECLARE course_cur CURSOR FOR 
SELECT info.COURSENAME,co.ID FROM cm_courseinfo info JOIN cm_sys_cocategory co ON info.CATEGORYID = co.ID 
WHERE info.plankey = (SELECT  plan.plankey FROM cm_courseplan plan  WHERE plan.id = id1);

DECLARE student_cur CURSOR FOR 
SELECT us.ID,stu.syscategoryid,co.categoryname FROM cm_sys_student stu JOIN pm_user us ON us.ID=stu.studentid 
JOIN cm_sys_cocategory co ON co.id=stu.syscategoryid WHERE us.VALID=1 AND stu.status=0;

OPEN course_cur;
  FETCH course_cur INTO course_temp_cur;
  WHILE (!course_temp_cur) DO
  
SET course_tempID_cur = course_cur.ID;
SET course_tempName_cur = course_cur.COURSENAME;

OPEN student_cur;
  FETCH student_cur INTO student_tmp_cur;
  WHILE (!student_tmp_cur) DO
  
    SET student_tmpUsId_cur = student_cur.ID;
    SET student_tmpSyscategoryId_cur = student_cur.syscategoryid;     
    SET student_tmpCategoryname_cur = course_cur.categoryname+'<br>';
    
    IF(course_tempID_cur = student_tmpSyscategoryId_cur)THEN
     SET temp = temp + course_tempName_cur+'<br>';
    END IF;
    
    IF(!temp) THEN
INSERT PM_SYSTEMPROMPT(ids,content,USER_TYPE,OBJECTNAMES ,PUSH_USER,LAST_UPDATE_USER,LAST_UPDATE_TIME) VALUES
 (student_tmpUsId_cur,student_tmp_Categoryname+temp,1,objectnames,loginId,loginId,NOW());
SET temp = "";
COMMIT;  
    END IF;
    
  FETCH student_cur INTO student_tmp_cur;  
  END WHILE;
CLOSE student_cur;

  FETCH course_cur INTO course_temp_cur;
  END WHILE;
CLOSE course_cur;
过程如上,调用时出错,不知道哪里的错误了!

Answer 1

java.sql.SQLException: Incorrect number of FETCH variables
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2975)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1600)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1695)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3026)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1137)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:803)
at com.mysql.jdbc.CallableStatement.execute(CallableStatement.java:678)
at com.mchange.v2.c3p0.impl.NewProxyCallableStatement.execute(NewProxyCallableStatement.java:2966)
at com.bluefish.pm.announcement.service.AnnouncementService.sendAllMessage(AnnouncementService.java:201)
at com.bluefish.pm.announcement.action.AnnouncementAction.sendAllMessage(AnnouncementAction.java:97)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at com.bluefish.common.web.BaseAction.processExecute(BaseAction.java:132)
at com.bluefish.common.ajax.AjaxAction.execute(AjaxAction.java:49)
at com.bluefish.common.web.RequestProcessor.processActionPerform(RequestProcessor.java:60)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:236)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1196)
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:432)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
at com.bluefish.common.web.CharFilter.doFilter(CharFilter.java:25)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:172)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:174)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:875)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:81)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:689)
at java.lang.Thread.run(Thread.java:595)
13:44:48,093 DEBUG SqlUtils:83 - Attempted to convert SQLException to SQLException. Leaving it alone. [SQLState: HY000; errorCode: 1328]
提示以上错误!

Answer 2

DECLARE course_cur CURSOR FOR
SELECT info.COURSENAME,co.ID
这里游标有两个（以上？）字段，而
FETCH course_cur INTO course_temp_cur;
这里只有一个变量，当然会报Incorrect number of FETCH variables了
修改过程。

Answer 3

DECLARE temp VARCHAR(50);


DECLARE course_tempID_cur VARCHAR(10);
DECLARE course_tempName_cur VARCHAR(100);

DECLARE student_tmpSyscategoryId_cur VARCHAR(10);
DECLARE student_tmpUsId_cur VARCHAR(10);
DECLARE student_tmpCategoryname_cur VARCHAR(100);

DECLARE course_cur CURSOR FOR 
SELECT info.COURSENAME,co.ID FROM cm_courseinfo info JOIN cm_sys_cocategory co ON info.CATEGORYID = co.ID 
WHERE info.plankey = (SELECT  plan.plankey FROM cm_courseplan plan  WHERE plan.id = id1);

DECLARE student_cur CURSOR FOR 
SELECT us.ID,stu.syscategoryid,co.categoryname FROM cm_sys_student stu JOIN pm_user us ON us.ID=stu.studentid 
JOIN cm_sys_cocategory co ON co.id=stu.syscategoryid WHERE us.VALID=1 AND stu.status=0;

OPEN course_cur;
  FETCH course_cur INTO course_tempName_cur,course_tempID_cur;
  WHILE (!course_tempID_cur) DO
  

OPEN student_cur;
  FETCH student_cur INTO student_tmpUsId_cur,student_tmpSyscategoryId_cur,student_tmpCategoryname_cur;
  WHILE (!student_tmp_cur) DO
  
         
    SET student_tmpCategoryname_cur = student_tmpCategoryname_cur+'<br>';
    
    IF(course_tempID_cur = student_tmpSyscategoryId_cur)THEN
     SET temp = temp + course_tempName_cur+'<br>';
    END IF;
    
    IF(!temp) THEN
INSERT PM_SYSTEMPROMPT(ids,content,USER_TYPE,OBJECTNAMES ,PUSH_USER,LAST_UPDATE_USER,LAST_UPDATE_TIME) VALUES
 (student_tmpUsId_cur,student_tmpCategoryname_cur+temp,1,objectnames,loginId,loginId,NOW());
SET temp = "";
COMMIT;  
    END IF;
    
  
  END WHILE;
CLOSE student_cur;

 
  END WHILE;
CLOSE course_cur;
过程已经修改,不报错.但最后确没有执行insert语句
上面两个游标后的语句都已经查出记录了!

Answer 4

游标到达末尾，似乎不能用!temp或者!游标变量名来。你要定义continue或者exit handler来确定一个while循环是否结束。网上搜下mysql，存储过程使用游标，很多的。

Answer 5

BEGIN
DECLARE course_tempID_cur VARCHAR(10);
DECLARE course_tempName_cur VARCHAR(100);


DECLARE _done INT DEFAULT 0; DECLARE course_cur CURSOR FOR 
SELECT info.COURSENAME,co.ID FROM cm_courseinfo info JOIN cm_sys_cocategory co ON info.CATEGORYID = co.ID 
WHERE info.plankey = (SELECT  plan.plankey FROM cm_courseplan plan  WHERE plan.id = id1);

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET _done = 1;

OPEN course_cur;
  REPEAT
      FETCH course_cur INTO course_tempName_cur,course_tempID_cur;
      IF NOT _done THEN
SELECT course_tempName_cur;
      END IF;
UNTIL _done END REPEAT;
CLOSE course_cur;游标后面的语句查出是三条记录,为什么我循环的时候只打出第一次的记录了?
循环有什么问题吗? 
为什么不能把三条记录都循环的打出来?

Answer 6

java.sql.SQLException: Incorrect number of FETCH variables

Answer 7

能否给一个mysql用for循环打出游标里内容的例子