MySQL multi-revisit standard nesting

1, DECLARE CONTINUE HANDLER for not FOUND

This phrase is often seen in the stored procedure of MySQL: DECLARE CONTINUE HANDLER for not FOUND

It means: If no data is returned, the program continues, and the variable is_found is set to 0, which occurs when the select XX into XXX from TableName.

2, query each grade under what classes, a grade table (Org_grade) A class table (Org_class)

2.1 Build Table

CREATE TABLE Erp.org_grade (
grade_id Int (one) not NULL auto_increment,
Grade_name varchar (255) DEFAULT NULL,
PRIMARY KEY (grade_id)
)
ENGINE = MYISAM
Auto_increment = 2
Avg_row_length = 20
CHARACTER SET UTF8
COLLATE Utf8_general_ci; CREATE TABLE Erp.org_class (
class_id Int (one) not NULL auto_increment,
class_name varchar (255) DEFAULT NULL,
grade_id Int (one) DEFAULT NULL,
PRIMARY KEY (class_id)
)
ENGINE = MYISAM
Auto_increment = 3
Avg_row_length = 20
CHARACTER SET UTF8
COLLATE Utf8_general_ci; 2.2 Procedure Example Create PROCEDURE good_nested_cursors1 ()
READS SQL DATA
BEGIN
DECLARE l_grade_id INT;
DECLARE l_class_id INT;
DECLARE l_class_cnt INT DEFAULT 0;
DECLARE L_done INT DEFAULT 0;

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 l_done=1;
OPEN GRADE_CSR;
Grade_loop:loop--Loop through Org_grade
FETCH GRADE_CSR into l_grade_id;
Select Concat (' Grade: ', l_grade_id);
IF L_done=1 Then
LEAVE Grade_loop;
END IF;
OPEN CLASS_CSR;
SET l_class_cnt=0;
Class_loop:loop-loop through class in grade.
FETCH CLASS_CSR into l_class_id;
IF L_done=1 Then
LEAVE Class_loop;
END IF;
SET l_class_cnt=l_class_cnt+1;
Select Concat (' Class: ', l_class_id);
END LOOP Class_loop;
CLOSE CLASS_CSR;
SET l_done=0;

END LOOP Grade_loop;
CLOSE GRADE_CSR;
END;

MySQL multi-revisit standard nesting