56,687
社区成员
发帖
与我相关
我的任务
分享
DELIMITER //
drop procedure if exists P_Level2//
CREATE PROCEDURE P_Level2()
begin
declare l_user_name varchar(50);
declare l_mobile_id varchar(255);
declare l_mobile varchar(25);
declare l_cur cursor for
select * from A;
open l_cur;
fetch l_cur into l_user_name,l_mobile_id;
while (l_fetion_id is not null) do
--下面的报错,目标是循环获取手机号,代码不知怎么写
begin
set @v_mobile="";
declare l_cur2 CURSOR for
select mobile from B
where id in (l_mobile_id);
open l_cur2;
fetch l_cur2 into l_mobile;
while (l_mobile is not null) do
@v_mobile=concat(@v_mobile,l_mobile,',')
fetch l_cur2 into l_mobile;
end while;
close l_cur2;
end;
--下面的代码的换行符输后,在第一行显示“\”,试了很多方法也不行。
set @sqlstmt=concat('select "',@v_mobile,',\n欢迎你"
into outfile "',l_user_name,"_",DATE_FORMAT(now(),'%Y%m%d'),'.txt"');
prepare stmt from @sqlstmt;
execute stmt;
deallocate prepare stmt;
fetch l_cur into l_user_name,l_fetion_id;
end while;
close l_cur;
end;//
mysql> select * from a;
+------+------+
| name | id |
+------+------+
| 张三 | 1 |
| 李四 | 2,3 |
+------+------+
2 rows in set (0.00 sec)
mysql> select * from b;
+----+--------+
| id | mobile |
+----+--------+
| 1 | 123 |
| 2 | 213 |
| 3 | 321 |
+----+--------+
3 rows in set (0.00 sec)
mysql> select a.name,group_concat(b.mobile)
-> from a,b
-> where find_in_set(b.id,a.id)
-> group by a.name;
+------+------------------------+
| name | group_concat(b.mobile) |
+------+------------------------+
| 李四 | 213,321 |
| 张三 | 123 |
+------+------------------------+
2 rows in set (0.05 sec)
mysql> delimiter //
mysql> Create Procedure p_bluelive()
-> BEGIN
-> DECLARE done INT DEFAULT 0;
-> DECLARE v_1 VARCHAR(10);
-> DECLARE v_2 VARCHAR(10);
-> DECLARE cur1 CURSOR FOR select a.name,group_concat(b.mobile)
-> from a,b
-> where find_in_set(b.id,a.id)
-> group by a.name;
-> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
->
-> OPEN cur1;
-> FETCH cur1 INTO v_1,v_2;
-> WHILE done=0 DO
-> set @sqlstmt=concat('select \'',v_2,',\' union all select \'欢迎您!\' into outfile \'',v_1,'.txt\'');
-> PREPARE stmt1 FROM @sqlstmt;
-> EXECUTE stmt1 ;
-> DEALLOCATE PREPARE stmt1;
-> FETCH cur1 INTO v_1,v_2;
-> END WHILE;
-> END
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call p_bluelive();
Query OK, 0 rows affected (0.02 sec)
mysql>