1drop database if exists demo; 2create database demo; 3use demo; 4create procedure proc_top_a(p1 integer) 5begin 6## DECLARE CONTINUE HANDLER for SQLEXCEPTION, NOT FOUND 7begin 8end; 9select "Starting ..."; 10call proc_middle_a(p1); 11select "The end"; 12end 13$$ 14create procedure proc_middle_a(p1 integer) 15begin 16DECLARE l integer; 17# without RESIGNAL: 18# Should be: DECLARE EXIT HANDLER for SQLEXCEPTION, NOT FOUND 19DECLARE EXIT HANDLER for 1 /* not sure how to handle exceptions */ 20begin 21select "Oops ... now what ?"; 22end; 23select "In prod_middle()"; 24create temporary table t1(a integer, b integer); 25select GET_LOCK("user_mutex", 10) into l; 26insert into t1 set a = p1, b = p1; 27call proc_bottom_a(p1); 28select RELEASE_LOCK("user_mutex") into l; 29drop temporary table t1; 30end 31$$ 32create procedure proc_bottom_a(p1 integer) 33begin 34select "In proc_bottom()"; 35if (p1 = 1) then 36begin 37select "Doing something that works ..."; 38select * from t1; 39end; 40end if; 41if (p1 = 2) then 42begin 43select "Doing something that fail (simulate an error) ..."; 44drop table no_such_table; 45end; 46end if; 47if (p1 = 3) then 48begin 49select "Doing something that *SHOULD* works ..."; 50select * from t1; 51end; 52end if; 53end 54$$ 55call proc_top_a(1); 56Starting ... 57Starting ... 58In prod_middle() 59In prod_middle() 60In proc_bottom() 61In proc_bottom() 62Doing something that works ... 63Doing something that works ... 64a b 651 1 66The end 67The end 68call proc_top_a(2); 69Starting ... 70Starting ... 71In prod_middle() 72In prod_middle() 73In proc_bottom() 74In proc_bottom() 75Doing something that fail (simulate an error) ... 76Doing something that fail (simulate an error) ... 77ERROR 42S02: Unknown table 'demo.no_such_table' 78call proc_top_a(3); 79Starting ... 80Starting ... 81In prod_middle() 82In prod_middle() 83ERROR 42S01: Table 't1' already exists 84call proc_top_a(1); 85Starting ... 86Starting ... 87In prod_middle() 88In prod_middle() 89ERROR 42S01: Table 't1' already exists 90drop temporary table if exists t1; 91create procedure proc_top_b(p1 integer) 92begin 93select "Starting ..."; 94call proc_middle_b(p1); 95select "The end"; 96end 97$$ 98create procedure proc_middle_b(p1 integer) 99begin 100DECLARE l integer; 101DECLARE EXIT HANDLER for SQLEXCEPTION, NOT FOUND 102begin 103begin 104DECLARE CONTINUE HANDLER for SQLEXCEPTION, NOT FOUND 105begin 106/* Ignore errors from the cleanup code */ 107end; 108select "Doing cleanup !"; 109select RELEASE_LOCK("user_mutex") into l; 110drop temporary table t1; 111end; 112RESIGNAL; 113end; 114select "In prod_middle()"; 115create temporary table t1(a integer, b integer); 116select GET_LOCK("user_mutex", 10) into l; 117insert into t1 set a = p1, b = p1; 118call proc_bottom_b(p1); 119select RELEASE_LOCK("user_mutex") into l; 120drop temporary table t1; 121end 122$$ 123create procedure proc_bottom_b(p1 integer) 124begin 125select "In proc_bottom()"; 126if (p1 = 1) then 127begin 128select "Doing something that works ..."; 129select * from t1; 130end; 131end if; 132if (p1 = 2) then 133begin 134select "Doing something that fail (simulate an error) ..."; 135drop table no_such_table; 136end; 137end if; 138if (p1 = 3) then 139begin 140select "Doing something that *SHOULD* works ..."; 141select * from t1; 142end; 143end if; 144end 145$$ 146call proc_top_b(1); 147Starting ... 148Starting ... 149In prod_middle() 150In prod_middle() 151In proc_bottom() 152In proc_bottom() 153Doing something that works ... 154Doing something that works ... 155a b 1561 1 157The end 158The end 159call proc_top_b(2); 160Starting ... 161Starting ... 162In prod_middle() 163In prod_middle() 164In proc_bottom() 165In proc_bottom() 166Doing something that fail (simulate an error) ... 167Doing something that fail (simulate an error) ... 168Doing cleanup ! 169Doing cleanup ! 170ERROR 42S02: Unknown table 'demo.no_such_table' 171call proc_top_b(3); 172Starting ... 173Starting ... 174In prod_middle() 175In prod_middle() 176In proc_bottom() 177In proc_bottom() 178Doing something that *SHOULD* works ... 179Doing something that *SHOULD* works ... 180a b 1813 3 182The end 183The end 184call proc_top_b(1); 185Starting ... 186Starting ... 187In prod_middle() 188In prod_middle() 189In proc_bottom() 190In proc_bottom() 191Doing something that works ... 192Doing something that works ... 193a b 1941 1 195The end 196The end 197drop database demo; 198