在MYSQL需求处理中,我们会遇到需要通过SQL多层循环来处理的问题。如:A表中有5条数据,B表中有5条数据,需要实现A表中的每1条数据对应B表中的5条数据,最后就有了25条数据,从而实现一对多的关系。那如何通过循环来处理呢?
下面就将为你介绍sql中while循环语句和通过游标来实现循环的实例,供你参考,希望对您学习SQL中的循环语句能够有所帮助。
注:示例中A表相当于t_test1,B表相当于t_test2
一、WHILE循环实现-- WHILE 实现循环处理
DROP PROCEDURE if EXISTS pr_while_test;
CREATE PROCEDURE pr_while_test()
proc_start:begin
declare done int DEFAULT 0;
declare i,j,j_max,i_max int ;
DECLARE from_tel ,to_tel VARCHAR(20);
SET i=1,j=1;
SELECT max(id) into i_max from t_test1;
SELECT max(id) into j_max from t_test2;
while i<=i_max DO
SELECT user_tel into from_tel from t_test1 WHERE id=i;
WHILE j<=j_max DO
SELECT user_tel into to_tel from t_test2 where
id=j;
INSERT into t_tmp(from_tel,to_tel,update_dt)
SELECT from_tel,to_tel,NOW();
set j=j+1;
END WHILE;
SET j=1;
SET i=i+1;
end while;
end proc_start
二、游标实现过程
--采用游标来实现循环处理
DROP PROCEDURE if EXISTS pr_cursor_test;
CREATE PROCEDURE pr_cursor_test()
proc_start:begin
declare done int DEFAULT 0;
declare i ,j_max int;
DECLARE from_tel ,to_tel VARCHAR(20);
declare cur cursor for select user_tel from t_test1;
declare continue handler for not found SET
done=1;
SET i=1;
SELECT max(id) into j_max from t_test2;
open cur;
fetch cur into from_tel;
while done<> 1 do
WHILE i<=j_max DO
SELECT user_tel into to_tel from t_test2 where
id=i;
INSERT into t_tmp(from_tel,to_tel,update_dt)
SELECT from_tel,to_tel,NOW();
set i=i+1;
END WHILE;
SET i=1;
fetch cur into from_tel;
end while;
close cur;
end proc_start
三、测试过程
1、测试while循环:
TRUNCATE table t_tmp;
CALL pr_while_test();
select * from t_tmp;
2、测试游标循环:
TRUNCATE table t_tmp;
CALL pr_cursor_test();
select * from t_tmp;
注:前面已写过SQL
Server相关循环示例过程的博文,详见:sql中多层循环示例 MSSQL