Mysql-过程执行失败，没有错误

Question

当我想声明这个程序时，它没有声明这个代码没有报告任何报告
我不知道那是问题

我想在solr索引中使用此过程，但此过程不执行

DELIMITER $$
create procedure getAllStuff()
begin
declare category_id int(10);
declare category_name varchar default NULL;
declare stateCommands varchar(255) default NULL;
declare leafCats INT(10) default NULL;
declare tableName varchar(255) default NULL;
declare finished int(10) default 0;

declare leafCats_cursor CURSOR FOR select id,name from category where rgt=lft+1;
declare CONTINUE handler FOR NOT FOUND set finished=1;

create temporary table IF NOT EXISTS leafCats (
id int null primary key auto_increment,
category_id int,
tableName varchar(255)
);

open leafCats_cursor;
set_leafCats: LOOP
fetch leafCats_cursor into category_id,category_name;
if finished =1 then
leave set_leafCats
endif
set tableName=replace(catgeory_name,' ','_');
set tableName = concat('stuff_',tableName);
insert into leafCats values (NULL,category_id,tableName);
end loop set_leafCats;
close leafCats_cursor;


declare cats_cursor CURSOR FOR select category_id,category_name from leafCats;

open cats_cursor;
get_cats: LOOP
fetch cats_cursor into category_id,category_name;
if finished =1 then
leave set_leafCats;
endif;
if stateCommands != NULL then
set stateCommands=concat(sql,'select t.id as id,t.name,t.overall,c.id as 
category_id       from '.tableName .' t join category c where c.id=' . category_id);
else 
set stateCommands=concat(sql,'union all select t.id asid,t.name,t.overall,c.id  as     
category_id from '.tableName .' t join      category c where c.id=' . category_id);
end if;

end loop get_cats;
close cats_cursor;


PREPARE s1 FROM stateCommands;
EXECUTE s1;
DEALLOCATE PREPARE s1;


end @@
DELIMITER ;

当我将分隔符更改为@@时，会出现此错误

错误1064 (42000)：你有一个错误在你的SQL语法；检查手册，对应于你的MySQL服务器版本的正确语法使用附近的'默认NULL；声明stateORds varchar（255）默认NULL；在第4行mysql声明叶猫

Answer 1

我提出了几点需要考虑的意见：

• IS Not NULL：测试值是否为NULL。12.3.2。比较函数和运算符。
• preparable_stmt是字符串文字或包含SQL语句文本的用户变量。13.5.1。准备语法。
• 游标声明必须出现在处理程序声明之前以及变量和条件声明之后。13.6.6。游标。

我还添加了一个我认为您可能会觉得有用的示例：

DELIMITER $$

DROP PROCEDURE IF EXISTS `getAllStuff`$$

CREATE PROCEDURE `getAllStuff`()
BEGIN
    DECLARE `finished` TINYINT(0) DEFAULT 0;
    DECLARE `category_id` INT UNSIGNED;
    DECLARE `category_name` VARCHAR(255);
    DECLARE `leafCats_cursor` CURSOR FOR
    SELECT `id`, `name` FROM `category` WHERE `rgt` = `lft` + 1;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET `finished` = 1;
    SET @`statecommands` := NULL;
    OPEN `leafCats_cursor`;
    `set_leafCats`: LOOP
        FETCH `leafCats_cursor` INTO `category_id`, `category_name`;
        IF `finished` = 1 THEN
            LEAVE `set_leafCats`;
        END IF;
        SET @statecommands := CONCAT(IF(@statecommands IS NOT NULL, CONCAT(@statecommands, ' \nUNION ALL'), ''), '
            SELECT
                `t`.`id` AS `id`,
                `t`.`name`,
                `t`.`overall`,
                `c`.`id` AS `category_id`
            FROM `', CONCAT('stuff_', REPLACE(`category_name`, ' ', '_'), '`'), ' `t`
                JOIN `category` `c` WHERE `c`.`id` = ', `category_id`);
    END LOOP `set_leafCats`;
    CLOSE `leafCats_cursor`;
    PREPARE `exec` FROM @`statecommands`;
    EXECUTE `exec`;
    DEALLOCATE PREPARE `exec`;
END$$

DELIMITER ;

您将得到如下语句，将执行该语句：

SELECT
    `t`.`id` AS `id`,
    `t`.`name`,
    `t`.`overall`,
    `c`.`id` AS `category_id`
FROM `stuff_category_1` `t`
    JOIN `category` `c` WHERE `c`.`id` = 1 
UNION ALL
SELECT
    `t`.`id` AS `id`,
    `t`.`name`,
    `t`.`overall`,
    `c`.`id` AS `category_id`
FROM `stuff_category_2` `t`
    JOIN `category` `c` WHERE `c`.`id` = 2 
UNION ALL
SELECT
    `t`.`id` AS `id`,
    `t`.`name`,
    `t`.`overall`,
    `c`.`id` AS `category_id`
FROM `stuff_category_3` `t`
    JOIN `category` `c` WHERE `c`.`id` = 3 
UNION ALL
SELECT
    `t`.`id` AS `id`,
    `t`.`name`,
    `t`.`overall`,
    `c`.`id` AS `category_id`
FROM `stuff_category_4` `t`
    JOIN `category` `c` WHERE `c`.`id` = 4;