MYSQL學習call getPictureList('402880e63789b63a013789b646dc0000',1,5);?
set max_sp_recursion_depth=12;?

MYSQL學習CREATE PROCEDURE getPictureList (IN rootCategoryId varchar(32),IN m INT,IN n INT)?
BEGIN?
?? CREATE TEMPORARY TABLE IF NOT EXISTS tempCategoryList?
?? (sno int primary key auto_increment,?
??? category_id varchar(32),?
??? depth int?
?? );?
?? DELETE FROM tempCategoryList;?
?
?? CALL getCategoryList(rootCategoryId,0);?
????
?? set? @query=concat(?
?????? ' select p.*',?
?????? ' from tempCategoryList t,picture p,picture_category pc ',?
?????? ' where t.category_id = pc.category_id and pc.pic_id = p.pid ',??
?????? ' order by p.hot_value desc? limit ',m,',',n);?
????
?? select @query;?
????
?? prepare stmt1 from @query;?
?? execute stmt1;?
?? deallocate prepare stmt1;???
?
END;?
?
CREATE PROCEDURE getCategoryList (IN rootCategoryId varchar(32),IN nDepth INT)?
BEGIN?
????? DECLARE done varchar(32) DEFAULT "";?
????? DECLARE b varchar(32);?
????? DECLARE cur1 CURSOR FOR SELECT category_id FROM category where parent=rootCategoryId;?
????? DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;?
?????
????? insert into tempCategoryList values (null,rootCategoryId,nDepth);?
?????
????? OPEN cur1;?
?????
????? FETCH cur1 INTO b;?
????? WHILE done="" DO?
????????????? CALL getCategoryList(b,nDepth+1);?
????????????? FETCH cur1 INTO b;?
????? END WHILE;?
?????
????? CLOSE cur1;?
END;