mysql for_mysql for 循环

TRUNCATE `ds_group_boards`

delimiter # 定义 为一句sql的结束标志

drop procedure if exists test;

create procedure test()

begin

declare temp_id int;

declare flag int default 0;

declare s_list cursor for SELECT id FROM ds_groups WHERE parent_id=0;

# 为下面while循环建立一个退出标志，当游标遍历完后将flag的值设置为1

declare continue handler for not found set flag=1;

open s_list; # 打开游标

# 将游标中的值赋给定义好的变量，实现for循环的要点

fetch s_list into temp_id;

while flag <> 1 do

INSERT INTO ds_group_boards (board, group_id) VALUES ('vp',temp_id);

INSERT INTO ds_group_boards (board, group_id) VALUES ('task',temp_id);

INSERT INTO ds_group_boards (board, group_id) VALUES ('repor',temp_id);

fetch s_list into temp_id;

end while;

close s_list; # 关闭游标

end

delimiter ; # 重新定义;为一句sql的结束标志

call test(); # 调用

DROP PROCEDURE IF EXISTS staff_zt_test;

CREATE PROCEDURE staff_zt_test () BEGIN DECLARE flag INT DEFAULT 0; DECLARE temp_id INT; DECLARE idList CURSOR FOR (

SELECT id FROM ds_groups WHERE parent_id=0); DECLARE CONTINUE

HANDLER FOR NOT FOUND

SET flag=1; OPEN idList; FETCH idList INTO temp_id; WHILE flag !=1

DO

INSERT INTO ds_group_boards (board,group_id) VALUES ('vp',temp_id);

INSERT INTO ds_group_boards (board,group_id) VALUES ('task',temp_id);

INSERT INTO ds_group_boards (board,group_id) VALUES ('repor',temp_id); FETCH idList INTO temp_id; END WHILE; CLOSE idList; END;

CALL staff_zt_test ();

mysql> DELIMITER //

mysql> CREATE PROCEDURE staff_zt_test () BEGIN DECLARE flag INT DEFAULT 0; DECLARE temp_id INT;

DECLARE idList CURSOR FOR (

-> SELECT id FROM ds_groups WHERE parent_id=0); DECLARE CONTINUE

-> HANDLER FOR NOT FOUND

-> SET flag=1; OPEN idList; FETCH idList INTO temp_id; WHILE flag !=1

-> DO

-> INSERT INTO ds_group_boards (board,group_id) VALUES ('vp',temp_id);

-> INSERT INTO ds_group_boards (board,group_id) VALUES ('task',temp_id);

-> INSERT INTO ds_group_boards (board,group_id) VALUES ('repor',temp_id); FETCH idList INTO

temp_id; END WHILE; CLOSE idList; END;

-> //

Query OK, 0 rows affected (0.01 sec)

mysql> CALL staff_zt_test ();

-> //

Query OK, 0 rows affected (0.00 sec)

truncate table ds_group_boards;

DROP PROCEDURE IF EXISTS staff_zt_test;

CREATE PROCEDURE staff_zt_test () BEGIN DECLARE flag INT DEFAULT 0; DECLARE temp_id INT; DECLARE idList CURSOR FOR (

SELECT id FROM ds_groups WHERE parent_id=0); DECLARE CONTINUE

HANDLER FOR NOT FOUND

SET flag=1; OPEN idList; FETCH idList INTO temp_id; WHILE flag !=1

DO

INSERT INTO ds_group_boards (board,group_id) VALUES ('vp',temp_id);

INSERT INTO ds_group_boards (board,group_id) VALUES ('task',temp_id);

INSERT INTO ds_group_boards (board,group_id) VALUES ('repor',temp_id); FETCH idList INTO temp_id; END WHILE; CLOSE idList; END;