BYR Achieve · 镜像论坛

[讨论]sp的问题

2008/3/20镜像同步13 回复

前几天想写mysql的自定义函数,达人说叫sp(存储程序),啃了一会书后写了成功地完成了一些小函数.但有一个函数老通不过,再次请教达人. 数据表survey_survey 字段类型 Null 默认 surveyID int(20) 是 NULL surveyTitle varchar(255) 是 surveyFile varchar(500) 是 NULL surveyInfo mediumtext 是 surveyType int(2) 是 surveyMaxOption int(2) 是 8 beginTime varchar(14) 是 endTime varchar(14) 是 isCheckIP int(1) 是 1 maxIPTime int(6) 是 1000 maxResponseNum int(6) 是 showResultButton int(1) 是 0 surveyTemplate int(1) 是 1 surveyState int(1) 是 1 surveyCreatorID varchar(20) 是 surveyCreateDate timestamp 是 CURRENT_TIMESTAMP 首先写了一个存储过程 DELIMITER $$ DROP PROCEDURE IF EXISTS GetSurveyList $$ #Get the surveylist with GetSurveyList,you must designate a variable to get a return value. #It returns the numbers of survey which is available,if there's no available survey,it will return 0. CREATE PROCEDURE GetSurveyList (OUT numbers INT) proc:BEGIN SET numbers=0; SELECT COUNT(*) FROM survey_survey WHERE surveyState=3 INTO numbers; IF numbers=0 THEN LEAVE proc; ELSE SELECT surveyID,surveyTitle FROM survey_survey WHERE surveyState=3; END IF; END $$ ; 成功执行了.很高兴但再写一个,就老报错 DELIMITER $$ CREATE PROCEDURE GetSurvey (IN id INT,OUT numbers INT) proc:BEGIN DECLARE sn,cn INT; SET sn=0; SET cn=0; SELECT COUNT(*) FROM survey_survey WHERE surveyState=3 INTO sn; IF sn=0 THEN SET numbers=sn; LEAVE proc; END IF; SELECT COUNT(*) FROM survey_survey WHERE surveyID=id INTO cn; IF cn=0 THEN LEAVE proc; ELSE SELECT surveyID,surveyTitle,surveyInfo,surveyType FROM survey_survey WHERE surveyID=id into numbers; END IF; END $$ delimiter ; error 1222 (21000):the used select statements have a different number of columns 翻译出来就是所使用的SELECT语句有不同的列数找了一个老外的mysql5第三版,上面有一个类似的例程,我试了一下也没问题, #下面是老外的 DELIMITER $$ CREATE PROCEDURE titles_insert_all (IN newtitle VARCHAR(100),IN publ VARCHAR(60),IN authList VARCHAR(255),OUT newID INT) proc:BEGIN DECLARE cnt,pos INT; DECLARE aID,pblID,ttlID INT; DECLARE author VARCHAR(60); SET newID=-1; #Search for/store publisher SELECT COUNT(*) FROM publishers WHERE publname=publ INTO cnt; IF cnt=1 THEN SELECT publID FROM publishers WHERE publname=publ INTO pblID; ELSE INSERT INTO publishers(publName) VALUES(publ); SET pblID=LAST_INSERT_ID(); END IF; #Store the title INSERT INTO titles(title,publID) VALUES(newtitle,pblID); SET ttlID=LAST_INSERT_ID(); #Loop over all authors in authList authloop: WHILE NOT(authList="") DO SET pos=LOCATE(";",authList); IF pos=0 THEN SET author=TRIM(authList); SET authList=""; ELSE SET author=TRIM(LEFT(authList,pos-1)); SET authList=SUBSTR(authList,pos+1); END IF; IF author ="" THEN ITERATE authloop; END IF; #Search for/store author SELECT COUNT(*) FROM authors WHERE authName=author OR authName=swap_name(author) INTO cnt; IF cnt>=1 THEN SELECT authorID FROM authors WHERE authName=author OR authName=swap_name(author) LIMIT 1 INTO aID; ELSE INSERT INTO authors(authName) VALUES(author); SET aID=LAST_INSERT_ID(); END IF; #Update rel_title_author INSERT INTO rel_title_author(title,authID) VALUES(ttlID,aID); END WHILE authloop; #return value SET newID=ttlID; END proc $$ DELIMITER ; # 求达人再次帮忙.今晚站内通宵等.

订阅后，新回复会通过你的通知中心匿名送达。