下面的應用有使用參數
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;