MySQL学习笔记(十): 存储过程

一. 存储过程(PROCEDURE)

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。

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

优点:

• 存储过程可封装，并隐藏复杂的商业逻辑。

• 存储过程无法使用 SELECT 指令来运行，因为它是子程序，与查看表，数据表或用户定义函数不同。

• 存储过程可以用在数据检验，强制实行商业逻辑等。

缺点

• 存储过程，往往定制化于特定的数据库上，因为支持的编程语言不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程。
• 存储过程的性能调校与撰写，受限于各种数据库系统。

1. 创建存储过程

语法格式:

CREATE PROCEDURE <过程名> ( [过程参数[,…] ] ) <过程体>

[过程参数[,…] ]的格式 : [ IN | OUT | INOUT ] <参数名> <类型>

• 过程名: 存储过程名称

• 过程参数: MySQL 存储过程支持三种类型的参数，即输入参数、输出参数和输入/输出参数，分别用 IN、OUT 和 INOUT 三个关键字标识。其中，输入参数可以传递给一个存储过程，输出参数用于存储过程需要返回一个操作结果的情形，而输入/输出参数既可以充当输入参数也可以充当输出参数。<参数名>为参数名, <类型>为MySQL中的数据类型.

• 过程体: 包含在过程调用的时候必须执行的 SQL 语句。这个部分以关键字 BEGIN 开始，以关键字 END 结束。

例如:

# 修改命令结束标识符
DELIMITER $

# 添加一个存储过程: 增新一条订单记录后, 如果商品表中对应的商品库存小于零则回滚, 否则进行提交
CREATE PROCEDURE `insertOrder`(in _o_cuid int(11),in _o_cid int(1),in _o_num int(11),out flag int(11))			# 返回值flag表示是否新增成功
BEGIN
	set @_c_num=0;			# 临时变量:用于存储新增后商品表中的对应商品的库存
	set autocommit=0;		# 取消自动提交
	START TRANSACTION;		# 开始事务
	INSERT INTO `order` (o_cuid,o_cid,o_num) VALUES (_o_cuid,_o_cid,_o_num); # 新增订单
	set @_c_num=(select c_num from commodity where c_id=_o_cid);	# 获取新增后的库存
		IF @_c_num>=0 			# 如果新增后库存大于等于零, 则提交, 并且flag设为1
			THEN COMMIT;
			SET flag = 1;
		else ROLLBACK;			# 如果新增后库存小于零, 则回滚, 并且flag设为0
			SET flag = 0;
		END IF;
	set autocommit=1;		# 开启自动提交
END$

# 添加一个存储过程: 在更新一条订单记录后, 如果商品表中对应的商品库存小于零则回滚, 否则进行提交
CREATE PROCEDURE `updateOrder`(in _o_id INT(11),in _o_num INT(11),out flag INT(11))
BEGIN
	set @_c_num=0;
	set autocommit=0;
	START TRANSACTION;
	UPDATE `order` SET o_num=_o_num WHERE o_id=_o_id;
	set @_c_num=(select c_num from commodity where c_id=(SELECT o_cid FROM `order` WHERE o_id=_o_id));
		IF @_c_num>0 
			THEN COMMIT;
			SET flag = 1;
		else ROLLBACK;
			SET flag = 0;
		END IF;
	set autocommit=1;
END$

# 修改命令结束标识符
DELIMITER ;

注意:

1. 和触发器一样, 创建存储过程的前后都要修改命令结束标识符

**MYSQL 存储过程中的关键语法: **

1. 参数

   MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT,形式如：

CREATE PROCEDURE 存储过程名([IN | OUT | INOUT ]参数名 参数类型, ...)       

• IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
• OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
• INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量)

2. 变量

   SET @x=1;
   

   注意:

   1. MySQL所有的变量都是字符串 当你进行运算的时候 MySQL会尝试进行数值的转换 如果转换不成功则抛出异常

2. 调用存储过程

语法格式:

CALL pro_NAME([parameter[,......]]);

例如:

set @x=-1;		# 定义一个变量,用来接受insertOrder的返回值
call insertOrder(1, 10, 5, @x);	# 调用insertOrder存储过程,新增了一条记录:1号客户购买了5件10号商品, 并将是否新增成功保存在了@x变量中
select @x;		# 输出@x变量的值

3.查看存储过程

• 查看数据库中的存储过程和状态

  show procedure status;
  

• 查看存储过程的创建代码

  show create procedure 存储过程名;
  

4. 删除存储过程

drop procedure 存储过程名;

5. 修改存储过程

MySQL中修改存储过程可以通过删除原存储过程，再以相同的名称创建新的存储过程。