The question is published on by Tutorial Guruji team.
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.
For my first experiment, I want to query the entire taxonomic tree by designating 1 (the animal kingdom) as the integer.
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).
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
??
Answer
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 ;