Mysql - 游標/動態sql/事務_ZenDei技術網路在線

游標這個在我目前的項目裡面用的還不多, 但是其功能還是很強大的. 動態sql以前都沒用過, 是跟著富士康(不是張全蛋的富土康哦)過來的同事學的. 還是挺好用的. 我的資料庫方面, 跟他學了不少. 在此, 感謝他一下, 建華鍋鍋. 事務在前面的篇章中其實已經出現過了, 這個東西好像還是程式中用的比較多 ...

游標這個在我目前的項目裡面用的還不多, 但是其功能還是很強大的.

動態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 ;

5. 結果:

執行這個存儲過程

call p_autocreate('1,2,3,4', 3);

ok, 執行成功, 接下來, 來看一下GoodDetails表的數據:

我這裡的例子, 已經是最簡單的一個例子了, 在實際使用過程中, 可能比這個還要複雜一些, 數據更多一些.

不過說到這個數據量, 我倒不介意, 多做一個實驗.

6. 實驗

　　我將各自的數據量, 都修改為 10000, 如下圖, 這個時候, 要生成 40000 條數據, 並且插入到表中去. 如果使用程式處理插入資料庫的方式, 確實會慢一些.

資料庫的方式, 確實會快很多. 如下圖, 生成4w條數據, 然後插入GoodDetails表中, 花了不到4s的時間. 算是一個比較快的時間了.

OK, 接下來, 就來介紹一下他們的語法.

二、游標

1. 語法

1.1 聲明游標

　　declare 游標名 cursor for select 列名 from 表

1.2 打開游標

　　open 游標名

1.3 游標前進

　　fetch 游標名 into 變數a, 變數b ...

1.4 關閉游標

　　close 游標名

2. 註

　　既然游標執行的方式, 像是一個迴圈, 那麼什麼時候才知道這個迴圈要結束呢.

　　例子裡面, 有一句話, DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; 這句話的意思, 其實就是當游標找不到下一條數據的時候, 就回將變數 done 修改為1.

三、動態sql

1. 語法

1.1 準備sql變數

　　此sql變數必須是字元串格式的哦. 這樣可以動態生成需要執行的sql.

1.2 預編譯

　　PREPARE stmt FROM @v_sql;

　　這裡的stmt是一個變數, 名稱自己取

1.3 執行

　　EXECUTE stmt;

1.4 釋放資源

　　DEALLOCATE PREPARE stmt;

四、事務

1. 語法

到這裡, 我突然不知道說些什麼了. 那就簡單介紹下吧

1.1 開始事務

　　start transation;

1.2 提交事務

　　commit;

1.3 回滾事務

　　rollback;