Mysql 函数应用

摘自 daisylh 博客

 

 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;