大家好,现在项目做oracle切换mysql,很多oracle支持的功能到mysql上就不支持了,现在遇到一个难题,原来的项目中oracle函数用得太多,现在切换起来工作量巨大,所以想在mysql上自定义一些函数,来实现oracle函数的功能。问题:
创建mysql函数的时候怎么实现函数个数的动态传入?如concat()函数的参数个数可以是2个或者更多。怎么实现?
我现在想做的是写一个函数,实现oracle的decode()函数功能;我知道有一个if()函数可以实现,但是if()函数的参数必须是3个;而我想写的函数是完全实现decode的功能,参数个数可以是3个或者3个以上。
创建mysql函数的时候怎么实现函数个数的动态传入?如concat()函数的参数个数可以是2个或者更多。怎么实现?
我现在想做的是写一个函数,实现oracle的decode()函数功能;我知道有一个if()函数可以实现,但是if()函数的参数必须是3个;而我想写的函数是完全实现decode的功能,参数个数可以是3个或者3个以上。
CREATE
PROCEDURE ps_yddl_get_gyjx_jgm_d(IN ps_jgm char(8) CHARACTER SET utf8, IN ps_ywbh varchar(20) CHARACTER SET utf8, IN ps_gybh varchar(350) CHARACTER SET utf8, IN ps_ksrq varchar(10) CHARACTER SET utf8, IN ps_jsrq varchar(10) CHARACTER SET utf8, IN ps_start int(11))
COMMENT ''
BEGIN
DECLARE ps_sqlwhere VARCHAR(200) default ' ';
DECLARE ps_order VARCHAR(50);
SET ps_sqlwhere = concat(' where rq between "', ps_ksrq, '" and "', ps_jsrq,'"'); CALL splitString(ps_gybh, "-");
CASE ps_pxfs
WHEN '3' THEN
SET ps_order = ' order by rq ASC ,dgytjid ASC';
WHEN '2' THEN
SET ps_order = ' order by ywl DESC ,dgytjid ASC ';
ELSE
SET ps_order = ' order by pjfwsj ASC ,dgytjid ASC ';
END CASE;
IF (ps_jgm != 'all') THEN
SET ps_sqlwhere = concat(ps_sqlwhere, ' and jgm = ', ps_jgm);
END IF;
IF (ps_ywbh != 'all') THEN
SET ps_sqlwhere = concat(ps_sqlwhere, ' and ywbh = ', ps_ywbh);
END IF; IF (ps_gybh != 'all') THEN
SET ps_sqlwhere = concat(ps_sqlwhere, ' and gybh in (select * from tmp_split) ');
END IF;
SET @ps_sqltotal = concat('select count(*) into @total from jx_dgy_tj ', ps_sqlwhere);
SET @ps_sqltext = concat('select dgytjid, jgm, gybh, gyxm, ywbh, ywl, zgpf, zdpf, pjpf, zdfwsj, zcfwsj, pjfwsj, fwsjpm, ywlpm, rq, xq, wdmc,@total as total from jx_dgy_tj ', ps_sqlwhere, ps_order, ' limit ', '0,10');
PREPARE stmt FROM @ps_sqltotal;
execute stmt;
PREPARE stmt2 FROM @ps_sqltext;
execute stmt2;
END