【MySQL】1. 存储过程（附遍历查询结果示例）& 函数

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

存储过程可以有任意多个返回值，函数有且只有一个。

1. 运行速度：由于一次编译永久有效，不用像普通 SQL 那样每次执行都要变异，所以效率高一些。

2. 减少网络传输：存储过程直接就在数据库服务器上跑，所有的数据访问都在数据库服务器内部进行，不需要传输数据到其它服务器，所以会减少一定的网络传输。因为存储过程封装了一组 SQL，不用在数据库和其他服务器之间多次跳转。

3. 可维护性：存储过程有些时候比程序更容易维护，这是因为可以实时更新DB端的存储过程。 有些bug，直接改存储过程里的业务逻辑，就搞定了。

4. 增强安全性：提高代码安全，防止 SQL注入，因为已经是编译好的，所以没法改 SQL 结构。这一点sql语句也可以做到。

5. 可扩展性：应用程序和数据库操作分开，独立进行，而不是相互在一起。方便以后的扩展和DBA维护优化。

1. 不适合复杂逻辑的应用：SQL本身是一种结构化查询语言，但不是面向对象的的，本质上还是过程化的语言，面对复杂的业务逻辑，过程化的处理会很吃力。同时SQL擅长的是数据查询而非业务逻辑的处理，如果如果把业务逻辑全放在存储过程里面，违背了这一原则。

2. 如果需要对输入存储过程的参数进行更改，或者要更改由其返回的数据，则您仍需要更新程序集中的代码以添加参数、更新调用，等等，这时候估计会比较繁琐了。

3. 开发调试复杂，由于IDE的问题，存储过程的开发调试要比一般程序困难。

4. 没办法应用缓存。虽然有全局临时表之类的方法可以做缓存，但同样加重了数据库的负担。如果缓存并发严重，经常要加锁，那效率实在堪忧。

5. 不支持群集，数据库服务器无法水平扩展，或者数据库的切割（水平或垂直切割）。数据库切割之后，存储过程并不清楚数据存储在哪个数据库中。

阿里的开发手册也明确表示，禁止在开发中使用存储过程，因为难以调试和扩展，更难以移植。

但是自己私下增删一些数据，做一些简单的逻辑，用起来还是挺方便的。我就遇到这种情况了，老板让临时给所有项目插几十条数据，这个功能不是用户的需求，不用纳入开发中，所以直接在数组库加就好了，但是每条数据都要在几十个项目上加一遍。。。
于是就想到写一个存储过程解决。

DELIMITER $$
CREATE PROCEDURE  存储过程名称 (输入输出类型  参数名  参数类型(参数长度))
BEGIN
  -- 主体
END $$
DELIMITER ;
DELIMITER 用来定义结束符，MySQL 中默认 ";" 为结束符，但是存储过程中可能有多个语句，内部会用 ";" 分割，这样会导致存储过程还么运行完就结束了，可能会报错。所以，暂时将结束符定义成其它的符号，例如 "$$"，这个不是固定的，通常以连个符号作结束符 "//" "??" 等等都行。所以在存储过程结尾用 "$$" 表示结束。但要记得用完之后要改回默认值：DELIMITER ;。
DECLARE  变量名  变量类型(长度) [DEFAULT  默认值]
参数可有可无，其中，输入输出类型有三种：

IN, 标记为输入参数

OUT, 标记为输出的参数

INOUT, 输入输出参数
给变量赋值
set  变量名 = ...   可以是值，也可以是 SQL
select  字段1, 字段2  into 变量1, 变量2  from 表名 ...
SQL 中没有数组，可以用游标代替。游标有点像迭代器，允许依次取出查询结果中的记录。
-- 声明
DECLARE 游标名 CURSOR FOR   select语句;
-- 声明当游标遍历完后将标志变量置成某个值
DECLARE s int DEFAULT 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;
-- 启用
open 游标名
-- 取出一条数据并赋值给变量
fetch 游标名 into 变量名;
-- 关闭游标
close 游标名;
-- 创建存储过程，用于给用户添加“所有项目”的某个角色
DROP PROCEDURE IF EXISTS bind_role_user;
DELIMITER $$
CREATE PROCEDURE bind_role_user (
IN user_id BIGINT(20),
IN role_id BIGINT(20)
BEGIN
    DECLARE relation_id BIGINT(20);
    DECLARE s int DEFAULT 0;
    DECLARE count int DEFAULT 0;
    -- 定义游标，并将sql结果集赋值到游标中
    DECLARE report CURSOR FOR select id as report from project where delete_flag = 0 and process_state !=2;
    -- 声明当游标遍历完后将标志变量置成某个值
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;
    -- 打开游标
    open report;
    fetch report into relation_id;
    while s<>1 do
        set count = count + 1;
    -- 当s等于1时表明遍历以完成，退出循环
        insert into role_user(id, user_id, role_id, relation_id,) values(DEFAULT, user_id, role_id, relation_id);
        fetch report into relation_id;
    end while;
    -- 关闭游标
    close report;
END $$
DELIMITER ;
-- 执行
DELIMITER $$
CALL bind_role_user (1357252007676612609, 1367760183823962114);
DELIMITER ;
删除存储过程

drop procedure 存储过程名
查看存储过程

show create procedure 存储过程名
存储过程不能直接修改，可以删除重建

drop procedure if exists 存储过程名;

create procedure .....

call 存储过程名([变量])

什么是函数？
和存储过程类似，就是一组 sql 语句集。
create function 函数名(参数列表)  returns  返回类型
begin
return 值
参数列表和存储过程不同的是，只有参数名和参数类型
select 函数名(参数)
show create function 函数名
drop function 函数名
三、存储过程和函数对比