mysql创建存储过程及遍历查询结果,存储过程创建及使用 & 遍历查询结果

什么是存储过程？

存储过程(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 ;