mysql存储过程书写注意事项

MYSQL 存储过程踩坑记录

原来在mssql下面写存储过程较多，在mysql下尝试遇到了很多问题，这里罗列一下：

改变

1. 在workbench的代码编辑窗口中，要用DELIMITER $$来定义多行代码的开始和结束。

 DELIMITER $$
 ...
 END$$
 DELIMITER ;

2. declare 变量时，不能用@前缀。只能定义为普通字符串。 例如：

declare birthday datetime;
declare studentName varchar(30);

3. select语句赋值到变量，两个限制：
   A、只能选择1行记录，不能有多行
   B、用into 变量的方式赋值。例如:

select name from student into studentName where gender='男' limit 1; 

C、最佳实践，列名前加表名.

declare name as varchar(30);
select student.name from student into name where gender='男' limit 1;

4. cursor的用法，mysql中cursor书写的定式：

 DECLARE done INT;
 DECLARE cur_test CURSOR FOR SELECT user_name,user_pass FROM users;
 DECLARE continue handler FOR SQLSTATE '02000' SET done = 1;
 IF param THEN INTO result FROM users WHERE id = param;
 ELSE
 	OPEN cur_test;
    repeat
    	FETCH cur_test into name,pass;
    	SELECT concat_ws(',',result,name,pass) INTO result;
    	until done
    END repeat;
    CLOSE cur_test;
END IF;

读取cursor的过程中，如果有其它的select语句，没有返回结果时，会对done造成影响，所以在中间如果用到其它的select语句赋值时，需要如下写，注意exists用法：

DECLARE done INT;
declare name varchar(30);
DECLARE cur_test CURSOR FOR SELECT user_name,user_pass FROM users;
DECLARE continue handler FOR SQLSTATE '02000' SET done = 1;
IF param THEN INTO result FROM users WHERE id = param;
ELSE
 	OPEN cur_test;
    repeat
    	FETCH cur_test into name,pass;
    	SELECT concat_ws(',',result,name,pass) INTO result;
		if exists(select student.name from student where gender='女') then
			select student.name into name from student where gender='女' limit 1
		end if
    	until done
    END repeat;
    CLOSE cur_test;
END IF;