使用MySQL存储过程连续插入多条记录

这些日子做一个项目，需要群发消息功能，由于群发的用户ID值不能确定而又需要使用存储过程实现，而MYSQL5.0不支持数组，研究了半天终于搞定了

 

 1-- Procedure "p_Message_MultiInsert" DDL
 2
 3CREATE PROCEDURE `p_Message_MultiInsert`(param_State smallint,
 4
 5    param_Type smallint,
 6
 7    param_SMS smallint,
 8
 9    param_SenderID int,

    param_SendTime datetime,

    param_ReceiveTime datetime,

    param_ReceiverIDS varchar(1000),

    param_Title varchar(60),

    param_Content varchar(4000),

    param_Count int)
begin



DECLARE i INT(8) DEFAULT 0;

REPEAT SET i = i + 1;

INSERT t_Message

(

    f_State,

    f_Type,

    f_SMS,

    f_SenderID,

    f_SendTime,

    f_ReceiveTime,

    f_ReceiverID,

    f_Title,

    f_Content

)

VALUES



(

    param_State,

    param_Type,

    param_SMS,

    param_SenderID,

    param_SendTime,

    param_ReceiveTime,

    SUBSTRING_INDEX(SUBSTRING_INDEX(param_ReceiverIDS, ',', i), ',', -1),

    param_Title,

    param_Content

);

UNTIL i >= param_Count

 END REPEAT;

end;