公司的数据库正准备由Ms_sql转到MySql上,所以,相应的函数和存储过程都要转到MySql上。现在遇到很多搞不懂的问题,请高手指点
原来Ms_Sql函数是这样的:create FUNCTION JobSeeker_GetJobFunction(@JobFunction Varchar(50),@JobFunction2 Varchar(50),@Language char(2))
RETURNS varchar(300) AS
BEGIN
declare @ReturnStr varchar(300)
set @ReturnStr=''
Select @ReturnStr=(case when @ReturnStr='' then '' else @ReturnStr+',' end)+ (case @Language when 'CN' then CN_JobFunction else EN_JobFunction end)
From CODE_JobFunction(nolock)
where JobFunction_CODE in (@JobFunction1,@JobFunction2)
return @ReturnStrEND我现在把它转成MySql上函数:
DELIMITER $$
CREATE FUNCTION JobSeeker_GetJobFunction (
JobFunction1 Varchar(50),JobFunction2 Varchar(50),Languages char(2)
) RETURNS varchar(300)
BEGIN
declare ReturnStr varchar(300);
set ReturnStr='';
Select ReturnStr:=concat((case when ReturnStr='' then '' else ReturnStr+',' end), (case Languages when 'CN' then CN_JobFunction else EN_JobFunction end))
From CODE_JobFunction(nolock)
where JobFunction_CODE in (JobFunction1,JobFunction2);
return ReturnStr;
END $$DELIMITER ;结果,在MySql执行时,总是在Select ReturnStr:=附近报错,但不知是什么原因,请高手指点@!!
原来Ms_Sql函数是这样的:create FUNCTION JobSeeker_GetJobFunction(@JobFunction Varchar(50),@JobFunction2 Varchar(50),@Language char(2))
RETURNS varchar(300) AS
BEGIN
declare @ReturnStr varchar(300)
set @ReturnStr=''
Select @ReturnStr=(case when @ReturnStr='' then '' else @ReturnStr+',' end)+ (case @Language when 'CN' then CN_JobFunction else EN_JobFunction end)
From CODE_JobFunction(nolock)
where JobFunction_CODE in (@JobFunction1,@JobFunction2)
return @ReturnStrEND我现在把它转成MySql上函数:
DELIMITER $$
CREATE FUNCTION JobSeeker_GetJobFunction (
JobFunction1 Varchar(50),JobFunction2 Varchar(50),Languages char(2)
) RETURNS varchar(300)
BEGIN
declare ReturnStr varchar(300);
set ReturnStr='';
Select ReturnStr:=concat((case when ReturnStr='' then '' else ReturnStr+',' end), (case Languages when 'CN' then CN_JobFunction else EN_JobFunction end))
From CODE_JobFunction(nolock)
where JobFunction_CODE in (JobFunction1,JobFunction2);
return ReturnStr;
END $$DELIMITER ;结果,在MySql执行时,总是在Select ReturnStr:=附近报错,但不知是什么原因,请高手指点@!!
(case when @ReturnStr='' then '' else concat(@ReturnStr,',') end),
(case Languages when 'CN' then CN_JobFunction else EN_JobFunction end))
From CODE_JobFunction(nolock)
where JobFunction_CODE in (JobFunction1,JobFunction2); CODE_JobFunction(nolock):表名 OR SP(参数)?
From CODE_JobFunction(nolock)
where JobFunction_CODE in (JobFunction1,JobFunction2);
参见mySQL手册,从表中取某列值到变量语法格式为;
19.2.7.3. SELECT ... INTO Statement
Select concat((case when ReturnStr='' then '' else ReturnStr+',' end), (case Languages when 'CN' then CN_JobFunction else EN_JobFunction end))
into ReturnStr
From CODE_JobFunction(nolock)
where JobFunction_CODE in (JobFunction1,JobFunction2);
另外,@ReturnStr这个变量不能加@符号,一加就报错。
按照你的意思,我的函数依然是无法创建。
是不是函数中有什么其它限制呢?
CREATE FUNCTION JobSeeker_GetJobFunction (
JobFunction1 Varchar(50),JobFunction2 Varchar(50),Languages char(2)
) RETURNS varchar(300)
BEGIN
declare ee varchar(300);
set @ReturnStr='';
Select @ReturnStr:=concat(
(case when @ReturnStr='' then '' else concat(@ReturnStr,',') end),
(case Languages when 'CN' then CN_JobFunction else EN_JobFunction end))
into ee From `CODE_JobFunction(nolock)`
where JobFunction_CODE in (JobFunction1,JobFunction2);
return ee;
END $$DELIMITER ;
BEGIN
declare ReturnStr varchar(300);
-- set @temp='';
Select concat(
(case when ReturnStr='' then '' else concat( ReturnStr,',') end),
(case Languages when 'CN' then CN_JobFunction else EN_JobFunction end))
From CODE_JobFunction
where JobFunction_CODE in (JobFunction1,JobFunction2,JobFunction3) into ReturnStr;
return ReturnStr;
END $$
这样子又好了,真的不知真正的问题出在哪里!语法上现在已经通过了;
测试存储过程是遇到了一个新问题:因为我的where条件里使用了in(...)语法,结果会返回大于一的记录,可Mysql不会 把结果集通过逗号连接起来!
怎么解决呢??
Result consisted of more than one row