Mysql實現Oracle中的Start with...Connect by方式

更新時間：2024年12月23日 08:59:01 作者：HNT_Wings

文章總結：作者在遷移數據庫時,使用了Oracle的startwith進行樹的遞歸查詢,但遇到了一些問題,通過搜索和修改,作者成功地使用存儲過程和預處理語句來實現動態(tài)查詢,并解決了變量聲明和預處理語句的問題

Mysql Oracle中的Start with...Connect by

工作需要，遷移數據庫時發(fā)現使用了Oracle中的start with來進行樹的遞歸查詢，所以自己動手豐衣足食。

通過一番搜索后發(fā)現

大家的實現基本都是這樣的：

CREATE FUNCTION queryChildrenAreaInfo(areaId INT)
RETURNS VARCHAR(4000)
BEGIN
DECLARE sTemp VARCHAR(4000);
DECLARE sTempChd VARCHAR(4000);

SET sTemp='$';
SET sTempChd = CAST(areaId AS CHAR);

WHILE sTempChd IS NOT NULL DO
SET sTemp= CONCAT(sTemp,',',sTempChd);
SELECT GROUP_CONCAT(id) INTO sTempChd FROM t_areainfo WHERE FIND_IN_SET(parentId,sTempChd)>0;
END WHILE;
RETURN sTemp;

但是這樣的代碼沒法復用（而且我的navicat居然建立函數失敗，或者各種錯誤，所以我使用了存儲過程，效果一樣），所以我們使用set和execute來進行語句的拼接和執(zhí)行，

修改后

如下：

CREATE PROCEDURE getChildList
IN rootId DECIMAL(65),
IN tablesname VARCHAR(6000),
OUT sTemp VARCHAR(6000)
BEGIN
DECLARE sTempChd VARCHAR(4000);

SET sTemp='$';
SET sTempChd = CAST(rootId AS CHAR);

WHILE sTempChd IS NOT NULL DO
SET sTemp= CONCAT(sTemp,',',sTempChd);
set @sqlexe = concat("SELECT GROUP_CONCAT(id) INTO sTempChd FROM " , tablesname , " WHERE FIND_IN_SET(parentId,sTempChd)>0;")
prepare sqlexe from @sqlexe;
execute sqlexe;
END WHILE;
END;

這樣一來我們就可以將表名作為參數傳入，但是一番執(zhí)行后，你會發(fā)現，哦豁，它居然報了這樣一個錯：

1327 - Undeclared variable: sTempChd；

這個低級錯誤困擾了我半天，我不是聲明了sTempChd為declare嗎？

答案很簡單

預處理語句（也就是我們的prepare）中，只接受@聲明的參數。因為在存儲過程中，使用動態(tài)語句，預處理時，動態(tài)內容必須賦給一個會話變量，也就是@形式聲明的變量，而declare聲明的是存儲過程變量，具體的內容涉及到更深的知識，我暫時無法找到原因。

前文是自上而下的查詢，自下而上的查詢其實很簡單，只要替換一下參數和語句內容就可以了，

具體如下：

CREATE PROCEDURE `getParentList`( 
	IN rootId DECIMAL(65,0), 
	IN tablesname VARCHAR ( 500 ),
	OUT sTemp VARCHAR ( 6000 ) )
BEGIN
	DECLARE
		PARENTID DECIMAL(65);
	SET sTemp = '$';
	SET @sTempChd = cast(rootId as char);
	WHILE @sTempChd <> 0 DO
		SET sTemp = concat( sTemp, ',', @sTempChd );
		SET @sqlcmd = CONCAT("SELECT PARENTID into @sTempChd FROM " , tablesname , " WHERE CATEID = " , @sTempChd , ";");
		PREPARE stmt FROM @sqlcmd;
		EXECUTE stmt;
	END WHILE;
	DEALLOCATE PREPARE stmt;
END

別忘了，最后要執(zhí)行一下deallocate語句，釋放預處理sql，免得session的預處理語句過多，達到max_prepared_stmt_count的上限值。