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;

 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]

DECLARE course_cur CURSOR FOR
SELECT info.COURSENAME,co.ID

FETCH course_cur INTO course_temp_cur;

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;

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;

--转自