存储过程利用递归查找出树节点以及所有子节点

2015-1-29星期四
调用存储过程，查询所有的圈子
call circles ();


与回帖有关的储存过程
drop PROCEDURE if EXISTS replies;
delimiter //




CREATE PROCEDURE replies ()
BEGIN
 
  set @@max_sp_recursion_depth=99;
  select max_sp_recursion_depth;
SELECT
a.*
FROM
diz_reply AS a;
 


END//


call replies();
存储过程相互调用
drop PROCEDURE if EXISTS replies;
delimiter //




CREATE PROCEDURE replies ()
BEGIN
 
  set @@max_sp_recursion_depth=99;
-- SELECT
-- a.*
-- FROM
-- diz_reply AS a;
 
 call marc();


END//
 call replies();


 drop PROCEDURE if EXISTS marc;
delimiter //
 create PROCEDURE marc()
begin 
select 
a.* from diz_circle as a;
end;
call marc();


测试游标：
drop PROCEDURE if EXISTS replies;
delimiter //




CREATE PROCEDURE replies ()
BEGIN
  DECLARE iid VARCHAR(32);
  DECLARE nname VARCHAR(32);
    declare cur1 CURSOR FOR SELECT a.id,a.topic_id from diz_reply as a ;
    declare CONTINUE HANDLER FOR SQLSTATE '02000' SET iid = null;
 OPEN cur1;
FETCH cur1 into iid,nname;
while(iid is not null) DO
select iid,nname;
fetch cur1 into iid,nname;
end while;


END//
 call replies();


循环语句测试：
drop PROCEDURE if EXISTS replies;
delimiter //
CREATE procedure replies(num int)
BEGIN
while(num>50) DO
insert into ant(id,name) VALUES(num,'marc');
select * from ant;
set num=num-1;
end while;
end//
call replies(60);


利用游标插入数据到指定表：
drop PROCEDURE if EXISTS replies;
delimiter //




CREATE PROCEDURE replies ()
BEGIN
  DECLARE iid VARCHAR(32);
  DECLARE nname VARCHAR(32);
    declare cur1 CURSOR FOR SELECT a.id,a.topic_id from diz_reply as a ;
    declare CONTINUE HANDLER FOR SQLSTATE '02000' SET iid = null;
 OPEN cur1;
FETCH cur1 into iid,nname;
while(iid is not null) DO
INSERT into ant(id,name) values(iid,nname);
fetch cur1 into iid,nname;
end while;


END//
 call replies();


创建临时表：
 create temporary  table if not exists tmp_table(id bigint(20),name varchar(50)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
利用循环插入数据到指定表：
drop PROCEDURE if EXISTS replies;
delimiter //
CREATE procedure replies(num int)
BEGIN
 
while(num>50) DO
insert into tem_table(id,name) VALUES(num,'info');
set num=num-1;
end while;
end//
call replies(60);


存储到固定表，然后再读取：
存储：
drop PROCEDURE if EXISTS replies;
delimiter //




CREATE PROCEDURE replies ()
BEGIN
  DECLARE iid VARCHAR(32);
  DECLARE nname VARCHAR(32);
    declare cur1 CURSOR FOR SELECT a.id,a.topic_id from diz_reply as a ;
    declare CONTINUE HANDLER FOR SQLSTATE '02000' SET iid = null;
 OPEN cur1;
FETCH cur1 into iid,nname;
while(iid is not null) DO
INSERT into tem_table(id,name) value(iid,nname);
fetch cur1 into iid,nname;
end while;


END//
 call replies();
读取：
drop procedure if EXISTS marc;
delimiter //
CREATE PROCEDURE marc()
BEGIN


create temporary  table if not exists tem_table(id VARCHAR(32),name varchar(32)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
call replies();
select * from tem_table;
drop temporary table if EXISTS tem_table;
end//


call marc();


创建表存储查询结果，再显示结果
储存：
drop PROCEDURE if EXISTS replies;
delimiter //




CREATE PROCEDURE replies ()
BEGIN
  DECLARE iid VARCHAR(32);
  DECLARE nname VARCHAR(32);
    declare cur1 CURSOR FOR SELECT a.id,a.topic_id from diz_reply as a ;
    declare CONTINUE HANDLER FOR SQLSTATE '02000' SET iid = null;
 OPEN cur1;
FETCH cur1 into iid,nname;
while(iid is not null) DO
INSERT into tem_table(id,name) value(iid,nname);
fetch cur1 into iid,nname;
end while;


END//
 call replies();
创建表，显示信息：
drop procedure if EXISTS marc;
delimiter //
CREATE PROCEDURE marc()
BEGIN


create table if not exists tem_table(id VARCHAR(32),name varchar(32)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
call replies();
select * from tem_table;
drop temporary table if EXISTS tem_table;
end//


call marc();


创建临时表,存储到临时表，显示结果：
存储：
drop PROCEDURE if EXISTS replies;
delimiter //




CREATE PROCEDURE replies ()
BEGIN
  DECLARE iid VARCHAR(32);
  DECLARE nname VARCHAR(32);
    declare cur1 CURSOR FOR SELECT a.id,a.topic_id from diz_reply as a ;
    declare CONTINUE HANDLER FOR SQLSTATE '02000' SET iid = null;
 OPEN cur1;
FETCH cur1 into iid,nname;
while(iid is not null) DO
INSERT into tem_table(id,name) value(iid,nname);
fetch cur1 into iid,nname;
end while;


END//
 call replies();
创建临时表，显示结果：
drop procedure if EXISTS marc;
delimiter //
CREATE PROCEDURE marc()
BEGIN


create TEMPORARY table if not exists tem_table(id VARCHAR(32),name varchar(32)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
call replies();
select * from tem_table;
drop temporary table if EXISTS tem_table;
end//


call marc();


统计查询结果：
drop procedure if EXISTS marc;
delimiter //
CREATE PROCEDURE marc()
BEGIN


create TEMPORARY table if not exists tem_table(id VARCHAR(32),name varchar(32)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
call replies();
select count(*) from tem_table;
drop temporary table if EXISTS tem_table;
end//


call marc();


2015-1-30星期五
递归实现查找树节点，查找出指定回复下的所有节点：
递归实现查找：
drop PROCEDURE if EXISTS replies;
delimiter //




CREATE PROCEDURE replies (pid VARCHAR(32),layer int)
BEGIN
  DECLARE iid VARCHAR(32);
  DECLARE nname VARCHAR(32);
    declare cur1 CURSOR FOR SELECT a.id,a.topic_id from diz_reply as a  where a.parent_id=pid;
    declare CONTINUE HANDLER FOR SQLSTATE '02000' SET iid = null;
if(layer>0) THEN
 OPEN cur1;
FETCH cur1 into iid,nname;
while(iid is not null) DO
INSERT into tem_table(id,name) value(iid,nname);
call replies(iid,layer-1);
fetch cur1 into iid,nname;
end while;
end if;


END//
 call replies('402881f24b14aee0014b14d8a6fd0069',10);


创建临时表并且显示数据：
drop procedure if EXISTS marc;
delimiter //
CREATE PROCEDURE marc(pid VARCHAR(32),layer int)
BEGIN


create TEMPORARY table if not exists tem_table(id VARCHAR(32),name varchar(32)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
set @@max_sp_recursion_depth=99;
call replies(pid,layer);
select * from tem_table;
drop temporary table if EXISTS tem_table;
end//


call marc('402881f24b14aee0014b14d8a6fd0069',10);


查找根节点还有它的子节点：
利用递归查找所有子节点：
drop PROCEDURE if EXISTS replies;
delimiter //




CREATE PROCEDURE replies (pid VARCHAR(32),layer int)
BEGIN
  DECLARE iid VARCHAR(32);
  DECLARE nname VARCHAR(32);
    declare cur1 CURSOR FOR SELECT a.id,a.topic_id from diz_reply as a  where a.parent_id=pid;
    declare CONTINUE HANDLER FOR SQLSTATE '02000' SET iid = null;
if(layer>0) THEN
 OPEN cur1;
FETCH cur1 into iid,nname;
while(iid is not null) DO
INSERT into tem_table(id,name) value(iid,nname);
call replies(iid,layer-1);
fetch cur1 into iid,nname;
end while;
end if;


END//
 call replies('402881f24b14aee0014b14d8a6fd0069',10);
查找出根节点还有它的子节点：
drop procedure if EXISTS marc;
delimiter //
CREATE PROCEDURE marc(pid VARCHAR(32),layer int)
BEGIN


create TEMPORARY table if not exists tem_table(id VARCHAR(32),name varchar(32)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
set @@max_sp_recursion_depth=99;
call replies(pid,layer);
select * from (select a.id,a.topic_id from diz_reply as a where a.id=pid UNION select b.* from tem_table as b)temp;
drop temporary table if EXISTS tem_table;
end//


call marc('402881f24b14aee0014b14d8a6fd0069',10);