java调用mysql存储过程1328错误

fobcclkm 2010-03-26 01:59:44
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;
过程如上,调用时出错,不知道哪里的错误了!
...全文
970 9 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
fobcclkm 2010-03-26
  • 打赏
  • 举报
 回复
能否给一个mysql用for循环打出游标里内容的例子
懒得去死 2010-03-26
  • 打赏
  • 举报
 回复
java.sql.SQLException: Incorrect number of FETCH variables
fobcclkm 2010-03-26
  • 打赏
  • 举报
 回复
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;

游标后面的语句查出是三条记录,为什么我循环的时候只打出第一次的记录了?
循环有什么问题吗?
为什么不能把三条记录都循环的打出来?
crazylaa 2010-03-26
  • 打赏
  • 举报
 回复
游标到达末尾，似乎不能用!temp或者!游标变量名来。你要定义continue或者exit handler来确定一个while循环是否结束。网上搜下mysql，存储过程使用游标，很多的。
fobcclkm 2010-03-26
  • 打赏
  • 举报
 回复
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语句
上面两个游标后的语句都已经查出记录了!
crazylaa 2010-03-26
  • 打赏
  • 举报
 回复
DECLARE course_cur CURSOR FOR
SELECT info.COURSENAME,co.ID
这里游标有两个（以上？）字段，而
FETCH course_cur INTO course_temp_cur;
这里只有一个变量，当然会报Incorrect number of FETCH variables了。。。
修改过程。
fobcclkm 2010-03-26
  • 打赏
  • 举报
 回复
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]
提示以上错误!
mysql-connector-java-5.1.30工具
MySQL官方提供的驱动包 mysql-connector-java-5.1.30.zip
Maven+Spring+Spring MVC+MyBatis+MySQL，搭建SSM框架环境
Maven+Spring+Spring MVC+MyBatis+MySQL，搭建SSM框架环境，详细查看博客
简单的注册登录功能(JavaWeb项目)
简单的注册，登录功能，注册功能使用MySQL数据库，采用mvc编程模式，注册功能有详细的表单填写限制
guestbook程序代码
axeon的guestbook程序代码
Java调用MySQL存储过程
Java调用MySQL存储过程
MySQL

56,940

社区成员

56,756

社区内容

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章