記一次MySQL存儲過程和游標的使用

需求：

    有三張表：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;
    ……