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

instl 2015-11-21

展開全文

從語法上來講DECLARE cur CURSOR for語句必須寫在SET @sqlstr前面，這就意味著不能通過先執(zhí)行一條動(dòng)態(tài)語句根據(jù)結(jié)果拼湊游標(biāo)的內(nèi)容，而且游標(biāo)在定義的時(shí)候也沒有參數(shù)的概念，而是將定義的結(jié)構(gòu)完全當(dāng)作一個(gè)字符串直接處理，不會做任何的處理，也就是靜態(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只有這一個(gè)地方講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. ”話說這個(gè)意思是使用ODBC的話就可以經(jīng)過設(shè)置之后使用dynamic cursor，關(guān)鍵是我有JDBC，安裝程序的時(shí)候再自帶一個(gè)ODBC驅(qū)動(dòng)的話，貌似不太現(xiàn)實(shí)，于是考慮其它方式。我總結(jié)一下解決這個(gè)問題有三種途徑。

一、避免使用動(dòng)態(tài)游標(biāo)的可能性。首先使用所謂動(dòng)態(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 語句來說這個(gè)語句里面都是確定的值，但是有一個(gè)我們可以控制的參數(shù)就是表名。表這里可以看作是一個(gè)臨時(shí)的數(shù)據(jù)集合，如果我們可以控制里面的值，在游標(biāo)OPEN之后讀這個(gè)“動(dòng)態(tài)的集合”，于是實(shí)現(xiàn)這個(gè)有臨時(shí)表和視圖兩種途徑。在這里我選擇視圖。現(xiàn)在創(chuàng)建三個(gè)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

第一個(gè)功能很簡單，根據(jù)條件獲得一個(gè)ID集合，并把集合存在tree_test_view這個(gè)視圖中，這里直接在SQL編輯器寫的代碼，直接運(yùn)行需要添加存儲過程存在判斷和DELIMITER轉(zhuǎn)義換行符?，F(xiàn)在我們已經(jīng)獲得需要要?jiǎng)h除的樹的ID集合，因?yàn)橐阎挥袃杉壍臄?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

這個(gè)就是執(zhí)行的刪除操作，基本的游標(biāo)循環(huán)操作，話說這寫法好像很有shell和python的韻味。proc1和proc2兩個(gè)分別執(zhí)行就可已，當(dāng)然為了對外的友好，可以再寫一個(gè)進(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)原來一般情況下會首先想到第二種，于是我覺得我確實(shí)懶的不行了。第三種方法實(shí)際就是第二種方法的偷懶方式，把所有的都放在一個(gè)存儲過程中實(shí)現(xiàn)，實(shí)際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

本站是提供個(gè)人知識管理的網(wǎng)絡(luò)存儲空間，所有內(nèi)容均由用戶發(fā)布，不代表本站觀點(diǎn)。請注意甄別內(nèi)容中的聯(lián)系方式、誘導(dǎo)購買等信息，謹(jǐn)防詐騙。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請點(diǎn)擊一鍵舉報(bào)。

轉(zhuǎn)藏 分享

QQ空間 QQ好友新浪微博微信

獻(xiàn)花（0） +1

來自： instl > 《mysql》

舉報(bào)/認(rèn)領(lǐng)