函式中 SELECT 查詢中的 SQL 變數

MariaDB 10.3.14

我有一個在層次結構中生成根 id 的函式：

功能是這樣的：

CREATE FUNCTION `getRootId`(GivenID INT) RETURNS int(11)
DETERMINISTIC
BEGIN
DECLARE ch INT;
DECLARE root INT;
SET ch = GivenID;

WHILE ch >= 0 DO
   SELECT id, parent_id INTO root, ch  FROM
   (SELECT id, parent_id FROM pctable WHERE id = ch) A;
END WHILE;
RETURN root;
END

我希望能夠將 id、parent_id 和表名作為變數提供給這個函式，這樣我就可以很容易地在不同的層次結構表中使用它。

像這樣的東西：

CREATE FUNCTION `getRootId`(GivenID INT,id_column_name varchar(45),parent_id_column_name varchar(45),target_table_name varchar(45))

一個功能可用於主題、指標….

我是高級 SQL 編碼的新手，如果這是一個新手問題，我深表歉意。

非常感謝。

更新：

我切換到使用準備好的語句的過程：

CREATE DEFINER=`root`@`localhost` PROCEDURE `getRootIdFlex`(IN GivenID INT,IN id_name varchar(45),IN parent_name varchar(45),IN tbl_name varchar(45) , OUT root varchar(1000))
DETERMINISTIC
BEGIN
DECLARE id_or_parent INT;

SET @tbl_name = tbl_name;
SET @id_name = id_name;
SET @parent_name = parent_name;

SET id_or_parent = GivenID;

SET @s := CONCAT('SELECT ?, ? INTO root, id_or_parent FROM (SELECT ?, ? FROM ? WHERE ? = id_or_parent) A');
PREPARE stmt FROM @s;
WHILE id_or_parent >= 0 DO
   EXECUTE stmt USING @id_name, @parent_name, @id_name, @parent_name, @tbl_name, @id_name;
END WHILE;
END

但是當我以這種方式執行程序時：

SET @root := NULL;
CALL getRootIdFlex(11, 'Topic_id', 'Topic_Topic_id','Topic', @root);
SELECT @root;

我得到：

Error Code: 1327. Undeclared variable: root

根被聲明為 OUT 變數。為什麼會出現這個錯誤？

我修復了它，這是原始功能，經過一些編輯使其可讀。我將 while 條件更改為在 NULL 處停止，因為那是我的層次結構中的端點。

CREATE FUNCTION `getRootId`(GivenID INT) RETURNS int(11)
DETERMINISTIC
BEGIN
SET @root = NULL;
SET @tmp_parent = GivenID;
WHILE @tmp_parent IS NOT NULL DO
   SELECT id, parent_id INTO @root, @tmp_parent  FROM
   (SELECT id, parent_id FROM pctable WHERE id = @tmp_parent) A;
END WHILE;
RETURN @root;
END

為了在準備好的語句中包含變數，您需要使用 PROCEDURES 所以函式不會這樣做：

CREATE PROCEDURE `getRootIdFlex`(IN GivenID INT,IN id_name varchar(45),IN parent_name varchar(45),IN tbl_name varchar(45) , OUT root varchar(1000))
DETERMINISTIC
BEGIN
SET @tmp_parent = GivenID;
SET @tmp_root := 111;

SET @s := CONCAT('SELECT ',id_name,',',parent_name, ' INTO @tmp_root, @tmp_parent FROM (SELECT ',id_name,',',parent_name, ' FROM ',tbl_name,' WHERE ',id_name,' = @tmp_parent) A');

PREPARE stmt FROM @s;
WHILE @tmp_parent IS NOT NULL DO
   EXECUTE stmt;
END WHILE;
SET root := @tmp_root;
END

這裡的關鍵點是：確保在變數中到處添加“@”符號，以避免與 SQL 查詢中的表列混淆。

謝謝。

引用自：https://dba.stackexchange.com/questions/255427