mssql 转mysql自定义函数的有关问题

mssql 转mysql自定义函数的问题
以前用sqlserver数据库现在转到mysql上了就最后一个函数不会转其他都弄好了 ...请教一下高手先谢谢了

函数一

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER FUNCTION [dbo].[fu_GetPhoneNamesByOfficeID]
(
@OfficeID int
)
RETURNS varchar(250)
AS
BEGIN

declare @Str varchar(250), @PhoneName varchar(50), @isFirst bit
select @Str=''
set @isFirst=0

declare name_cur cursor scroll for

select
p.手机品牌+ ' ' + p.手机型号
from
TB_BAS_Office_Model m inner join phoneInfo p on p.flngID=m.flngPhoneID and m.flngOfficeID=@OfficeID

order by p.手机品牌, p.flngID desc

open name_cur

fetch name_cur
into @PhoneName

while @@fetch_status = 0
begin
if @isFirst=0
begin
set @isFirst=1
select @Str=@Str + @PhoneName
end
else
begin
select @Str=@Str + ',' + @PhoneName
end
fetch next from name_cur
into @PhoneName
end

close name_cur
deallocate name_cur

-- 返回值
return @Str
End

最好能做好我实在是搞不定了.....谢谢了麻烦版主了

------解决方案--------------------

SQL code

ALTER FUNCTION  fu_GetPhoneNamesByOfficeID
( 
OfficeID int 
)   
RETURNS varchar(250)   
AS   
BEGIN

declare Str varchar(250);
declare PhoneName varchar(50);
declare isFirst bit;
set  Str='';
set isFirst=0;

declare name_cur cursor  for
select   
concat(p.手机品牌, ' ' , p.手机型号 )
from   
TB_BAS_Office_Model m inner join phoneInfo p on p.flngID=m.flngPhoneID and m.flngOfficeID=@OfficeID
order by p.手机品牌, p.flngID desc;

open name_cur;

fetch name_cur into PhoneName;

repeat 
    
if isFirst=0 then

set isFirst=1;
set Str=concat(Str, PhoneName);
else
set Str=concat(Str ,',',PhoneName);
end if 
fetch name_cur into PhoneName;
until 0 end repeat ;

close name_cur ;
    
return Str;

End

------解决方案--------------------

DELIMITER $$

CREATE
 /*[DEFINER = { user | CURRENT_USER }]*/
 FUNCTION `jnxp`.`fu_GetPhoneNamesByOfficeID`()
 RETURNS TYPE
 /*LANGUAGE SQL
 | [NOT] DETERMINISTIC
 | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
 | SQL SECURITY { DEFINER | INVOKER }
 | COMMENT 'string'*/
 BEGIN

 END$$

DELIMITER ;

转义用的 必须用上

mssql 转mysql自定义函数的有关问题

相关推荐