Mysql 游標(biāo)使用動態(tài)變量

instl 2015-11-21

展開全文

從語法上來講DECLARE cur CURSOR for語句必須寫在SET @sqlstr前面，這就意味著不能通過先執(zhí)行一條動態(tài)語句根據(jù)結(jié)果拼湊游標(biāo)的內(nèi)容，而且游標(biāo)在定義的時候也沒有參數(shù)的概念，而是將定義的結(jié)構(gòu)完全當(dāng)作一個字符串直接處理，不會做任何的處理，也就是靜態(tài)游標(biāo)吧。關(guān)于dynamic cursor的內(nèi)容官方有解釋http://dev./doc/refman/5.6/en/connector-odbc-usagenotes-functionality.html#connector-odbc-usagenotes-functionality-dynamic-cursor我從5.1找到5.6只有這一個地方講dynamic cursor而且內(nèi)容一模一樣。

“Support for the dynamic cursor is provided in Connector/ODBC 3.51, but dynamic cursors are not enabled by default. You can enable this function within Windows by selecting the Enable Dynamic Cursor check box within the ODBC Data Source Administrator.On other platforms, you can enable the dynamic cursor by adding 32 to the OPTION value when creating the DSN. ”話說這個意思是使用ODBC的話就可以經(jīng)過設(shè)置之后使用dynamic cursor，關(guān)鍵是我有JDBC，安裝程序的時候再自帶一個ODBC驅(qū)動的話，貌似不太現(xiàn)實，于是考慮其它方式。我總結(jié)一下解決這個問題有三種途徑。

一、避免使用動態(tài)游標(biāo)的可能性。首先使用所謂動態(tài)就是一段代碼可復(fù)用與多種情況，于是每種情況都寫一種代碼就可避免，但是作為開發(fā)人員，基本沒人會這樣做。

二、拆分存儲過程。假設(shè)存在表結(jié)構(gòu)如下：

Sql代碼

mysql> desc tree_test;
+———–+————-+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+———–+————-+——+—–+———+—————-+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(11) | NO | | | |
| parent_id | int(11) | NO | | NULL | |
+———–+————-+——+—–+———+—————-+
3 rows in set (0.00 sec)

現(xiàn)在的問題就是將不確定值傳給游標(biāo)，簡單的游標(biāo)定義如下DECLARE cur CURSOR for SELECT * FROM TABLE。這里看到好多人說法是如果在for語句后面使用內(nèi)置方法方法的話，方法參數(shù)可以是你傳入的值，也就是說后面的SQL語句是支持方法調(diào)用的。沒測試，那天測試了再補(bǔ)充吧。就拿后面的 select 語句來說這個語句里面都是確定的值，但是有一個我們可以控制的參數(shù)就是表名。表這里可以看作是一個臨時的數(shù)據(jù)集合，如果我們可以控制里面的值，在游標(biāo)OPEN之后讀這個“動態(tài)的集合”，于是實現(xiàn)這個有臨時表和視圖兩種途徑。在這里我選擇視圖?，F(xiàn)在創(chuàng)建三個PROCEDURE：

Sql代碼

CREATE DEFINER=`root`@`localhost` PROCEDURE `proc1`(IN top int)
BEGIN
DROP VIEW IF EXISTS tree_test_view;
SET @sqlstr = "CREATE VIEW tree_test_view as ";
SET @sqlstr = CONCAT(@sqlstr , "SELECT id FROM tree_test WHERE parent_id = 0 LIMIT ", top);
PREPARE stmt FROM @sqlstr;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END

第一個功能很簡單，根據(jù)條件獲得一個ID集合，并把集合存在tree_test_view這個視圖中，這里直接在SQL編輯器寫的代碼，直接運(yùn)行需要添加存儲過程存在判斷和DELIMITER轉(zhuǎn)義換行符。現(xiàn)在我們已經(jīng)獲得需要要刪除的樹的ID集合，因為已只有兩級的數(shù)，所有只需要知道根的id就可以直接刪除整棵樹，如果是多級的話就需要進(jìn)行遞歸刪除，當(dāng)然前提是需要知道根ID，并且只知道根ID就足夠了。

Sql代碼

CREATE DEFINER=`root`@`localhost` PROCEDURE `proc2`()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE temp_id INT;
DECLARE cur CURSOR for( SELECT id from tree_test_view);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur;
FETCH cur INTO temp_id;
WHILE done <> 1 DO
delete tree_test WHERE id = temp_id or parent_id = temp_id;
FETCH cur INTO temp_id;
END WHILE;
CLOSE cur;
END

這個就是執(zhí)行的刪除操作，基本的游標(biāo)循環(huán)操作，話說這寫法好像很有shell和python的韻味。proc1和proc2兩個分別執(zhí)行就可已，當(dāng)然為了對外的友好，可以再寫一個進(jìn)行統(tǒng)一調(diào)用：

Sql代碼

REATE DEFINER=`root`@`localhost` PROCEDURE `tree_test_query`(IN top int)
BEGIN
call proc1(top);
call proc2();
END

三、第三種是我首先想到的，后來發(fā)現(xiàn)原來一般情況下會首先想到第二種，于是我覺得我確實懶的不行了。第三種方法實際就是第二種方法的偷懶方式，把所有的都放在一個存儲過程中實現(xiàn)，實際SQL如下：

Sql代碼

CREATE DEFINER=`root`@`localhost` PROCEDURE `tree_test_query`(IN top int)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE temp_id INT;
DECLARE cur CURSOR for( SELECT id from tree_test_view);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
DROP VIEW IF EXISTS tree_test_view;
SET @sqlstr = "CREATE VIEW tree_test_view as ";
SET @sqlstr = CONCAT(@sqlstr , "SELECT id FROM tree_test WHERE parent_id = 0 LIMIT ", top);
PREPARE stmt FROM @sqlstr;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
OPEN cur;
FETCH cur INTO temp_id;
WHILE done <> 1 DO
DELETE FROM tree_test WHERE id = temp_id or parent_id = temp_id;
FETCH cur INTO temp_id;
END WHILE;
CLOSE cur;
END