Question

关于mysql中存储过程，下面的代码报好几处错误，希望有人帮帮忙，最好指出我在哪些语法出错。先谢谢了！

use sjfx;
create Procedure p_insert_qs(in_paperid int, in_studentid int, in_score int)
AS DECLARE
t int;
begin
#更新paper_score表中的数据
temp= select score from paper_score where paper_id=in_paperid and
student_id= in_studentid;
if temp is null then
insert into paper_score(paper_id,student_id,score)
values(in_paperid, in_studentid, in_score);
else
update paper_score set score= score+in_score where
paper_id=in_paperid and
student_id= in_studentid;
#更新question_index中的数据
end;

CREATE TRIGGER insert_qs
AFTER INSERT ON question_score
for each row
call Procedure p_insert_qs(new.paper_id, new.student_id, new.score);

Answer 1

temp= select ......
这里的 temp 变量 好像前面从来没有定义过。

而且语法好像也有点问题。
一般的写法是
select score INTO temp from ...

还有就是那个 IF / ELSE 语法不对， 缺少一个 END IF

下面是一个 IF / ELSE 的例子：

mysql> DELIMITER //
mysql> CREATE PROCEDURE TestIfElse
-> (
-> p_val INT
-> )
-> BEGIN
-> IF (p_val = 1) THEN
-> SELECT '1' AS A;
-> ELSEIF (p_val = 2) THEN
-> SELECT '2' AS A;
-> ELSE
-> SELECT 'other' AS A;
-> END IF;
-> END//
Query OK, 0 rows affected (0.05 sec)

Answer 2

直接写一个触发器就好了，没必要写这么复杂的。
你的意思是如果表question_score有更新，就检查paper_score是否存在这个记录，有则修改，无则添加？

追问

是的，现在做毕业设计，还要写好多触发器