记一次MySQL存储过程和游标的使用-创新互联

需求:

创新互联主要从事成都网站建设、做网站、网页设计、企业做网站、公司建网站等业务。立足成都服务锦江,十多年网站建设经验,价格优惠、服务专业,欢迎来电咨询建站服务:028-86922220

  有三张表: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;     ……

另外有需要云服务器可以了解下创新互联cdcxhl.cn,海内外云服务器15元起步,三天无理由+7*72小时售后在线,公司持有idc许可证,提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案,具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势,专为企业上云打造定制,能够满足用户丰富、多元化的应用场景需求。


分享标题:记一次MySQL存储过程和游标的使用-创新互联
地址分享:http://scyanting.com/article/dhhhoh.html