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;
过程如上,调用时出错,不知道哪里的错误了!
...全文
971 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-connector-java-5.1.30`这个版本的特性、用途以及如何在Java项目中使用它。 ### MySQL Connector/J 5.1.30 特性 1. **兼容性**：此版本的驱动支持MySQL服务器版本5.0至5.7，这为...
Maven+Spring+Spring MVC+MyBatis+MySQL，搭建SSM框架环境
SSM框架是Java开发中常用的一种组合，由Maven、Spring、Spring MVC和MyBatis四个组件构成，加上MySQL作为数据库支持，形成了一个强大的企业级应用开发基础。下面将详细介绍这些组件及其在搭建环境中扮演的角色。 1....
简单的注册登录功能(JavaWeb项目)
在Java中，我们可以使用JDBC或者ORM框架如Hibernate、MyBatis来操作数据库。 4. **异常处理**：在注册过程中可能会遇到各种异常，如数据库连接问题、SQL注入等，都需要进行妥善处理并返回合适的错误信息。 接下来...
guestbook程序代码
3. **数据持久化**：guestbook程序通常会使用关系型数据库（如MySQL、Oracle等）存储用户的留言。代码会涉及数据库连接、SQL查询和事务管理，确保数据的一致性和安全性。 4. **错误处理和异常处理**：在代码中，会...
Java调用MySQL存储过程
Java调用MySQL存储过程
MySQL

56,942

社区成员

56,759

社区内容

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

加载中

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

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

+ 用AI写文章