MySQL存储过程

基本语法

CREATE PROCEDURE sp_name ([proc_parameter[,...]])proc_parameter(参数类型):[ IN | OUT | INOUT ] param_name type其中IN表示输入参数,OUT表示输出参数,INOUT表示既能传入值又能传出值。

一个小练习

问题：有一张学生信息表，如下，需要写一个存储过程，输入学号id，计算小于这个id的学生的平均成绩。

-- ------------------------------ Table structure for student-- ----------------------------DROP TABLE IF EXISTS `student`;CREATE TABLE `student` (  `id` tinyint(4) NOT NULL,  `name` varchar(255) DEFAULT NULL,  `value` tinyint(255) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;-- ------------------------------ Records of student-- ----------------------------INSERT INTO `student` VALUES ('1', 'aaa', '90');INSERT INTO `student` VALUES ('2', 'bbb', '91');INSERT INTO `student` VALUES ('3', 'ccc', '30');INSERT INTO `student` VALUES ('4', 'ddd', '93');INSERT INTO `student` VALUES ('5', 'eee', '94');

答案：

-- 这个存储过程的作用是：输入一个学号，-- 计算小于这个学号的学生的平均成绩-- 定义换行符delimiter //DROP PROCEDURE IF EXISTS sp_getave;-- 创建存储过程sp_getave,-- 有两个参数，分别是一个输入参数，一个输出参数CREATE PROCEDURE sp_getave(IN input INT, OUT output INT)-- 存储过程开始BEGIN-- 存储过程内容    SELECT         @sum := @sum + value     FROM student s, (SELECT @sum := 0) v    WHERE s.id < input;    SET output := @sum / (input - 1);-- 存储过程结束，注意：END后需要加上换行符标志，即，最开始定义的END //-- 将换行符换回来delimiter ;-- 执行CALL sp_getave(4,@output);-- 查看输出结果SELECT @output;

存储过程中常用的小知识点

由于存储过程是是一段实现定义的程序，所以，存储过程的变量使用方法如下：

declare @ID varchar(20)      --定义一个变量叫@IDset @ID=3                    --变量赋值为3select @ID=1                 --变量赋值为1，并显示

另外，存储过程同样支持循环，条件语句等程序控制语句，有需求在自行搜索。

优缺点

优点

1. 运行速度：对于很简单的sql，存储过程没有什么优势。对于复杂的业务逻辑，因为在存储过程创建的时候，数据库已经对其进行了一次解析和优化。存储过程一旦执行，在内存中就会保留一份这个存储过程，这样下次再执行同样的存储过程时，可以从内存中直接调用，所以执行速度会比普通sql快。

2. 减少网络传输：存储过程直接就在数据库服务器上跑，所有的数据访问都在数据库服务器内部进行，不需要传输数据到其它服务器，所以会减少一定的网络传输。但是在存储过程中没有多次数据交互，那么实际上网络传输量和直接sql是一样的。而且我们的应用服务器通常与数据库是在同一内网，大数据的访问的瓶颈会是硬盘的速度，而不是网速。

3. 可维护性：的存储过程有些时候比程序更容易维护，这是因为可以实时更新DB端的存储过程。 有些bug，直接改存储过程里的业务逻辑，就搞定了。

4. 增强安全性：提高代码安全，防止 SQL注入。这一点sql语句也可以做到。

5. 可扩展性：应用程序和数据库操作分开，独立进行，而不是相互在一起。方便以后的扩展和DBA维护优化。

缺点

1. SQL本身是一种结构化查询语言，但不是面向对象的的，本质上还是过程化的语言，面对复杂的业务逻辑，过程化的处理会很吃力。同时SQL擅长的是数据查询而非业务逻辑的处理，如果如果把业务逻辑全放在存储过程里面，违背了这一原则。

2. 如果需要对输入存储过程的参数进行更改，或者要更改由其返回的数据，则您仍需要更新程序集中的代码以添加参数、更新调用，等等，这时候估计会比较繁琐了。

3. 开发调试复杂，由于IDE的问题，存储过程的开发调试要比一般程序困难。

4. 没办法应用缓存。虽然有全局临时表之类的方法可以做缓存，但同样加重了数据库的负担。如果缓存并发严重，经常要加锁，那效率实在堪忧。

5. 不支持群集，数据库服务器无法水平扩展，或者数据库的切割（水平或垂直切割）。数据库切割之后，存储过程并不清楚数据存储在哪个数据库中。

总结

存储过程的合理使用可以提高数据库性能，同时，如果可以在程序中操作的尽量避免使用存储过程，因为不好维护。