存储过程如何做到输入可变数量的参数

  • 2018-08-14
  • 285
  • 0

方法:对一个输入字符串进行拆分,从而达到获的多个参数的目的

最近改前同事的一个存储过程,就用到了输入可变参数。从网上搜了一下,发现MySQL并不支持存储过程的可变参数。于是就想了一些方法,下面是我的案例:

CREATE DEFINER=`root`@`%` PROCEDURE `yingxiaoka_chushihua_cll_input`(IN init_card_type_name VARCHAR(20))
begin
 set @i=0;
  SELECT count(distinct a.ID) into @total FROM xm as a left join xm_md as c on a.ID=c.`xm_id` where yw_id = "1" and c.`flag`=1 ;
  
  while @i < @total do

 set @stmt1 = concat('SELECT a.id ,a.xm_name into @id, @xm_name  FROM xm AS a 
LEFT JOIN xm_md AS c ON a.ID = c.`xm_id` where yw_id = "1" AND c.`flag` = 1 GROUP BY ID ORDER BY `ID` DESC limit ', @i,',1'); 
prepare stmt from @stmt1;  
execute stmt;
-- select @id, @xm_name;

SET @card_type_name = REPLACE(init_card_type_name,',','","');
set @stmt2 = concat ('insert into wblist_fk (card_type_no,card_series,card_type_name,card_brand,card_grade,flag,type_code,id,xm_name,type_code2,sequence)
select result_id, id, parent_id, leixing, leaf, state,type_code,"',@id,'","',@xm_name,'",type_code2 ,sequence from sys_type_tm where `type_name`="卡类" and parent_id IN("',@card_type_name,'")');

prepare stmt from @stmt2;  
execute stmt;
select @stmt2;

  set @i=@i+1;
   end while;
select @i;
end

目的简单的说下:就是想做到对新增加的卡初始化,前同事做的是把所有的卡一股脑重新初始化一遍。我只是加了一个输入参数init_card_type_name,用的是IN查询。所以我就对输入的参数进行操作,输入格式是”A,B,C,D…”,对该参数用replace函数将’,’替换为'”,”‘。因此也能达到目的,可能还有其他更好的方法,可以多研究研究。

评论

还没有任何评论,你来说两句吧