MySQL - 存储过程与函数

一、为什么使用存储过程？

好处：简单、安全、高性能

1. 简化复杂的操作
2. 防止错误保证数据的唯一性
3. 简化对变动的管理，提高安全性
4. 提高性能

缺陷：权限问题，许多数据库管理员限制存储过程的创建权限，只允许用户使用存储过程，不允许他们创建存储过程。

二、存储过程

1）创建存储过程：CREATE PROCEDURE

2）语法形式： CREATE PROCEDURE sp_name ([ IN | OUT | INOUT ][proc_parameter[,...]]) ……

　参数形式：[ IN | OUT | INOUT ] param_name type

　① IN：表示输入参数；OUT：表示输出参数；③ INOUT：表示既可以输入也可以输出；

3）客户端创建：数据库下的stored Procedure表

4）命令行实例：

● 创建：

DELIMITER $$  -- 更改分隔符
CREATE PROCEDURE 存储过程名称([参数1 类型，参数2 类型]) -- 可以不传入参数()
COMMENT'查询所有雇员的工资' -- 注释
BEGIN -- 语句开始
SELECT sal FROM t_employee; -- 语法体
END$$ -- 语句结束
DELIMITER；-- 恢复分隔符

● 删除：

DROP PROCEDURE 函数名；-- 该删除的存储过程不存在时会产生一个错误。
DROP PROCEDURE IF EXISTS 函数名；-- 仅当存在时删除

● 修改：先删除存储过程，然后再创建新的存储过程。

调用存储过程：CALL 存储过程名称[(传参)]；

查看定义信息：SHOW CREATE PROCEDURE 存储过程名称；

查看状态信息：SHOW PROCEDURE 存储过程名称；

三、函数

1）创建函数：CREATE FUNCTOIN

2）语法形式： CREATE FUNCTION func_name ( [ [ IN | OUT | INOUT ] func_parameter [,...] ] ) ……

　参数形式：[ IN | OUT | INOUT ] param_name type

　① INT：输入参数；② OUT：输出参数；③ INOUT：输入兼输出参数；

　④ param_name：表示参数的名称；⑤ type：表示参数的类型，该类型可以是MySQL数据库中的任意类型；

3）客户端创建：数据库下的function表

4）命令行实例：

创建：

DELIMITER $$ -- 为了避免冲突定义结束符；
DROP FUNCTION EXISTS 函数名称$$ -- 如果函数已经存在，就删掉
CREATE FUNCTION 函数名称(参数1 类型，参数2 类型) -- 可以不传入参数 （）
RETURNS 类型 -- 返回值类型
COMMENT'查询所有雇员的工资' -- 注释
BEGIN
　　SELECT sal FROM t_employee; -- 语法体
RETURN 返回值 -- 返回值
END$$
DELIMITER；

删除：

DROP FUNCTION 函数名;

语法总结：

创建函数：CREATE FUNCTION 函数名称 (输入参数变量，类型) RETURNS 类型 BEGIN ... RETURN 返回值 END (中间是具体实现)变量定义关键字：DECLARE 变量名 类型。
流程控制: 
　IF(条件表达式)
　　　THEN ... 
　ELSEIF(条件表达式) 
　　　THEN ... 
　ELSE 
　　　... 
　END IF;
变量赋值: SELECT..into 变量名 或者是 set 变量名=value

调用函数：SELECT 函数名称()；

查看定义信息：SHWO CREATE FUNCTION 函数名；

查看状态信息：SHWO FUNCTION 函数名；

四、游标

就是一个可读的标识，用来标识数据取到什么地方了。

游标提供了一种对从表中检索出的数据进行操作的灵活手段。

通过使用游标，使SQL这种面向集合的语言有了面向过程开发的能力。

1）游标具有三个属性

① Asensitive：数据库也可以选择不复制结果集；

② Read only：不可更新；

③ Nonscrollable：游标只能向一个方向前进，并且不可以跳过任何一行数据。

2）游标的使用

在MySQL数据库中，可以在存储过程、函数、触发器、或者事件中使用游标。

使用时需要与handler一起，并且游标要在handler之前定义。

一般使用循环遍历取出游标的每一行数据：循环使用https://www.jianshu.com/p/cb0152efac32

存储过程中变量的定义不要和表中的字段名相同（不区分大小写），否则就无法正常取值。

① 声明游标：DECLARE 游标名 CURSOR FOR select语句；

② 打开游标：OPEN 游标名；

③ 使用游标：FETCH 游标名 INTO 变量名[,变量名]；

④ 关闭游标：CLOSE 游标名；隐关闭，如果不明确关闭游标，mysql将在达到END语句时自动关闭它