Mysql自定义函数变量赋值问题

在Mysql里自定义个函数,在函数体里无法给变量赋值,请各位大虾指点:
CREATE FUNCTION fn_hztopy(hz varchar(30))
RETURNS varchar(30)

BEGIN
declare aa varchar(30);
declare num int;
declare i int;
set aa='';
set num=length(hz);
set i=1;
while i<=num do
  select aa=aa+Py from hzpy where Hz = substring(hz,i,1);//通过这样赋值在SQL SERVER里可以,但在MYSQL里不行.
  select aa=aa+substring(hz,i,1);//通过这样赋值在SQL SERVER里可以,但在MYSQL里不行.
  select i=i+1;//通过这样赋值在SQL SERVER里可以,但在MYSQL里不行.
end while;
return aa;
END;

运行后的结果报错,错误提示:没有返回值,如果我手动设置aa='123';成功!

12 个解决方案

#1

select aa:=aa+Py from hzpy where Hz = substring(hz,i,1);
MYSQL在SELECT中赋值用:=

#2

CREATE FUNCTION fn_hztopy(hz varchar(30))
RETURNS varchar(30)

BEGIN
declare aa varchar(30);
declare num int;
declare i int;
set aa='';
set num=length(hz);
set i=1;
while i<=num do
  select aa=aa+Py from hzpy where Hz = substring(hz,i,1);//这句过不去,这样的查询MYSQL不识别,把=换:=也不行
  if select found_rows()=0 then
     set aa:=aa+substring(hz,i,1);//这句可以过去了,除了把=换成:=外,把select换成set就可以了
  end if;
  select i=i+1;
end while;
return aa;
END;

#3

set @aa='';
select @aa:=@aa+Py from hzpy where Hz = substring(hz,i,1)

#4

带不带@还是一样~郁闷~

#5

不会吧，@AA没有累计？如果可以的话，将hzpy导成SQL文件，上传
www.access911.net/csdn
，用WINRAR压缩

#6

不知道在哪里上传,要不告诉我,你的邮箱,我把hzpy语句发给你,只有300多K

#7

或者把你QQ号告诉我,我加你,在QQ上聊的快一些~

#8

DELIMITER $$
drop function if exists `zz`.`fn_hztopy` $$
CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    FUNCTION `zz`.`fn_hztopy`( hz1 varchar(30))
    RETURNS varchar(30)
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
    BEGIN
declare aa1 varchar(30);
declare num int;
declare i int;
set @aa='';
set num=CHAR_LENGTH(hz1);
set i=1;
while i<=num do
select @aa:=concat(@aa,Py) into aa1 from hzpy where Hz = substring(hz1,i,1) ;
set i=i+1;
end while;
return aa1;
    END$$

DELIMITER ;

select fn_hztopy('就寺一')

#9

CREATE FUNCTION fn_hztopy(hz varchar(30))
RETURNS varchar(30)

BEGIN
declare aa1 varchar(30);
declare num int;
declare i int;
set @aa='';
set num=CHAR_LENGTH(hz);
set i=1;
while i<=num do
  select @aa:=concat(@aa,Py) into aa1 from hzpy where Hz = substring(hz,i,1);
  if select found_rows()=0 then
     set @aa=@aa+substring(hz,i,1);
  end if;
  set i=i+1;
end while;
return aa1;
END;
ERROR 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select found_rows()=0 then
     set @aa=@aa+substring(hz,i,1);
  end if;
  se' at line 13

#10

解决,3Q~散分

#11

怎么解决的啊？介绍一下啊

#12

看一下QQQ

#1