mysql處理遞迴的一個例子

只是一個例子，簡單記錄一下！

delimiter //
drop procedure if exists getChildModel //
CREATE PROCEDURE getChildModel(IN startId INT,in inAdmin int)
BEGIN

DECLARE _id varchar(4) DEFAULT '0';
DECLARE _path VARCHAR(2000);
DECLARE _Last bigint DEFAULT 0;

CREATE TEMPORARY TABLE IF NOT EXISTS temp_table (
id varchar(4) NOT NULL ,
path varchar(2000) default NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB TYPE = HEAP;

CREATE TABLE IF NOT EXISTS pp_table (
topid int not null,
id varchar(4) NOT NULL ,
path varchar(2000) default NULL,
PRIMARY KEY (topid,id)
) ENGINE=InnoDB;

delete from temp_table;

insert into temp_table(id, path)
select startId, model_parent_Id from gweb_model src where model_aiid = startId;


set _id = startId;
set _path = startId;

WHILE ( _id <> '0') DO
insert into temp_table(id, path)
select model_aiid, model_parent_Id
from gweb_model src where src.model_parent_Id=_id ;
set _last = _id;
set _id = 0;
select id, path into _id, _path from temp_table where id>_last limit 1;
END WHILE;

insert into pp_table select startId,id,path from temp_table;

insert into dzy_permission_all select inAdmin,id from temp_table;

truncate table temp_table;

END
//

[@more@]