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

2012-05-28

mssql 转mysql自定义函数的问题以前用sqlserver数据库现在转到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   BEGINdeclare Str varchar(250);declare PhoneName varchar(50);declare isFirst bit;set  Str='';set isFirst=0;declare name_cur cursor  forselect   concat(p.手机品牌, ' ' , p.手机型号 )from   TB_BAS_Office_Model m inner join phoneInfo p on p.flngID=m.flngPhoneID and m.flngOfficeID=@OfficeIDorder by p.手机品牌, p.flngID desc;open name_cur;fetch name_cur into PhoneName;repeat     if isFirst=0 thenset isFirst=1;set Str=concat(Str, PhoneName);elseset 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 ;

转义用的 必须用上

热点排行

Mysql

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