1.当使用游标赋值结果集时,变量名不能使用列明,不然赋值为null
BEGIN
DECLARE stopindex INT DEFAULT 0;
DECLARE id1 int;
DECLARE title VARCHAR(1000);
DECLARE description VARCHAR(1000);
DECLARE insertdate DATETIME;
DECLARE username VARCHAR(1000);
DECLARE closed int;
DECLARE commandtype VARCHAR(1000);
DECLARE
cur CURSOR FOR (
<span style="color:#FF0000;">select id,zlmc,zlnr,fbsj,fbr,kg,zldj from jiaowei.five_instructions</span>
);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopindex = 1;
OPEN cur;
<span style="color:#FF0000;">#如此处,游标查询的表有id字段,则遍历赋值时,不能再用id做变量,不然赋值字段为null</span>
FETCH cur INTO <span style="color:#FF0000;">id1</span>,title,description,insertdate,username,closed,commandtype;
WHILE (stopindex !=1) DO
if closed=0 THEN
set closed=1;
ELSE
set closed=0;
end if;
insert into topic set id=id1,title=title,description=description,insertdate=insertdate
,username=username,closed=closed,commandtype=commandtype,msgtype=0,type='指令';
FETCH cur INTO id1,title,description,insertdate,username,closed,commandtype;
END WHILE;
CLOSE cur;
END
2.当变量赋值使用 select id into 变量名 from student where name= "ffff" limit 0,1; 当查不到值时会报错运行中断。