mysql 存储过程

1.概念

存储过程和函数可以理解为一段 SQL 语句的集合，它们被事先编译好并且存储在数据库中。在 Pascal 语言中，是有”过程”和”函数”的区分的，过程可以理解为 没有返回值的函数。不过在 C 家族语言中，则没有过程这个概念， 统一为函数。

创建一个存储过程的语法为：

create procedure 存储过程名(参数列表)
begin
存储过程体
end
call 存储过程名(参数列表)

简单存储过程

-- 创建存储过程
CREATE PROCEDURE users (IN x INT) -- in 表示输入
BEGIN
	SELECT * FROM `users` WHERE id = x;
END

执行

-- 执行
call user_procedure(1);

删除

-- 删除存储过程
DROP PROCEDURE users;

2.参数类型

1. 存储过程有参数类型这种说法，它的类型可以取值有三个：in、out、inout。

2. 其中它们的意义如下：

(1) in 表示只是用来输入。

(2) out 表示只是用来输出。

(3) inout 可以用来输入，也可以用作输出。

DROP PROCEDURE user_out;
CREATE PROCEDURE user_out (IN x INT, OUT y VARCHAR(100))
BEGIN
	SELECT name INTO y FROM `users` WHERE id = x;
END -- 执行

CALL user_out(2,@a);
select @a

-- 查询存储过程
show create PROCEDURE user_out; 

3.存储过程理解

1. 调用存储过程与直接执行 SQL 语句的效果是相同的，但是存储过程的一个好处是处理逻辑都封装在数据库端。

2. 当我们调用存储过程的时候，我们不需要了解其中的处理逻辑，一旦处理逻辑发生变化，只需要修改存储过程即可，对调用它的程 序完全无影响。

3. 调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，可以提高数据处理的效率

4. 变量

1. 存储过程中是可以使用变量的，可以通过 declare 来定义一个局部变量，该变量的作用域只是 begin....end 块中。

2. 变量的定义必须写在符合语句的开头，并且在任何其他语句的前面。我们可以一次声明多个相同类型的变量，我们还可以使用default 来赋予默认值。

3. 定义一个变量的语法为： declare 变量名 1 [,变量名 2...] 变量类型 [default 默认值]

4. 上面的变量类型就是 MySQL 支持的类型。

5. 变量可以直接赋值，还可以通过查询赋值。

6. 直接赋值就是使用 set 来进行赋值，它的语法为： set 变量名 1 = 表达式 1 [,变量名 2=表达式 2...]

7. 也可以通过查询来将结果赋值给变量，它需要要求查询返回的结果只有一行，语法范例： select 列名列表 into 变量列表 from 表名 其他语句；

create PROCEDURE user_out2(in x int, out y varchar(50))
BEGIN
declare s varchar(50) ;
select name into s from `users` where id = x;
set y = s;
END
-- 执行
call user_out2(2, @a);
select @a

5.存储过程中的数据类型

1. 数值类型：Int,float,double,decimal

2. 日期类型：timestamp,date,year

3. 字符串：char,varchar,text

timestamp: 是使用最多的数据类型-》十位数的时间戳

text：一旦用到text类型的时候就可以考虑分表；

如果部分表的话，该字段的查询不会直接放在一起查 询，因为多个字段查询中其中如果有text字段的话，就容易遇到慢查询 所以通常的话，如果需要这个值的时候会根据id单独拿这个text字段

6. 流程控制语句其他语法

if 的语法格式为：
if 条件表达式 then 语句
[elseif 条件表达式 then 语句] ....
[else 语句]
end if
case 的语法格式
首先是第一种写法：
case 表达式
when 值 then 语句
when 值 then 语句
...
[else 语句]
end case
然后是第二种写法：
case
when 表达式 then 语句
when 表达式 then 语句
....
[else 语句]
end case
loop 循环 语法格式为：
[标号:] loop
循环语句
end loop [标号]
while
while a>100 do
循环语句
End while
Repeat //游标
SQL语句1
UNTIL 条件表达式
END Repeat;
Loop
SQL语句
所有的条件判断和跳出需要自己实现
End loop
leave 语句用来从标注的流程构造中退出，它通常和 begin...end 或循环一起使用
leave 标号;
声明语句结束符，可以自定义:
DELIMITER [符合]
delimiter $$
$$

7.游标

1. 游标也有的资料上称为光标。

2. 我们可以在存储过程中使用游标来对结果集进行循环的处理。

3. 游标的使用步骤基本分为：声明、打开、取值、关闭。

DECLARE test_cursor CURSOR FOR 结果集; //声明游标
OPEN test_cursor; //打开游标
CLOSE test_cursor; //关闭游标
DECLARE CONTINUE HANDLER FOR NOT FOUND //结果集查询不到数据自动跳出

游标总结：

1. 游标的声明的语法： declare 游标名称 cursor for 查询语句;

2. 打开光标的语法： open 游标名称;

3. 获取游标数据： fetch 游标名称 into 变量名 1 [,变量名 2 ....]

4. 关闭游标的语法： close 游标名称;

5. 游标的基本使用须知：对某个表按照循环的处理，判断循环结束 的条件是捕获 not found 的条件，当 fetch 光标找不到下一条记录的 时候，就会关闭光标 然后退出过程。

6. 可能有过 Pascal 编程经验的朋友们都会知道，声明的顺序也是很 重要的，在 SQL 中，我们使用 declare 定义的顺序是：变量、条件、 游标、应用程序 操作 查询出来的数据会放置于临时表中，然后再通过游标去读取数据。

delimiter $$
create procedure exchange(out count int )
begin
declare supply_id1 int default 0;
declare amount1 int default 0;
-- 游标标识
declare blag int default 1;
-- 游标
declare order_cursor cursor for select supply_id,amount from order_group;
-- not found 这个异常进行处理
declare continue handler for not found set blag = 0;
set count = 0;
-- 打开游标
open order_cursor;
-- 遍历
read_loop: LOOP
fetch order_cursor into supply_id1,amount1;
if blag = 0 then
leave read_loop;
end if;
if supply_id1 = 1 then
set count = count + amount1;
end if;
end loop read_loop;
end;
$$
delimiter ;
call exchange(@count);
select @count;

存储过程优点

1. 第一点优势就是执行速度快。因为我们的每个 SQL 语句都需要经 过编译，然后再运行，但是存储过程都是直接编译好了之后，直接 运行即可。

2. 第二点优势就是减少网络流量。我们传输一个存储过程比我们传 输大量的 SQL 语句的开销要小得多。

3. 第三点优势就是提高系统安全性。因为存储过程可以使用权限控 制，而且参数化的存储过程可以有效地防止 SQL 注入攻击。保证了 其安全性。

4. 第四点优势就是耦合性降低。当我们的表结构发生了调整或变动 之后，我们可以修改相应的存储过程，我们的应用程序在一定程度 上需要改动的地方就较 小了。

5. 第五点优势就是重用性强。因为写好一个存储过程之后，再次调用它只需要一个名称即可，也就是”一次编写，随处调用”,而且 使用存储过程也可以让 程序的模块化加强。

存储过程的缺点

1. 第一个缺点就是移植性差。因为存储过程是和数据库绑定的，如 果我们要更换数据库之类的操作，可能很多地方需要改动。

2. 第二个缺点就是修改不方便。因为对于存储过程而言，我们并不 能特别有效的调试，它的一些 bug 可能发现的更晚一些，增加了应 用的危险性。

3. 第三个缺点就是优势不明显和赘余功能。对于小型 web 应用来说， 如果我们使用语句缓存，发现编译 SQL 的开销并不大，但是使用存 储过程却需要检查 权限一类的开销，这些赘余功能也会在一定程度 上拖累性能。

小结

1. 存储过程和函数的优势是可以将数据的处理放在数据库服务器上 进行，避免将大量的结果集传输给客户端，减少了数据的传输，因 此也减少了宽带和服务 器的压力。

2. 但是在数据库服务器上进行大量的运算也会占用服务器的 CPU，造成数据库服务器的压力。

3. 一般来说是不建议在存储过程中进行大量的复杂的运算的，它们不是数据库服务器的强项，应该把这些操作让应用服务器去处 理。