错误回复:在 MySQL 存储过程中声明整数 [英] Error Re: declaring integer in MySQL stored procedure

问题描述

我是存储过程的新手，正在尝试实现问题的解决方案@HierarchicalMySQL II 中的查询.我在下面发布了我的代码.我立即挂断了第三行.看起来我的问题得到了回答@在 MySQL 存储函数中声明整数变量时出错，但我仍然无法让它工作.

I'm new to stored procedures and am trying to implement the solution to the question @ Hierarchical Query in MySQL II. I posted my code below. I immediately got hung up on the third line. It looks like my question is answered @ Error declaring integer variable inside MySQL stored function, but I still can't get it to work.

对于我的第一个实验，我想通过将 1(动物界)指定为整数来查询整个分类树.

For my first experiment, I want to query the entire taxonomic tree by designating 1 (the animal kingdom) as the integer.

我一直在看一些存储过程教程，但我还不清楚它究竟是如何工作的，而且我可能先于马车.据我了解，可以通过 MySQL 或 PHP 查询存储过程，我更愿意在 PHP 中进行查询.我不明白下面的代码是存储过程查询还是我在编写查询之前必须做的事情.我已经创建了练习桌 (t).

I've been looking at some stored procedure tutorials, but it isn't yet clear to me exactly how it works, and I may have the cart before the horse. As I understand it, a stored procedure can be queried via MySQL or PHP, and I would prefer to do it in PHP. I don't understand if the code below is a stored procedure query or something I have to do before I can write queries. I've created the practice table (t).

所以也许我应该换一种方式问我的问题:如果我已经有一个带有父 ID 字段的练习表，并且我想学习如何使用 PHP 查询该表，我还需要弄乱代码吗?下面是为了创建一个存储过程"?或者我可以使用 PHP 编写的查询创建存储过程吗?

So maybe I should ask my question another way: If I have already have a practice table with a field for Parent ID's, and I want to learn how to query that table with PHP, do I still need to mess with the code below in order to create a "stored procedure"? Or can I create a stored procedure with a query written in PHP?

DELIMITER $$
create procedure showHierarchyUnder
(
SET i = 1;
)
BEGIN
 declare bDoneYet boolean default false;
 declare working_on int;
 declare next_level int;
 declare theCount int;
CREATE temporary TABLE xxFindChildenxx
(   N int not null,
    processed int not null,
    level int not null,
    parent int not null
);
set bDoneYet=false;
insert into xxFindChildenxx (N,processed,level,parent) select  theId,0,0,0;
while (!bDoneYet) do
    select count(*) into theCount from xxFindChildenxx where processed=0;

    if (theCount=0) then 
        set bDoneYet=true;
    else
        SELECT N,level+1 INTO working_on,next_level FROM xxFindChildenxx where processed=0 limit 1;
        insert into xxFindChildenxx (N,processed,level,parent)
        select N,0,next_level,parent
        from t
        where parent=working_on;
        update xxFindChildenxx set processed=1 where N=working_on;
    end if;
end while;
delete from xxFindChildenxx where N=theId;
select level,count(*) as lvlCount from xxFindChildenxx group by level;
drop table xxFindChildenxx;
END
??

推荐答案

这将为您创建存储过程.为了在存储过程中设置变量，您必须首先声明它，然后才能在声明所有变量后设置它.注意 SET i = 1; 不再位于 () 之间，并且 i 已被 declared i int;代码>.看起来您正试图传入一个变量，例如 create procedure showHierarchyUnder(IN i int(10)) 但这将在调用该过程时期望一个值.你仍然可以这样做，但如果 i 总是 1，那就没有意义了.将来如有疑问，请访问 dev.mysql.

This will create the stored procedure for you. In order to set a variable in a stored procedure you must first declare it and only then can you set it after you have declared all your variables. Notice how the SET i = 1; is not in between the () anymore and i has been declared i int;. Looks like you were trying to pass in a variable like create procedure showHierarchyUnder(IN i int(10)) but that would be expecting a value when the procedure is called. You can still do that but if i will always be 1, there is no point. In the future when in doubt just go to dev.mysql.

DELIMITER //
create procedure showHierarchyUnder()
BEGIN
 declare bDoneYet boolean default false;
 declare working_on int;
 declare next_level int;
 declare theCount int;
 declare i int;
 SET i = 1;
CREATE temporary TABLE xxFindChildenxx
(   N int not null,
    processed int not null,
    level int not null,
    parent int not null
);
set bDoneYet=false;
insert into xxFindChildenxx (N,processed,level,parent) select  theId,0,0,0;
while (!bDoneYet) do
    select count(*) into theCount from xxFindChildenxx where processed=0;

    if (theCount=0) then 
        set bDoneYet=true;
    else
        SELECT N,level+1 INTO working_on,next_level FROM xxFindChildenxx where processed=0 limit 1;
        insert into xxFindChildenxx (N,processed,level,parent)
        select N,0,next_level,parent
        from t
        where parent=working_on;
        update xxFindChildenxx set processed=1 where N=working_on;
    end if;
end while;
delete from xxFindChildenxx where N=theId;
select level,count(*) as lvlCount from xxFindChildenxx group by level;
drop table xxFindChildenxx;
END//
DELIMITER ;

这篇关于错误回复:在 MySQL 存储过程中声明整数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！