首页 > > 网络编程 > Mysql >

Mysql - 游标/动态sql/事务

2018-06-17 23:19:21来源：未知阅读 ()

游标这个在我目前的项目里面用的还不多, 但是其功能还是很强大的.

动态sql以前都没用过, 是跟着富士康(不是张全蛋的富土康哦)过来的同事学的. 还是挺好用的. 我的数据库方面, 跟他学了不少. 在此, 感谢他一下, 建华锅锅.

事务在前面的篇章中其实已经出现过了, 这个东西好像还是程序中用的比较多一点.

由于之前的工作中碰到过一个场景, 正好将游标,动态sql,事务都用上了, 那么我也弄一个例子好了, 想了一个别的场景, 与工作的那个场景不相干, 并没有泄露公司业务机密之类的啊.

先看例子吧, 然后在后面, 我补上语法.

一、例子

1. 建表

　　既然是讲例子, 当然不能忘记建表嘛, 从0开始.

create table Goods
(
    Id int not null PRIMARY key auto_increment,
    Code varchar(50) comment '编码',
    Name varchar(20) comment '名称',
    Count int comment '数量',
    Brand varchar(20) comment '品牌'
) default charset=utf8 comment '商品表';

create table GoodDetails
(
    Id int not null PRIMARY key auto_increment,
    GId int not null comment 'Goods表Id',
　　 Name varchar(20) comment '名称',
    Code varchar(50) comment '编码明细',
    Remark varchar(100) comment '备注'
) default charset=utf8 comment '商品明细';

2. 加入基础数据

3. 虚拟场景介绍

公司最近进了一批物品, 就是上面的Goods表了, 并且准备给每一个物品进行编码(编码规则就是用Goods表的Code加上流水号, 去生成), 并打上条形码.

这里的功能就是生成商品明细和流水号的问题了, 一键生成. 这里通常的实现方式有两种:

方式一 : 程序生成

　　在程序中, 读取需要生成的数据, 比如上面这四条, 然后循环每一条, 给数据加上编码, 总共生成出12条数据, 在吧这12条数据, 存入明细表中. 在数据量少的时候还好, 完全可以接受, 但是如果数据量多了, 那速度, 慢的让人有砸电脑的冲动. Goods表的几条数据, 到GoodDetails表中, 会变成数百, 甚至上千, 上万.

方式二 : 数据库生成

　　如果不想读取出来再插入, 并且逻辑处理并不多,不复杂的情况下, 可以使用数据库去生成. 还是很方便的, 速度也提升非常多.

那这里, 我只介绍方式二了, 方式一, 只是处理麻烦一点.

4. 脚本:

delimiter $
drop PROCEDURE if EXISTS p_autocreate;
CREATE  PROCEDURE `p_autocreate`(IN g_ids VARCHAR(1000), IN nolength INT)
BEGIN
 DECLARE res_code INT;

 DECLARE res_msg VARCHAR (50);

 /*临时表的条数*/
 DECLARE t_count INT;

 /**游标内使用变量**begin**/
 DECLARE g_id INT;

 DECLARE g_code VARCHAR (50);

 DECLARE d_code VARCHAR (50);

 DECLARE g_count INT (11);

 DECLARE g_name VARCHAR (20);
 /**游标内使用变量**end**/

 /**游标的位置**/
 DECLARE v_index INT DEFAULT 1;

 DECLARE done BIT DEFAULT 0;

 /*声明游标*/
 DECLARE g_cursor CURSOR FOR SELECT id, CODE, NAME, COUNT FROM temp_goods;

 /*游标查询时, 如果找不到下一个了, 会将done置为1*/
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

 /**创建临时表**begin**/
 DROP TABLE IF EXISTS temp_goods;
 CREATE TEMPORARY TABLE temp_goods (
  Id INT NOT NULL, --  PRIMARY key auto_increment,
  CODE VARCHAR (50),
  NAME VARCHAR (20),
  COUNT INT
 ) DEFAULT CHARSET = utf8;
 /**创建临时表**end**/

 /**初始化返回值**begin**/
 SET res_code := "-99";

 SET res_msg := "OK";
 /**初始化返回值**end**/

 IF (g_ids IS NOT NULL OR LENGTH(g_ids) > 0) THEN

      SET @v_sql := CONCAT("INSERT INTO  temp_goods(Id,Code,Name,Count) ",
                    " select Id,Code,Name,Count from Goods ",
                    " where ",
                    " find_in_set(id, ",
                    CHAR(34),
                    g_ids,
                    CHAR(34),
                    ")>0 ;");

 /*预编译此动态sql, 并存入stmt中*/
 PREPARE stmt FROM @v_sql;

 /*执行此动态sql, 此动态sql的作用, 是从Goods中提取有效数据*/
 EXECUTE stmt;

 /*释放此资源*/
 DEALLOCATE PREPARE stmt;

 SELECT COUNT(1) INTO t_count FROM temp_goods;

 START TRANSACTION; -- 开始事务

 IF (t_count > 0) THEN
      /*打开游标*/
      OPEN g_cursor;

      REPEAT 
                        /*这里的顺序要与之前的顺序保持一致*/
            FETCH g_cursor INTO g_id, g_code, g_name, g_count;

            IF NOT done THEN

               SET v_index := 1;

               IF (IFNULL(g_count, 0) > 0) THEN

                  WHILE (v_index <= g_count)
                  DO
                     SET d_code := CONCAT(g_code, LPAD(v_index, nolength, "0"));

                     INSERT INTO GoodDetails(GId, NAME, CODE) VALUES (g_id, g_name, d_code);

                     SET v_index := v_index + 1;
                  END WHILE;
               END IF;
            END IF;
         UNTIL done END REPEAT; -- 结束repeat循环
         CLOSE g_cursor; /*关闭游标*/

         COMMIT; -- 提交事务

      ELSE

         ROLLBACK; -- 回滚事务

         SET res_code := "10";

         SET res_msg := "系统中不存在相关记录.";
      END IF;
   ELSE
      SET res_code := "5";

      SET res_msg := "请选择要生成的记录";
   END IF;

   DROP TABLE IF EXISTS temp_goods;

   SELECT res_msg;
END $
delimiter ;