记一次MySQL存储过程中和游标的采取

发布时间：2023-08-26 15:04:49 所属栏目：MySql教程来源：

导读：需求：

有三张表：Player、Consumption、Consumption_other。Player表中记录用户信息（playerid、origin等字段），Consumption和Consumption_other记录用户的消费信息。现需要根据Player表中的origin字段，分

需求：

    有三张表：Player、Consumption、Consumption_other。Player表中记录用户信息（playerid、origin等字段），Consumption和Consumption_other记录用户的消费信息。现需要根据Player表中的origin字段，分别向Consumption和Consumption_other表中插入一条消费记录。规定：Player表中origin=0的，将信息插入到Consumption表中；Player表中origin不为0的，将信息插入到Consumption_other表中。

方法：

    使用MySQL的存储过程和游标实现：

mysql> DELIMITER //

mysql> CREATE PROCEDURE `add_consumption`()

    -> BEGIN

    ->   -- 定义需要接收游标数据的变量

    ->   DECLARE id int(11);

    ->   DECLARE origin int(11);

    ->   -- 定义遍历数据结束标志

    ->   DECLARE done BOOLEAN DEFAULT 0;

    ->   -- 定义游标

    ->   DECLARE cur CURSOR FOR SELECT

    ->     player.playerid as id,

    ->     player.origin as origin

    ->   FROM player;

    ->   -- 将结束标志绑定到游标

    ->   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

    ->   -- 打开游标

    ->   OPEN cur;

    ->     -- 关闭事务自动提交

    ->     SET autocommit=0;

    ->     -- 开始循环

    ->     read_loop:LOOP

    ->       -- 提取游标中的数据

    ->       FETCH cur INTO id,origin;

    ->       -- 声明何时结束循环

    ->       IF done THEN

    ->         LEAVE read_loop;

    ->       END IF;

    ->       -- 循环时的事件

    ->       IF origin=0

    ->       THEN

    ->         INSERT INTO consumption VALUES (0,1525467600);

    ->       ELSE

    ->         INSERT INTO consumption_other VALUES(0,1525467600);

    ->       END IF;

    ->     END LOOP;

    ->     commit;

    ->     -- 关闭游标

    ->   CLOSE cur;

    -> END

    -> //

mysql> DELIMITER ;

mysql> call add_consumption();

存储过程相关：

1、创建存储过程：

    格式：

CREATE PROCEDURE 过程名([参数])

  过程体

    例子：

mysql> DELIMITER //

mysql> CREATE PROCEDURE `originplayer`(

    ->     IN ori int(11),

    ->     OUT total int(11)

    -> )

    -> BEGIN

    ->   select count(*) from player where origin=ori into total;

    -> END//

mysql> DELIMITER ;

mysql> call originplayer(0, @total);

mysql> select @total;

+--------+

| @total |

+--------+

|    172 |

+--------+

    解析：

delimiter是分割符的意思。因为MySQL默认以“;”为分割符，如果没有声明分割符，那么编译器会把存储过程当作SQL语句进行处理，则存储过程的编译过程会报错。“delimiter //”声明分割符是“//”。存储过程中的代码结束之后，再次声明“delimiter ;”，将“;”作为分割符。

创建的存储过程可能会有输入、输出、输入输出参数。本例有一个输入参数“ori”，类型是int，一个输出参数“total”，类型是int。如果有多个参数，用“,”分割开。

过程体的开始、结束使用BEGIN和END进行标识。

MySQL称存储过程的执行为调用，因此执行存储过程的语句是CALL。CALL接收存储过程的名字以及需要传递给它的任何参数。

2、参数：

    存储过程共有三种参数类型，INT、OUT、INOUT。形式如：CREATE PROCEDURE([[IN |OUT |INOUT ] 参数名数据类形...])

IN输入参数：该参数的值必须在调用存储过程时指定。如果在存储过程中修改了该参数的值，该参数的值仍然是修改之前的值。

OUT输出参数：指定MySQL变量，接收调用存储过程后返回的值。

INOUT输入输出参数：调用时指定，并且可被改变和返回。

3、变量：

定义存储过程局部变量：

DECLARE variable_name datatype [default value];

    datatype与MySQL的数据类型一样，如：int、float、date、varchar(length)；

MySQL变量：MySQL变量一般以@开头；

变量赋值：

SET variable_name = value

4、查询存储过程：

# 列出所有的存储过程：

mysql> show procedure status\G

# 列出某个库拥有的存储过程：

mysql> select name from mysql.proc where db='project';

# 查询存储过程的详细信息：

mysql> show create procedure project.originplayer;

5、删除存储过程：

mysql> drop procedure project.originplayer;

游标相关：

1、创建游标：

mysql> DELIMITER //

mysql> CREATE PROCEDURE `getplayerid`()

    -> BEGIN

    ->   DECLARE id int(11);

    ->   DECLARE done BOOLEAN DEFAULT 0;

    ->   DECLARE cur CURSOR FOR SELECT

    ->     playerid

    ->   FROM player;

    ->   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

    ->   OPEN cur;

    ->     REPEAT

    ->       FETCH cur into id;

    ->     UTIL done END REPEAT;

    ->   CLOSE cur;

    -> END//

mysql> DELIMITER ;

    解析：

MySQL游标仅用于存储过程中；

DECLARE语句用来定义和命名游标，这里的游标为“cur”；

OPEN和CLOSE用来打开和关闭游标。在处理OPEN语句时执行查询，存储检索出的数据以供浏览。CLOSE游标将释放游标占用的所有内存和内部资源。如果没有明确关闭游标，MySQL会在到达END语句时自动关闭游标；

在一个游标被打开后，使用FETCH语句可以访问游标的每一行，并可以指定将数据存储在什么地方。

上面例子中，FETCH语句在REPEAT内，因此它反复执行，直到done为真（由UTIL done END REPEAT;指定）；

CONTINUE HANDLER，当REPEAT由于没有更多的行供循环而不能继续时出现这个条件，将done设置为1，此时REPEAT终止。

2、DECLARE语句的次序：

    DECLARE语句的发布存在特定的次序。用DECLARE语句定义的局部变量必须在定义任意游标或句柄之前；句柄的定义必须在游标之后。

3、重复或循环：

    除了在1、创建游标中使用的REPEAT外，MySQL还支持循环语句，用来重复执行代码，直到使用LEAVE语句手动退出为止。如下：

    ……

    ->     read_loop:LOOP

    ->       -- 提取游标中的数据

    ->       FETCH cur INTO id,origin;

    ->       -- 声明何时结束循环

    ->       IF done THEN

    ->         LEAVE read_loop;

    ->       END IF;

    ->       -- 循环时的事件

    ->       IF origin=0

    ->       THEN

    ->         INSERT INTO consumption VALUES (0,1525467600);

    ->       ELSE

    ->         INSERT INTO consumption_other VALUES(0,1525467600);

    ->       END IF;

    ->     END LOOP;

    ……

（编辑：聊城站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!