mysql存储过程的使用实例讲解

最近遇到需要用存储过程解决一些问题，因为之前没接触过完全不懂，过程中遇到很多问题也踩了很多坑。

存储过程的概念：

存储过程是一组可编程的函数，是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

对存储过程的理解：

我个人理解存储过程跟编程时的函数没有什么区别，只是运用的地方不同而已，存储过程运用在数据库系统中，通过sql调用，创建一个存储过程就相当于创建了一个sql函数，可以传入参数（当然肯定也能没有参数）实现某种认定的功能。这其中有一点小差异那就是：编程时函数传入的参数都是输入参数（特意强调输入参数是为了和存储过程的参数作区分），函数执行完后会有一个返回值；而存储过程中申明时既要指定输入参数，也要指定输出参数（前提是有必要用到）。

存储过程的使用流程：

关于mysql存储过程的基本语法，请看mysql存储过程基本语法

先创建：创建一张名叫test_pro的表，只包含一个字段test_name，创建一个存储过程往其中插入一条数据。

#指定界定符(因为;是mysql默认的语句结束符，而存储过程中需要一组语句同时执行，所以需重新指定界定符号)
DELIMITER ;;
#创建存储过程
CREATE PROCEDURE my_test()

#存储过程体
BEGIN
		#申明变量
		DECLARE test_name VARCHAR(100);
		#给变量赋值
		SET test_name= 'my_test';
		#执行插入操作
		INSERT INTO `test_pro`(`test_name`) VALUES(test_name);

END;;
#恢复mysql默认界定符
DELIMITER;

后调用：调用存储过程时采用 call 存储过程名称 的语法，执行 CALL my_test();

以上是创建了一个不带参数的存储过程，是最简单的使用，复杂的存储过程也是这个流程，主要区别是在于Begin-End块中的逻辑。

存储过程使用实例：

实例中用到了3张表：学生表student、课程表：course、学生选课表：course_subscribe。用存储过程模拟学生选择操作，执行完后选课表中会出现学生选课的数据。

带输出参数的存储过程

创建存储过程包含输入参数student_name_in、course_name_in，根据传入的学生名和课程名，存储过程往选课表中插入记录。

（ps:下面的示例中都是完整的存储过程代码，如果在navicat等工具中编辑，只需要关注Begin-End块中的代码即可）

DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `subscribe_course2`(IN `student_name_in` varchar(100),IN `course_name_in` varchar(100))
BEGIN
	
	#申明变量
	DECLARE v_student_id INT(8);
	DECLARE v_course_id VARCHAR(100);
	
	#给变量赋值
	SELECT student_id INTO v_student_id FROM student WHERE student_name = student_name_in;
	SELECT course_id INTO v_course_id FROM course WHERE course_name = course_name_in;
	
	#插入
	INSERT INTO course_subscribe(student_id,course_id) VALUES(v_student_id,v_course_id);

END
;;
DELIMITER ;

调用存储过程：

call subscribe_course2('Tom','语文');，成功插入一条记录往选课表

创建存储过程包含输入参数student_name_in、course_name_in，输出参数student_age_out，根据传入的学生名和课程名，存储过程往选课表中插入记录，并且输出当前选课学生的年龄。

DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `subscribe_course3`(IN `student_name_in` varchar(100),IN `course_name_in` varchar(100),OUT `student_age_out` int)
BEGIN
	
	#申明变量
	DECLARE v_student_id INT(8);
	DECLARE v_course_id VARCHAR(100);
	
	#给变量赋值
	SELECT student_id INTO v_student_id FROM student WHERE student_name = student_name_in;
	SELECT course_id INTO v_course_id FROM course WHERE course_name = course_name_in;
	
	#插入
	INSERT INTO course_subscribe(student_id,course_id) VALUES(v_student_id,v_course_id);

	SELECT student_age INTO student_age_out FROM student WHERE student_name = student_name_in;

END
;;
DELIMITER ;

调用存储过程：其中@course_num是输出参数，可以通过输出参数名称引用存储过程的返回值，此出select 返回值让其输出。

call subscribe_course3('Jams','数学',@course_num);

SELECT @course_num;

踩过的坑：

因为自己开发使用了navicat工具，创建存储时没有用命令行sql，而是使用了可视化的向导，向导在创建存储过程时不会给参数指定默认长度，会导致报错,需要手动将其长度加上可以避免报错的问题，（所以说可视化工具虽然方便了操作，但是无形间挖了很多坑）。

sql参数必须以@符号开头，是在有输出参数的存储过程调用时发现的。

这个涉及到变量作用域的问题，在前面的存储过程体中能看到使用declare声明变量时是直接命名变量，这里将其称为存储过程变量，其作用域只是存储过程语句体内部（即Begin-End体内）。mysql数据库中默认变量以@符号开头称为用户变量，其作用域是当前会话，即会话作用域。

有关mysql变量及其作用域的更多介绍，请看mysql定义变量