首页 > 代码库 > MySQL之存储过程
MySQL之存储过程
引言
在Mysql中无论是在定义上还是在使用上，自定义函数和存储过程有很多相似的地方，所以我们来简单了解下什么是存储过程以及存储过程的实现方式。以及他们之间的区别。
定义
存储过程的定义是一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。它首先是一组能够完成特定功能的SQL集合，经过编译后存储在数据库中，是通过用户指定的存储过程的名字来调用的。
优势
存储过程在数据库中有很大的应用范围，可是它有什么优势呢
- 增强SQL语句的功能和灵活性：当对数据库进行复杂操作，可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
- 实现较快的执行速度：存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
- 减少了网络流量
- 存储过程可以重复使用,可减少数据库开发人员的工作量
- 安全性高,可设定只有某此用户才具有对指定存储过程的使用权
语法结构及说明
如果要定义一个存储过程，它需要满足下面的语法结构
CREATE[DEFINER={USER|CURRENT_USER}] //不指定则默认为当前用户PROCEDURE sp_name[pro_parameter[,....]][characteristic..]routine_bodyproc_parameter:[IN|OUT|INOUT|] parameter_name type
关于存储过程的定义有几点需要说明：
1.在定义存储过程的时候我们需要制定定义者，如果不知道用户那么就默认是当前数据库的用户
2.定义存储过程的名字和定义自定义函数的名字一样，需要指定属于哪一个数据库的存储过程，默认是当前数据库的
3.存储过程的参数可以是零个也是是一个或者多个
4.特性（characteristic）和自定义函数基本一致，包括
contrains sql：包含 sql 语句，但不包含读或写数据的语句no sql：不包含 sql 语句reads sql data：包含读数据的语句modifies sql data：包含写数据的语句sql security {default | invoker} 指明谁有权限来执行
5.参数的类型
IN,表示该参数必须在调用存储过程的时候指定OUT,表示该参数的值可以被存储过程改变，并且可以返回INPUT，在调用时候指定能够被存储过程改变然后返回
6.存储过程的存储体
存储过程的过程体（基本上和函数体的要求相同）：A、由合法的 sql 语句构成B、复合结构使用 begin ... end 语句C、复合结构可以包含声明、循环、控制结构
7.调用
如果存储过程是有参数的必须使用sp_name(参数)的形式调用，如果没有参数的话可以使用sp_name(）的形式也可以采用sp_namde的形式直接调用。
实现说明
1.无参的存储过程实现及调用
（获取当前时间）
mysql> CREATE PROCEDURE getDate SELECT NOW();ERROR 1046 (3D000): No database selectedmysql> CREATE PROCEDURE TEST.getDate SELECT NOW();ERROR 1064 (42000): You have an error in your SQL syntax; check the manual thatcorresponds to your MySQL server version for the right syntax to use near ‘SELECT NOW()‘ at line 1mysql> CREATE PROCEDURE TEST.getDate() SELECT NOW();Query OK, 0 rows affected (0.05 sec)mysql> USE TEST;Database changedmysql> CALL getDate;+---------------------+| NOW() |+---------------------+| 2014-11-24 21:18:01 |+---------------------+1 row in set (0.05 sec)Query OK, 0 rows affected (0.06 sec)mysql> CALL getDate();+---------------------+| NOW() |+---------------------+| 2014-11-24 21:18:05 |+---------------------+1 row in set (0.00 sec)Query OK, 0 rows affected (0.01 sec)
2.含有IN参数的存储过程的实现
（获取某一ID的匹配记录）
mysql> DELIMITER $$mysql> SHOW TABLES; -> $$+----------------+| Tables_in_test |+----------------+| t1 || t2 || t3 || t5 || t6 || t7 |+----------------+6 rows in set (0.00 sec)mysql> SELECT * FROM T1$$+----+------+----------+---------+-------+-------+| id | name | password | test001 | test3 | test4 |+----+------+----------+---------+-------+-------+| 2 | 1 | 1 | 1 | 1 | 1 || 3 | 3 | 4 | 5 | 6 | 7 || 4 | 3 | 4 | 5 | 6 | 7 || 5 | 3 | 4 | 5 | 6 | 7 || 6 | 3 | 4 | 5 | 6 | 7 || 7 | 3 | 4 | 5 | 6 | 7 || 8 | 3 | 4 | 5 | 6 | 7 || 9 | 3 | 4 | 5 | 6 | 7 || 10 | 3 | 4 | 5 | 6 | 7 |+----+------+----------+---------+-------+-------+9 rows in set (0.00 sec)mysql> CREATE PROCEDURE fetchById(IN id INT UNSIGNED) -> BEGIN -> SELECT * FROM T1 WHERE id = id; -> END -> $$Query OK, 0 rows affected (0.00 sec)mysql> CALL fetchById(5)$$+----+------+----------+---------+-------+-------+| id | name | password | test001 | test3 | test4 |+----+------+----------+---------+-------+-------+| 2 | 1 | 1 | 1 | 1 | 1 || 3 | 3 | 4 | 5 | 6 | 7 || 4 | 3 | 4 | 5 | 6 | 7 || 5 | 3 | 4 | 5 | 6 | 7 || 6 | 3 | 4 | 5 | 6 | 7 || 7 | 3 | 4 | 5 | 6 | 7 || 8 | 3 | 4 | 5 | 6 | 7 || 9 | 3 | 4 | 5 | 6 | 7 || 10 | 3 | 4 | 5 | 6 | 7 |+----+------+----------+---------+-------+-------+9 rows in set (0.00 sec)Query OK, 0 rows affected (0.02 sec)
通过上面的例子我们发现我们成功的定义了存储过程fetchById(int)，可是我们却没有取到我们想要的结果我们拿到了所有的数据，那么是什么地方出问题了呢？？我们发现原来是定义的存出结果的参数名和我们数据库的列名相等了，而这会导致的结果就是参数的值将不会是你传入的值，而是变成每条记录的那个字段的值。所以我们需要注意的一定就是在定义存储结构的时候参数名称不能够和数据库列名相同。
知道了问题所在，我们只需要修改就行了。和自定义函数一样，存储过程是不能够修改名称和结构体的，因为我们只能删掉后重新构建了，下面的SQL语句就实现了这个功能。
mysql> CREATE PROCEDURE fetchById(IN t_id INT UNSIGNED) -> BEGIN -> SELECT * FROM T1 WHERE id = t_id; -> END -> $$Query OK, 0 rows affected (0.00 sec)mysql> CALL fetchById(5); -> $$+----+------+----------+---------+-------+-------+| id | name | password | test001 | test3 | test4 |+----+------+----------+---------+-------+-------+| 5 | 3 | 4 | 5 | 6 | 7 |+----+------+----------+---------+-------+-------+1 row in set (0.03 sec)Query OK, 0 rows affected (0.04 sec)mysql> DELIMITER ;mysql> CALL fetchById(5);+----+------+----------+---------+-------+-------+| id | name | password | test001 | test3 | test4 |+----+------+----------+---------+-------+-------+| 5 | 3 | 4 | 5 | 6 | 7 |+----+------+----------+---------+-------+-------+1 row in set (0.00 sec)Query OK, 0 rows affected (0.01 sec)
3.创建带有IN和OUT类型参数的存储过程
（删除一条记录并且返回删除记录前后记录的总数目）
mysql> CREATE PROCEDURE delBeforeAndAfter(IN t_id INT UNSIGNED,OUT beforeSum INT UNSIGNED,OUT afterSum INT UNSIGNED) -> BEGIN -> SELECT COUNT(1) FROM T1 INTO beforeSum; -> DELETE FROM T1 WHERE id=t_id; -> SELECT COUNT(1) FROM T1 INTO afterSum; -> END -> $$Query OK, 0 rows affected (0.03 sec)
mysql> DELIMITER ;
mysql> SELECT COUNT(1) FROM T1;
+----------+
| COUNT(1) |
+----------+
| 9 |
+----------+
1 row in set (0.00 sec)
mysql> CALL delBeforeAndAfter(5,@beforeSum,@afterSum);
Query OK, 1 row affected (0.04 sec)
mysql> SELECT @beforeSum,@afterSum;
+------------+-----------+
| @beforeSum | @afterSum |
+------------+-----------+
| 9 | 8 |
+------------+-----------+
1 row in set (0.00 sec)
通过传入参数5，我们成功的返回删除前后删除后的记录总数。存储过程得到了实现。
与自定义函数的区别
1、存储过程实现的功能相对复杂，函数针对性较强：在实际的工作中，我们对表的操作经常是使用存储过程来实现的很少使用函数。
2、存储过程可以返回多个值，函数只能有一个返回值
3、存储过程一般独立执行，函数可以作为 sql 语句的组成部分来出现
这只是一些简单的区别，如果日后了解更多时再更新文章。
MySQL之存储过程