mysql function sf_get_full_manage_name_en

begin
  declare done int default 0;
  declare v_len int default 3;
  declare v_name_en varchar(255);
  declare v_full_name_en varchar(1023) default '';
  if v_manage_code is null or v_manage_code = '' then
      return '';
  end if;
 
  the_loop: loop
      select name_en into v_name_en from categories where code = substring(v_manage_code, 1, v_len);
      if done = 1 then
          set v_name_en = '';
      end if;
      if v_len > length(v_manage_code) || v_len > 12 then
            leave the_loop;
      end if;
      set v_len = v_len + 3;
      set v_full_name_en = concat(v_full_name_en, v_name_en, '/');
  end loop the_loop;
  return substring(v_full_name_en, 1, char_length(v_full_name_en) - 1);

end

函数定义结束

函数使用

$category_model=BizDB::select(BizDB::expr('sf_get_full_manage_name(code) as name ,sf_get_full_manage_name_en(code) as name_en'))
        ->from('categories')
        ->where('code', '=', $template['category_code'])
        ->execute()
        ->as_array();

例子2 定义名称为test的函数

BEGIN
    #Routine body goes here...
RETURN (SELECT COUNT(id) FROM users);
    
END

调用函数SELECT test()