在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';成功!
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';成功!
MYSQL在SELECT中赋值用:=
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;
select @aa:=@aa+Py from hzpy where Hz = substring(hz,i,1)
www.access911.net/csdn
,用WINRAR压缩
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('就寺一')
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