56,677
社区成员
发帖
与我相关
我的任务
分享
mysql> DELIMITER $$
mysql> create procedure rr(a VARCHAR(10),b VARCHAR(500))
-> begin
-> declare i,j,k,l int;
-> declare x varchar(500);
-> declare s varchar(501);
-> declare c char;
->
-> set s=concat(b,',');
-> set l=length(s);
-> set i=1;
-> set x='';
->
-> while i<=l do
-> set c=mid(s,i,1);
-> if c=',' then
-> if trim(x)!='' then
-> insert into mobile(needCallNumber,caller) values (a,x);
-> end if;
-> set x='';
-> else
-> set x=concat(x,c);
-> end if;
-> set i=i+1;
-> END WHILE;
-> end$$
Query OK, 0 rows affected (0.19 sec)
mysql> DELIMITER ;
mysql> call rr("008","a,b,c,d");
Query OK, 1 row affected (0.07 sec)
mysql>
mysql> select * from mobile;
+----------+----------------+--------+-----------+
| MobileID | needCallNumber | caller | callstate |
+----------+----------------+--------+-----------+
| 1 | 008 | a | 0 |
| 2 | 008 | b | 0 |
| 3 | 008 | c | 0 |
| 4 | 008 | d | 0 |
+----------+----------------+--------+-----------+
4 rows in set (0.00 sec)
mysql>
CREATE PROCEDURE sp_test1(b varchar(300))
begin
set @sql = b;
set @sql=concat('insert into test1 select ',replace(b,',',' union all select '));
prepare stmt from @sql;
execute stmt;
end;