The following example shows nesting of compound statements.
The local variable and cursor name of the outer compound statement labeled L1 are used in the inner compound statement labeled L2.
CREATE PROCEDURE sp_sample1(IN p_name CHAR(30), INOUT p_amt INTEGER) L1: BEGIN DECLARE cvar1, v_amt INTEGER; DECLARE cursor1 CURSOR WITHOUT RETURN FOR SELECT c1 AS c_c1, c2 AS c_c2 FROM temp; L2: BEGIN DECLARE v_name CHARACTER(30); DECLARE CONTINUE HANDLER FOR SQLSTATE '42000' BEGIN OPEN cursor1; INSERT INTO temp VALUES (v_name, v_amt); END; SET vName = p_name; -- tab1 table does not exist INSERT INTO tab1 VALUES (v_name, v_amt); FETCH cursor1 INTO cvar1, p_amt; END L2; END L1;