1.用if...then...elseif...
DELIMITER $$
DROP FUNCTION IF EXISTS `demodb`.`sf1` $$
CREATE FUNCTION `sf1`(pid VARCHAR(10)) RETURNS varchar(100)
BEGIN
DECLARE strsql varchar(100);
if pid="1" then set strsql= 'select * from tablea';
elseif pid="2" then set strsql= 'select * from tableb' ;
else set strsql= 'select * from tablec' ;
end if ;
return strsql;
END $$
DELIMITER ;
2.用case...when...then...
DELIMITER $$
DROP FUNCTION IF EXISTS `demodb`.`sf2` $$
CREATE FUNCTION `sf2`(pid VARCHAR(10)) RETURNS varchar(100)
BEGIN
DECLARE strsql varchar(100);
case pid
when "1" then set strsql= 'select * from tablea';
when "2" then set strsql= 'select * from tableb' ;
else set strsql= 'select * from tablec' ;
end case ;
return strsql;
END $$
DELIMITER ;
3.存儲過程中調用函數
DELIMITER $$
DROP PROCEDURE IF EXISTS `jmcndb`.`sp7` $$
CREATE PROCEDURE `sp7`(IN pid VARCHAR(10))
BEGIN
select sf1(pid) into @sql;
prepare stmt from @sql;
execute stmt ;
END $$
DELIMITER ;
p.s.sf1(pid)即為1的函數
4.函數的查詢
show function status;
show create function sf1;