MySQL 存储过程
1.1 简介
1.1.1 概述
??MySQL 5.0 版本开始支持存储过程。存储过程(Stored Procedure)是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。存储过程是为了完成特定功能的 SQL 语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。
1.1.2 优缺点
? 优点 ?① 存储过程可封装，并隐藏复杂的商业逻辑。 ?② 存储过程可以回传值，并可以接受参数。 ?③ 存储过程无法使用 select 指令来运行，因为它是子程序，与查看表，数据表或用户定义函数不同。 ?④ 存储过程可以用在数据检验，强制实行商业逻辑等。
? 缺点 ?① 存储过程，往往定制化于特定的数据库上，因为支持的编程语言不同。当切换到其他的数据库系统时，需要重写原有的存储过程。 ?② 存储过程的性能调校与撰写，受限于各种数据库系统。
1.1.3 阿里规约
1.2 存储过程的使用
1.2.1 创建存储过程
? 语法
create procedure 存储过程名([参数模式] 参数名 参数类型)
begin
# 变量定义
declare name type default value;
# 变量赋值
SET name = value;
存储过程体
end? 参数模式
in：该参数可以作为输入，也就是该参数需要调用方传入值。
out：该参数可以作为输出，也就是说该参数可以作为返回值。
inout：该参数既可以作为输入也可以作为输出，也就是说该参数需要在调用的时候传入值，又可以作为返回值。
? 注意
?① 参数模式默认为 in。 ?② 一个存储过程可以有多个输入、多个输出、多个输入输出参数。
? 示例
# 无参数
mysql> create procedure showtest()
begin
select * from test;
end;
Query OK, 0 rows affected (0.07 sec)
# in 参数
mysql> create procedure instest(id int, money varchar(20))
begin
insert into test values (id, money);
end;
Query OK, 0 rows affected (0.06 sec)
# out 参数
mysql> create procedure test1(out a int, out b int)
begin
set a = 2 * 2;
select count(*) into b from test;
end;
Query OK, 0 rows affected (0.08 sec)
mysql> call test1(@a,@b);
Query OK, 1 row affected (0.06 sec)
mysql> select @a,@b;
+----+----+
| @a | @b |
+----+----+
| 4 | 4 |
+----+----+
1 row in set (0.12 sec)
# 使用流程控制语句
mysql> create procedure sumnum()
begin
# default 是指定该变量的默认值
declare sum int default 0;
declare i int default 1;
declare a int default 100;
while i <= a do # 循环开始
set sum = sum + i;
set i = i + 1;
end while; # 循环结束
select sum; # 输出结果
end;
Query OK, 0 rows affected (0.06 sec)1.2.2 查看存储过程
? 语法
show create procedure 存储过程名称;? 示例
1.2.3 调用存储过程
? 语法
call 存储过程名称(参数列表);? 示例
mysql> call showtest;
+----+-------+
| id | money |
+----+-------+
| 1 | 1000 |
| 2 | 1000 |
+----+-------+
2 rows in set (0.12 sec)
Query OK, 0 rows affected (0.05 sec)
mysql> call instest(3, '1000');
Query OK, 1 row affected (0.07 sec)
mysql> call showtest;
+------+-------+
| id | money |
+------+-------+
| 1 | 1000 |
| 2 | 1000 |
| 3 | 1000 |
+------+-------+
3 rows in set (0.10 sec)
Query OK, 0 rows affected (0.05 sec)1.2.4 修改存储过程
存储过程不能修改，只能先删除，然后重新创建
1.2.5 删除存储过程
? 语法
# if exites 可以省略，但是不建议
drop procedure [if exists] 存储过程名称;? 示例
mysql> drop procedure if exists showtest;
Query OK, 0 rows affected (0.07 sec)1.3 自定义函数
1.3.1 创建函数
? 语法
# 参数可选, 返回值必须要有，否则会报错
create function 函数名(参数名称 参数类型) returns 返回值类型
begin
函数体
end? 示例
mysql> create function fun() returns int
begin
declare sum int;
select count(*) into sum from test;
return sum;
end;
Query OK, 0 rows affected (0.07 sec)1.3.2 查看函数
? 语法
show create function 函数名;? 示例
1.3.3 调用函数
? 语法
select 函数名(实参列表);? 示例
mysql> select fun();
+-------+
| fun() |
+-------+
| 3 |
+-------+
1 row in set (0.14 sec)1.3.4 删除函数
? 语法
drop function [if exists] 函数名;? 示例
mysql> drop function if exists fun;
Query OK, 0 rows affected (0.07 sec)1.3.5 存储过程与函数的区别
??存储过程的关键字为 procedure，返回值可以有多个，调用时用 call，一般用于执行比较复杂的的过程体、更新、创建等语句。函数的关键字为 function，返回值必须有一个，调用用 select，一般用于查询单个值并返回。