MySQL Stored Procedure examples, including transactions, parameters, nested calls, cursors, loops, etc.

 Drop procedure if exists pro_rep_shadow_rs; <br/> delimiter | <br/> ---------------------------------- <br/> -- rep_shadow_rs <br/> -- added to process information, update and delete <br/> -- only data that has not been updated since the last time <br/> -- according to different flag spaces <br/> -- an output parameter is required, <br/> -- if the return value is 0, the call fails and the transaction is rolled back. <br/> -- if the return value is 1, the call is successful, transaction commit <br/> -- Test Method <br/> -- call pro_rep_shadow_rs (@ RTN); <br/> -- select @ RTN; <br/> -------------------------------- <br/> Create procedure pro_rep_shadow_rs (Out rtn int) <br/> begin <br/> -- declare a variable, all declarations must be prior to non-declared statements <br/> declare ilast_rep_sync_id int default-1; <br/> declare imax_rep_sync_id int default-1; <br/> -- if an exception occurs, or the application is automatically processed and rolled back, but the caller is no longer notified <br/> -- if you want the application to get an exception, you need to enter the following sentence, and remove all the statements for starting and committing the transaction <br/> declare exit handler for sqlexception rollback; <br/> -- query the previous <br/> select Eid into ilast_rep_sync_id from rep_de_proc_log where TBL = 'rep _ shadow_rs '; <br/> -- if it does not exist, add a row <br/> If ilast_rep_sync_id =-1 then <br/> insert into rep_de_proc_log (RID, Eid, TBL) values (, 'rep _ shadow_rs '); <br/> set ilast_rep_sync_id = 0; <br/> end if; </P> <p> -- next digit <br/> set ilast_rep_sync_id = ilast_rep_sync_id + 1; <br/> -- set the default return value to 0: Failed <br/> set RTN = 0; </P> <p> -- start transaction <br/> Start transaction; <br/> -- find the maximum number <br/> select max (rep_sync_id) into imax_rep_sync_id from rep_shadow_rs; <br/> -- new data <br/> If imax_rep_sync_id> = ilast_rep_sync_id then <br/> -- call callback (ilast_rep_sync_id, imax_rep_sync_id ); <br/> -- Update log <br/> Update rep_de_proc_log set rid = ilast_rep_sync_id, eid = imax_rep_sync_id where TBL = 'rep _ shadow_rs '; <br/> end if; </P> <p> -- no exception occurred while running. Commit the transaction <br/> commit; <br/> -- set the return value to 1 <br/> set RTN = 1; <br/> end; <br/> | <br/> delimiter; <br/> drop procedure if exists pro_rep_shadow_rs_do; <br/> delimiter | <br/> ------------------------------- <br/> -- process data within the specified number range <br/> -- two parameters are required <br/> -- last_rep_sync_id is minimum value of the serial number <br/> -- max_rep_sync_id is the maximum value of the serial number <br/> -- no return value <br/> limit <br/> Create procedure pro_rep_shadow_rs_do (last_rep_sync_id int, max_rep_sync_id INT) <br/> begin <br/> declare irep_operationtype varchar (1); <br/> declare irep_status varchar (1); <br/> declare irep_sync_id int; <br/> declare IID int; <br/> -- this is used to process the cursor reaching the last row. <br/> declare stop int default 0; <br/> -- declare cursor <br/> declare cur cursor for select ID, rep_operationtype, irep_status, rep_sync_id from rep_shadow_rs where rep_sync_id between last_rep_sync_id and max_rep_sync_id; <br/> -- declares the exception handling of the cursor and sets a termination flag. <br/> declare continue handler for sqlstate '2016' Set stop = 1; </P> <p> -- open the cursor <br/> open cur; </P> <p> -- read a row of data to the variable <br/> fetch cur into IID, irep_operationtype, irep_status, irep_sync_id; <br/> -- this is to determine whether the cursor has reached the end <br/> while stop <> 1 do <br/> -- various judgments <br/> If irep_operationtype = 'I 'Then <br/> insert into rs0811 (ID, fnbm) Select ID, fnbm from rep_shadow_rs where rep_sync_id = irep_sync_id; <br/> elseif irep_operationtype = 'U' then <br/> begin <br/> If irep_status = 'A' then <br/> insert into rs0811 (ID, fnbm) select ID, fnbm from rep_shadow_rs where rep_sync_id = irep_sync_id; <br/> elseif irep_status = 'B' then <br/> Delete from rs0811 where id = IID; <br/> end if; <br/> end; <br/> elseif irep_operationtype = 'D' then <br/> Delete from rs0811 where id = IID; <br/> end if; </P> <p> -- read data from the next row <br/> fetch cur into IID, irep_operationtype, irep_status, irep_sync_id; <br/> end while; -- loop end <br/> close cur; -- close the cursor <br/> end; <br/> |