存储过程代码:
1 BEGIN 2 DECLARE studentNum_1 VARCHAR(50) CHARACTER SET utf8; 3 DECLARE testId_1 VARCHAR(100) CHARACTER SET utf8; 4 DECLARE testName_1 VARCHAR(200) CHARACTER SET utf8; 5 DECLARE testScore_1 DOUBLE; 6 7 DECLARE num int DEFAULT 0; 8 9 DECLARE test_cursor CURSOR FOR SELECT USERID,PUBLISHEDASSESSMENTID,TITLE,FINALSCORE FROM gradinginfo WHERE gradinginfo.SITEID=siteId /*COLLATE utf8_unicode_ci*/; 10 11 DECLARE CONTINUE HANDLER for not found SET num=1; 12 13 OPEN test_cursor; 14 FETCH test_cursor INTO studentNum_1,testId_1,testName_1,testScore_1; 15 16 WHILE num<>1 DO 17 IF testScore_1 IS NULL THEN 18 SET testScore_1=0; 19 END IF; 20 21 22 INSERT INTO e_studenttest(siteId,studentNum,testId,testName,testScore) VALUEs(siteId,studentNum_1,testId_1,testName_1,testScore_1); 23 FETCH test_cursor INTO studentNum_1,testId_1,testName_1,testScore_1; 24 end WHILE; 25 26 CLOSE test_cursor; 27 SELECT 1;
28 END
表gradinginf的结构设计:
错误原因:在存储过程中,使用了此语句DECLARE test_cursor CURSOR FOR SELECT USERID,PUBLISHEDASSESSMENTID,TITLE,FINALSCORE FROM gradinginfo WHERE gradinginfo.SITEID=siteId
存储过程的参数varchar的排序规则默认为utf8_general_ci,而在表gradinginfo中的字段siteId的排序规则是utf_unicode_ci,两者混用便会报错。
解决办法:
1):在参数时设置为utf8_unicode_ci
Create procedure error(in siteId varchar(100) COLLATE utf8_unicode_ci)----但此种方法在我的mysq中会提示:该mysql版本不支持
2):把表gradinginfo中的字段siteId的排序规则由utf_unicode_ci更改为utf8_general_ci
3):在where语句中加上COLLATE utf8_unicode_ci
WHERE gradinginfo.SITEID=siteId COLLATE utf8_unicode_ci