Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "how to use cursor nesting in MYSQL stored procedure development". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

In the development of actual business logic, it is inevitable to use cursor nesting, as an example:

Delimiter / / drop procedure if exists good_nested_cursors1//CREATE PROCEDURE good_nested_cursors1 () READS SQL DATABEGIN DECLARE l_grade_id INT; DECLARE l_class_id INT; DECLARE l_class_cnt INT DEFAULT 0; DECLARE l_done INT DEFAULT 0; www.2cto.com DECLARE grade_csr cursor FOR SELECT grade_id FROM org_grade; DECLARE class_csr cursor FOR SELECT class_id FROM org_class WHERE grade_id=l_grade_id DECLARE CONTINUE HANDLER FOR NOT FOUND SET lounge classrooms 1; OPEN grade_csr; grade_loop: LOOP-- Loop through org_grade FETCH grade_csr into graded gradations; select concat ('Grade:', l_grade_id); IF l_done=1 THEN LEAVE grade_loop; END IF; OPEN class_csr; SET classrooms classrooms cnt0; class_loop: LOOP-- Loop through class in grade. FETCH class_csr INTO classrooms; IF l_done=1 THEN LEAVE class_loop; END IF; SET classrooms; select concat ('classes:', l_class_id); END LOOP; CLOSE class_csr; SET classes classrooms 0; www.2cto.com END LOOP grade_loop; CLOSE grade_csr; END;// delimiter / another example: CREATE PROCEDURE curdemo () NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT''BEGIN declare done1,done2 int default 0; declare name1,name2 varchar (20); declare id1,id2 int; www.2cto.com declare cur1 cursor for select id,name from test1; declare continue handler for not found set done1 = 1 Open cur1; repeat fetch cur1 into id1, name1; if not done1 then insert into test3 (name) values (name1); begin declare cur2 cursor for select id,name from test2; declare continue handler for not found set done2= 1; open cur2; repeat fetch cur2 into id2,name2; if not done2 then insert into test3 (name) values (name2); end if; www.2cto.com until done2 end repeat; close cur2; set done2=0; end; end if; until done1 end repeat; close cur1; commit; END This is the end of the introduction to "how to use cursor nesting in MYSQL stored procedure development". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.