我要评论mysql oracle中的start with...connect by
工作需要，迁移数据库时发现使用了oracle中的start with来进行树的递归查询，所以自己动手丰衣足食。
通过一番搜索后发现
大家的实现基本都是这样的：
create function querychildrenareainfo(areaid int) returns varchar(4000) begin declare stemp varchar(4000); declare stempchd varchar(4000); set stemp='$'; set stempchd = cast(areaid as char); while stempchd is not null do set stemp= concat(stemp,',',stempchd); select group_concat(id) into stempchd from t_areainfo where find_in_set(parentid,stempchd)>0; end while; return stemp;
但是这样的代码没法复用（而且我的navicat居然建立函数失败，或者各种错误，所以我使用了存储过程，效果一样），所以我们使用set和execute来进行语句的拼接和执行，
修改后
如下：
create procedure getchildlist
in rootid decimal(65),
in tablesname varchar(6000),
out stemp varchar(6000)
begin
declare stempchd varchar(4000);
set stemp='$';
set stempchd = cast(rootid as char);
while stempchd is not null do
set stemp= concat(stemp,',',stempchd);
set @sqlexe = concat("select group_concat(id) into stempchd from " , tablesname , " where find_in_set(parentid,stempchd)>0;")
prepare sqlexe from @sqlexe;
execute sqlexe;
end while;
end;这样一来我们就可以将表名作为参数传入，但是一番执行后，你会发现，哦豁，它居然报了这样一个错：
1327 - undeclared variable: stempchd；
这个低级错误困扰了我半天，我不是声明了stempchd为declare吗？
答案很简单
预处理语句（也就是我们的prepare）中，只接受@声明的参数。因为在存储过程中，使用动态语句，预处理时，动态内容必须赋给一个会话变量，也就是@形式声明的变量，而declare声明的是存储过程变量，具体的内容涉及到更深的知识，我暂时无法找到原因。
前文是自上而下的查询，自下而上的查询其实很简单，只要替换一下参数和语句内容就可以了，
具体如下：
create procedure `getparentlist`(
in rootid decimal(65,0),
in tablesname varchar ( 500 ),
out stemp varchar ( 6000 ) )
begin
declare
parentid decimal(65);
set stemp = '$';
set @stempchd = cast(rootid as char);
while @stempchd <> 0 do
set stemp = concat( stemp, ',', @stempchd );
set @sqlcmd = concat("select parentid into @stempchd from " , tablesname , " where cateid = " , @stempchd , ";");
prepare stmt from @sqlcmd;
execute stmt;
end while;
deallocate prepare stmt;
end别忘了，最后要执行一下deallocate语句，释放预处理sql，免得session的预处理语句过多，达到max_prepared_stmt_count的上限值。
总结
以上为个人经验，希望能给大家一个参考，也希望大家多多支持代码网。
发表评论