mysql存储过程需要注意的

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;  当查不到值时会报错运行中断。