mysql 函数和存储过程 例子

drop function getParentUserIds;
DELIMITER //
create FUNCTION `getParentUserIds`( `userId` int)
RETURNS varchar(4000) 
begin
declare resultData varchar(4000);
declare tempParentId int(50);
set resultData = userId;
 select parent_user_id into tempParentId from t_user_info where user_id = userId;
 while tempParentId>0 do
    select parent_user_id into tempParentId from t_user_info where user_id = userId;
    mysql_debug('dsfsdf');
    if tempParentId>0 then
       set userId = tempParentId;
       set resultData = concat(tempParentId,concat(',',resultData));
    elseif tempParentId <0 then
       set userId = -1;
    end if;
 end while;
return resultData;
END//






 drop function if exists getUserTreeData;
delimiter $$
create function getUserTreeData(userId int(20))
returns varchar(4000)
begin
declare updateUesrId int(20);
declare updateData varchar(4000);
declare unUpdateData varchar(100);
declare tempUserId int(20);
declare debugLog varchar(100);
declare debugName varchar(100) default 'getUserTreeData';
declare debugInfo varchar(1000);
declare resultData varchar(4000);


set debugLog = clearDebugLog(debugName);


select agency_user_id into tempUserId from t_user_info where user_id = userId;
set debugLog = debugLog(debugName,'1', concat('tempUserId is ',tempUserId));


if tempUserId<=0 then
     set unUpdateData = userId;
  set updateData = 0;
  set updateUesrId = 0;
  return concat(updateUesrId,'|',unUpdateData,'|',updateData);
end if;
   set updateData = userId;
   set userId = tempUserId;
set debugLog = debugLog(debugName,'2', concat('tempUserId is ',tempUserId));  
 
while tempUserId>0 and updateUesrId is null do
     select agency_user_id,first_agency_user_id into tempUserId ,  updateUesrId from t_user_info where user_id = userId;
     if tempUserId<=0 then
  set unUpdateData = userId;
  else
  set updateData = concat(userId,',',updateData);
  end if;
     set userId = tempUserId;
     set debugInfo = concat('tempUserId is ',tempUserId,';updateUesrId is ',isNullValue(updateUesrId),';unUpdateData is ',isNullValue(unUpdateData),'; updateData is ',isNullValue(updateData));
 set debugLog =   debugLog(debugName,'3', debugInfo);  
end while;
if updateUesrId is null or updateUesrId <=0 then
    set updateUesrId = strSplit(updateData,',',0);
end if;
    set debugLog =   debugLog(debugName,'4', concat('tempUserId is ',tempUserId));   
   set resultData = concat_ws('|',ifnull(updateUesrId,''),ifnull(unUpdateData,''),ifnull(updateData,''));  
return resultData;
end $$