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;