Mysql 存储过程应用

摘自 daisylh 博客

 

下面的應用有使用參數
1.用if...then...elseif...
DELIMITER $$

DROP PROCEDURE IF EXISTS `demodb`.`sp2` $$
CREATE PROCEDURE `sp2`(in pid varchar(10))
begin
  /*declare strsql varchar(100); */
  if pid="1" then  prepare stmt from 'select * from tablea ';
  elseif pid="2" then prepare stmt from 'select * from tableb' ;
  else   prepare stmt from 'select * from tablec' ;
  end if   ;
  /*prepare stmt from   strsql; */
  execute stmt ;
 end $$

DELIMITER ;

2.用case...when...then...
上面begin...end中的可以用case改寫,如下:
BEGIN
/*DECLARE strsql varchar(100);*/
CASE  pid
when "1" then  prepare stm from "select * from tablea";
when "2" then  prepare stm from "select * from tableb";
when "3" then  prepare stm from "select * from tablec";
else  prepare stm from "select * from tabled";
END CASE;
EXECUTE stm;

3.1的簡化式:
 if pid="1" then  set @sql= 'select * from tablea ';
   elseif pid="2" then set @sql='select * from tableb' ;
     else   set @sql='select * from tablec' ;
  end if   ;
  prepare stmt from  @sql;
  execute stmt ;

 

4.2的簡化式:
CASE  pid
when "1" then  set @sql="select * from tablea";
when "2" then  set @sql="select * from tableb";
when "3" then  set @sql="select * from tablec";
else  set @sql="select * from tabled";
END CASE;
prepare stm from @sql;
EXECUTE stm;

P.S.儘管DECLARE strsql varchar(100);若將@sql換為strsql在上面好像不能通過,但在下面的句子中執行OK
DECLARE strsql varchar(100);
CASE  pid
when "1" then  set strsql="select * from tablea";
when "2" then  set strsql="select * from tableb";
when "3" then  set strsql="select * from tablec";
else  set strsql="select * from tabled";
END CASE;
select strsql;