MySQL存储过程

1. 存储过程的概念

概念：使用多条语句完成业务的操作。简单的定义存储过程就是多条SQL的集合。

特点：

• 使用存储过程能简化复杂的单条SQL，相比于单条复杂的SQL极大提高了性能；
• 如果表结构发生变化，只需改变存储过程使用到SQL语句的表名，如果业务逻辑发生变化，只需跳转存储过程即可，具有很强的灵活性；
• 建立一次存储过程即可使用，不用反复建立，保证开发人员使用到都是相同的存储过程，保证数据可靠性
• 总的来说就是：使用存储过程简单、灵活、安全可靠、性能好

2. 存储过程语法

1. 创建存储过程

Create PROCEDURE 存储过程名称 (参数列表)
begin过程体
end;

2. 参数列表

• IN 输入

IN var1 Declmal(6,2)

• OUT 输出

IN var2 Decimal(6,2)

• INOUT 输入输出

IN var3 Decimal(6,2)

3. 变量

declare 变量名称 变量类型 [default value]

4. 执行存储过程

call 存储过程名称

5. 删除存储过程

DROP PROCEDURE 存储过程名称

6. 赋值

使用 set 和 select into 语句为变量赋值

set @var := 20
select sum(price) into total from table_name

7. if语句

f 条件 then表达式
[elseif 条件 then表达式]
...
[else表达式]
end if;

8. case语句

CASE 值 WHTN 匹配值 THEN 结果
[WHEN 匹配值 THEN 结果]
......
[ELSE 结果]
END

9. while语句

[开始标签:]while 条件 do循环体
[结尾标签]
end while;

10. loop语句

[开始标签:] loop
语句体
[结尾标签]
end loop;

11. iterate/leave语句

通过标签可以实现：

• iterate 表示迭代
• leave 表示离开

12. repeat语句

repeat
--循环体
until 循环条件
end repeat;

知识点：如果用命令行学习，在写多行SQL时 使用 //可实现换行。

3. 存储过程实例

准备张表order_detail并插入几条数据

CREATE TABLE `order_detail` (`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',`detail_name` varchar(255) DEFAULT NULL COMMENT '订单明细',`price` decimal(10,2) DEFAULT NULL COMMENT '价格',`oid` int(11) DEFAULT NULL COMMENT '订单id',PRIMARY KEY (`id`)
)

1. 无参存储过程

• 查看订单明细的所有订单名称，跟普通的查询语句没区别

CREATE PROCEDURE slelect_detail ( ) BEGIN
SELECTdetail_name 
FROMorder_detail;
END;

• 再调用存储过程

CALL slelect_detail ( );

• 此时就会打印内容：
  

• 删除存储过程

DROP PROCEDURE slelect_detail;

2. 带入参储存过程示例

• 查询oid为动态的所有订单明细名称，考虑到oid为动态，需要用户输入，故将oid作为入参：

CREATE PROCEDURE slelect_detail ( IN order_id INT ) BEGIN
SELECTdetail_name 
FROMorder_detail 
WHEREoid = order_id;
END;

• 调用存储过程，只查询oid为1的用户的订单明细名称

call slelect_detail(1);

• 打印内容如下：
  
• 删除存储过程

DROP PROCEDURE slelect_detail;

3. 带入参和出参的存储过程示例

• 查询任意用户的订单明细的所有金额：定义入参订单id为order_id，输出总金额为total

CREATE PROCEDURE slelect_toatal_money ( IN order_id INT, OUT total DECIMAL ( 8, 2 ) )
BEGIN
SELECTsum( price ) INTO total 
FROMorder_detail 
WHEREoid = order_id;
END;

• 调用存储过程示例

CALL slelect_toatal_money ( 1, @total );

• 查询order_id为1总金额示例

SELECT @total;

• 输出结果：
  
• 删除存储过程

drop PROCEDURE slelect_toatal_money;

4. if语句示例

使用控制流程，实现复杂的存储过程。

对输入的order_id自动加5，然后判断var是否小于7，如果是就查询订单明细价格，否则查询订单明细价格总和：

create procedure slelect_toatal_money(IN order_id INT)
begin
-- 定义变量
declare var  int;
-- 赋值
set var= order_id+5;
-- if 判断
if var<7 then
select price  from oder_detail where oid = order_id;
else
select sum(price)  from oder_detail where oid = order_id;
end if;
end;

• 调用

CALL slelect_toatal_money(1);

• 查询结果：
  

• 调用

 CALL slelect_toatal_money(2);

• 查询结果：
  
• 删除存储过程

知识点：

loop、while、repeat、iterate都是循环 loop、while、repeat功能几乎相同 iterate可通过标签的形式调用循环，与leave语句使用方式一样

CALL slelect_toatal_money(1);

结果 会出现 2组相同 结果：

CREATE PROCEDURE slelect_toatal_money(IN order_id INt)
BEGIN-- 定义变量DECLARE var INT;-- 赋值SET var = order_id + 5;-- repeat循环REPEATSELECT price FROM order_detail WHERE oid = order_id;SET var = var + 1;UNTIL var > 7END REPEAT;
END;

调用示例

8. repeat

repest与while不同之处：while在执行之前检查条件；repest在执行之后检查条件：

CALL slelect_toatal_money(2);

结果：

CALL slelect_toatal_money(1);

结果会输出 3组结果：
调用示例

CREATE PROCEDURE slelect_toatal_money(IN order_id INT)
BEGIN-- 定义变量DECLARE var INT;-- 赋值SET var := order_id;-- loopselect_loop : LOOPSELECT price + var FROM order_detail WHERE oid = order_id;SET var = var + 1;-- 跳出循环IF var > 3 THENLEAVE select_loop;END IF;END loop;
END;

调用示例

7. loop语句

如果var小于3就计算 价格+var 的值

CALL slelect_toatal_money(2);

结果：

call slelect_toatal_money(1);

结果：
调用示例

CREATE PROCEDURE slelect_toatal_money(IN order_id INT)
BEGIN
-- 定义变量
DECLARE var INT;
-- 赋值
SET var := order_id;
-- case 判匹配
CASE var
WHEN 1 THENSELECT price FROM order_detail WHERE oid = order_id;
WHEN 2 THENSELECT SUM(price) FROM order_detail WHERE oid = order_id;END CASE;
END;

调用示例

6. case语句示例

下边语句实现的效果与上面if语句实现效果一致：

CALL slelect_toatal_money ( 1 );

输出：

CREATE PROCEDURE slelect_toatal_money(IN order_id INT)
BEGIN
-- 定义变量
DECLARE var INT;
-- 赋值
SET var = order_id + 5;
-- while
while var < 7 DOSELECT price FROM order_detail WHERE oid = order_id;SET var = var + 1;END WHILE;
END;

调用示例

DROP PROCEDURE slelect_toatal_money;

5. while 语句示例

对 变量var进行判断，如果var<7就指向查询价格语句，并且var进行自增