這些日子做一個項目，需要群發訊息功能，由於群發的使用者ID值不能確定而又需要使用預存程序實現，而MYSQL5.0不支援數組，研究了半天終於搞定了
Code
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,
10
11 param_SendTime datetime,
12
13 param_ReceiveTime datetime,
14
15 param_ReceiverIDS varchar(1000),
16
17 param_Title varchar(60),
18
19 param_Content varchar(4000),
20
21 param_Count int)
22begin
23
24
25
26DECLARE i INT(8) DEFAULT 0;
27
28REPEAT SET i = i + 1;
29
30INSERT t_Message
31
32(
33
34 f_State,
35
36 f_Type,
37
38 f_SMS,
39
40 f_SenderID,
41
42 f_SendTime,
43
44 f_ReceiveTime,
45
46 f_ReceiverID,
47
48 f_Title,
49
50 f_Content
51
52)
53
54VALUES
55
56
57
58(
59
60 param_State,
61
62 param_Type,
63
64 param_SMS,
65
66 param_SenderID,
67
68 param_SendTime,
69
70 param_ReceiveTime,
71
72 SUBSTRING_INDEX(SUBSTRING_INDEX(param_ReceiverIDS, ',', i), ',', -1),
73
74 param_Title,
75
76 param_Content
77
78);
79
80UNTIL i >= param_Count
81
82 END REPEAT;
83
84end;
85
