StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMysql procedure: nesting begin statements in conditional blocks
text
Body
copied!<p>I am trying to only execute a block of code if a variable exists. Here is a code snipit. Can you nest Begin...End statements in an IF block?</p> <p>I've re-designed this several times. Suggestions?</p> <pre><code>delimiter // drop trigger if exists example_trigger;// create trigger example_trigger AFTER UPDATE on some_table for each row BLOCK1: begin -- check current status DECLARE done BOOLEAN DEFAULT FALSE; -- cap check if (new.CURRENT_ALLOCATED >= new.TOTAL_ALLOWED_QTY) then SET done = TRUE; end if; -- cap check end -- o if (done != TRUE and new.O_KEY is not null and new.A_KEY is null) then OBLOCK: begin DECLARE done_o BOOLEAN DEFAULT FALSE; DECLARE pd_nbr INT; DECLARE no_more_rows BOOLEAN; DECLARE cur_pd CURSOR FOR select pd.STATUS_KEY from PROD_DEL_V pd join PROD_T p on pd.KEY_NBR = p.KEY_NBR where pd.STATUS not in ('PU', 'EX') and p.O_KEY = new.O_KEY; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done_o = TRUE; -- run updates if (done_o != TRUE) then -- open cursor OPEN cur_pd; -- loop start loop_it: LOOP FETCH cur_pd INTO pd_nbr; -- exit loop if.. if no_more_rows = TRUE THEN CLOSE cur_pd; LEAVE loop_it; end if; INSERT INTO STATUS_TABLE ( STATUS_KEY , STATUS , NOTE_TXT ) ( SELECT PD.STATUS_KEY , 'PU' AS STATUS , concat('example_trigger - MAX has been reached or exceeded [TOTAL_ALLOWED_QTY = ',new.TOTAL_ALLOWED_QTY,' and CURRENT_ALLOCATED = ', new.CURRENT_ALLOCATED, ']') AS NOTE_TXT FROM PROD_DEL_TABLE PD WHERE PD.STATUS_KEY = pd_nbr ); END LOOP loop_it; end if; -- run updates end end OBLOCK:; -- end block end if; -- o -- a if (done != TRUE and new.O_KEY is null and new.A_KEY is not null) then ABLOCK: begin DECLARE done_a BOOLEAN DEFAULT FALSE; DECLARE pd_nbr INT; DECLARE no_more_rows BOOLEAN; DECLARE cur_pd CURSOR FOR select pd.STATUS_KEY from PROD_DEL_V pd join PROD_T p on pd.KEY_NBR = p.KEY_NBR join A_O_T a on a.O_KEY = p.O_KEY where pd.STATUS not in ('PU', 'EX') and a.A_KEY = new.A_KEY; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done_a = TRUE; -- run updates if (done_a != TRUE) then -- open cursor OPEN cur_pd; -- loop start loop_it: LOOP FETCH cur_pd INTO pd_nbr; -- exit loop if.. if no_more_rows = TRUE THEN CLOSE cur_pd; LEAVE loop_it; end if; INSERT INTO STATUS_TABLE ( STATUS_KEY , STATUS , NOTE_TXT ) ( SELECT PD.STATUS_KEY , 'PU' AS STATUS , concat('example_trigger - MAX has been reached or exceeded [TOTAL_ALLOWED_QTY = ',new.TOTAL_ALLOWED_QTY,' and CURRENT_ALLOCATED = ', new.CURRENT_ALLOCATED, ']' AS NOTE_TXT FROM PROD_DEL_TABLE PD WHERE PD.STATUS_KEY = pd_nbr ); END LOOP loop_it; end if; -- run updates end end ABLOCK; -- end block end if; -- a end BLOCK1; -- end large block // delimiter ; </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload