在mysql中怎么声明变量_在mysql中声明变量

这是因为所有DECLARE语句都必须位于存储过程的顶部。

您需要在所有DECLARE语句之后移动此SET @t_name=table_name;语句。

请参阅Manual。

编辑：您无法访问游标中的变量表名称。或者，您可以使用动态sql并将结果存储在临时表中，并在游标选择中使用该表。

CREATE PROCEDURE logger(IN errorz VARCHAR(50),IN table_name VARCHAR(50))

BEGIN

DECLARE t_name VARCHAR(50);

DECLARE link_id_var INT;

DECLARE error_type_var VARCHAR(30);

DECLARE source_url_var VARCHAR(500);

DECLARE done INT DEFAULT 0;

DECLARE curl CURSOR FOR SELECT link_id, anchor_match, anchor_match FROM cursor_temp;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

DROP TEMPORARY TABLE IF EXISTS cursor_temp;

SET @query1 = CONCAT('

CREATE TEMPORARY TABLE cursor_temp AS

SELECT p.link_id, anchor_match, anchor_match

FROM ',table_name,' AS p

INNER JOIN link_results AS l

ON p.page_r_id = l.page_r_id

INNER JOIN links AS ls

ON ls.link_id = p.link_id

WHERE anchor_match="None"

');

PREPARE stmt FROM @query1; EXECUTE stmt; DEALLOCATE PREPARE stmt;

OPEN curl;

my_loop:LOOP

FETCH curl INTO link_id_var,error_type_var,source_url_var;

INSERT INTO errors (link_id_var,error_type,source_url) VALUES (link_id_var,errorz,source_url_var) ON DUPLICATE KEY UPDATE error_type=error,source_url=source_url_var;

IF done=1 THEN

LEAVE my_loop;

END IF;

END LOOP my_loop;

CLOSE curl;

DROP TEMPORARY TABLE IF EXISTS cursor_temp;

END$$