記一次MySQL預存程序和遊標的使用

標籤：MySQL預存程序   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;    ……

記一次MySQL預存程序和遊標的使用