存储过程

存储过程

1.什么是存储过程
存储过程是一种在数据库中存储复杂程序，以便外部调用的一种数据库对象。存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过制定存储过程的名字并给定参数（需要时）来调用执行。就是数据库SQL语言层面的代码封装与重用。MySQL5.0开始支持存储过程。
2.存储过程的优点和缺点
优点：
（1）重复使用。存储过程可以重复使用，从而减少开发人员的工作量。
（2）减少网络流量。存储过程在服务器上，调用时只需传递存储过程的名称及参数即可，从而降低网络传输的数据量。
（3）安全性高。可设定只用某用户才具有对制定存储过程的使用权，参数化的存储过程还可防止sql注入。
缺点：
（1）数据库移植不方便:
（2）大量采用存储过程进行业务逻辑的开发致命的缺点是很多存储过程不支持面向对象的设计，无法采用面向对象的方式将业务逻辑进行封装，从而无法形成通用的可支持复用的业务逻辑框架。
（3） 代码可读性差,相当难维护,
（4）不支持群集
3.存储过程的使用
（1）创建存储过程：CREATE PROCEDURE 存储过程名
例：

mysql> delimiter//　　#将语句的结束符号从分号;临时改为两个//(可以是自定义)
mysql> CREATE PROCEDURE productroom()
    -> BEGIN
    -> 　　SELECT avg(room_price) AS priceavg FROM rooms;
    -> END//
Query OK, 0 rows affected (0.01 sec)
 
mysql> delimiter;　　#将语句的结束符号恢复为分号

解析：默认情况下，存储过程和默认数据库相关联，如果想指定存储过程创建在某个特定的数据库下，那么在过程名前面加数据库名做前缀。 在定义过程时，使用 DELIMITER // 命令将语句的结束符号从分号 ; 临时改为两个 //，使得过程体中使用的分号被直接传递到服务器，而不会被客户端（如mysql）解释。
（2）执行存储过程用：CALL 存储过程名
例：

mysql> CALL productroom();

执行productroom的存储过程，
(3)删除存储过程：DROP PROCEDURE存储过程名；
例：

mysql> DROP PROCEDURE productroom;

（4）使用参数：CREATE PROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类形…])
共三种参数类型：
IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）
例：

mysql> delimiter //
mysql> CREATE PROCEDURE productroom(
	-> 		OUT pricemax DECIMAL(8,2),
	-> 		OUT pricemin DECIMAL(8,2),
	-> 		OUT priceavg DECIMAL(8,2)
	-> )
	-> BEGIN
	->		SELECT max(room_price) INTO pricemax FROM rooms;
	->		SELECT min(room_price) INTO pricemin FROM rooms;
	->		SELECT avg(room_price) INTO priceavg FROM rooms;
	->END//
mysql> delimiter;

执行：

mysql> call productroom(@pricemax, @pricemin, @priceavg);

查看：

mysql> SELECT @pricemax, @pricemin, @priceavg;

例：

mysql> delimiter //
·		mysql> CREATE PROCEDURE productroom(
	-> 		IN rid INT,
	-> 		OUT pricetotal DECIMAL(8,2)
	->)
	->BEGIN
	->		SELECT room_price*room_num INTO pricetotal FROM rooms WHERE id=rid;
	->END//
mysql> delimiter;

执行：

mysql> call productroom(2, @pricetotal);

查看：

mysql> SELECT @pricetotal;