mysql存储过程递归_MySQL用存储过程实现递归查询（一）

1 droptableifexistsemployee;2 3 createtableemployee4 (5 emp_idsmallintunsignednotnullauto_incrementprimarykey,6 namevarchar(32)notnull,7 boss_idsmallintunsignednull,8 keyboss_id_idx(boss_id)9 )engine=innodb;10 11 12 insertintoemployee (name, boss_id)values13 ('foo',null),14 ('ali later',1), ('megan fox',1),15 ('jessica alba',2), ('eva longoria',2),16 ('keira knightley',3), ('liv tyler',3),17 ('sophie marceau',5);18 19 20 delimiter ;21 22 dropprocedureifexistsemployee_hier;23 24 delimiter #25 26 createprocedureemployee_hier27 (28 inp_emp_idsmallintunsigned29 )30 begin31 32 declarep_donetinyintunsigneddefault(0);33 declarep_depthsmallintunsigneddefault(0);34 35 createtemporarytablehier(36 boss_idsmallintunsigned,37 emp_idsmallintunsigned,38 depthsmallintunsigned39 )engine=memory;40 41 insertintohiervalues(null, p_emp_id, p_depth);42 43 /*http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html*/44 45 createtemporarytableemps engine=memoryselect*fromhier;46 47 whilep_done<>1do48 49 ifexists(select1fromemployee einnerjoinhierone.boss_id=hier.emp_idandhier.depth=p_depth)then50 51 insertintohierselecte.boss_id, e.emp_id, p_depth+152 fromemployee einnerjoinempsone.boss_id=emps.emp_idandemps.depth=p_depth;53 54 setp_depth=p_depth+1;55 56 truncatetableemps;57 insertintoempsselect*fromhierwheredepth=p_depth;58 59 else60 setp_done=1;61 endif;62 63 endwhile;64 65 select66 e.emp_id,67 e.nameasemp_name,68 b.emp_idasboss_emp_id,69 b.nameasboss_name,70 hier.depth71 from72 hier73 innerjoinemployee eonhier.emp_id=e.emp_id74 innerjoinemployee bonhier.boss_id=b.emp_id;75 76 droptemporarytableifexistshier;77 droptemporarytableifexistsemps;78 79 end#80 81 delimiter ;82 83 /*84 85 select * from employee;86 87 emp_id name boss_id88 ====== ==== =======89 1 foo null90 2 ali later 191 3 megan fox 192 4 jessica alba 293 5 eva longoria 294 6 keira knightley 395 7 liv tyler 396 8 sophie marceau 597 98 call employee_hier(1);99 100 emp_id emp_name boss_emp_id boss_name depth101 ====== ======== =========== ========= =====102 2 ali later 1 foo 1103 3 megan fox 1 foo 1104 4 jessica alba 2 ali later 2105 5 eva longoria 2 ali later 2106 6 keira knightley 3 megan fox 2107 7 liv tyler 3 megan fox 2108 8 sophie marceau 5 eva longoria 3109 110 call employee_hier(3);111 112 emp_id emp_name boss_emp_id boss_name depth113 ====== ======== =========== ========= =====114 6 keira knightley 3 megan fox 1115 7 liv tyler 3 megan fox 1116 */