首页 文章

Mysql - 过程执行失败而不是错误

提问于
浏览 次
0

当我想声明此过程时，它不会声明此代码不报告任何报告
我不知道那是问题所在

我想在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 ;

当我将分隔符改为@@这个错误apear

错误1064（42000）：您的SQL语法有错误;检查与MySQL服务器版本对应的手册，以便在'default NULL附近使用正确的语法;声明stateCommands varchar（255）默认为NULL;声明leafCats'在第4行mysql> DELIMITER;

mysql stored-procedures

1 回答

  • 1

    我评论了几点要考虑：

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

    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;
    回复于

相关问题