MySQL stored procedure example, including transactions, parameters, nested calls, cursors, loops, etc _ MySQL-mysql tutorial

MySQL stored procedure examples, including transactions, parameters, nested calls, cursors, loops, and other view plaincopy to clipboardprint?
Drop procedure if exists pro_rep_shadow_rs;
Delimiter |
----------------------------------
-- Rep_shadow_rs
-- Used to process information addition, update, and deletion
-- Only data that has not been updated since the last time is updated each time.
-- Based on different flag spaces
-- An output parameter is required,
-- If the return value is 0, the call fails and the transaction is rolled back.
-- If the return value is 1, the call is successful and the transaction is committed.
--
-- Test method
-- Call pro_rep_shadow_rs (@ rtn );
-- Select @ rtn;
----------------------------------
Create procedure pro_rep_shadow_rs (out rtn int)
Begin
-- Declare variables. all declarations must be prior to non-declared statements.
Declare iLast_rep_sync_id int default-1;
Declare iMax_rep_sync_id int default-1;
-- If an exception occurs, or automatic processing and rollback, but no longer notifies the caller
-- If you want the application to get an exception, you need to remove the following sentence and all the statements for starting and committing the transaction.
Declare exit handler for sqlexception rollback;
-- Find the last
Select eid into iLast_rep_sync_id from rep_de_proc_log where tbl = 'Rep _ shadow_rs ';
-- If not, add a row.
If iLast_rep_sync_id =-1 then
Insert into rep_de_proc_log (rid, eid, tbl) values (0, 0, 'rep _ shadow_rs ');
Set iLast_rep_sync_id = 0;
End if;

-- Next digit
Set iLast_rep_sync_id = iLast_rep_sync_id + 1;
-- Set the default return value to 0: Failed
Set rtn = 0;

-- Start the transaction
Start transaction;
-- Find the maximum number
Select max (rep_sync_id) into iMax_rep_sync_id from rep_shadow_rs;
-- New data available
If iMax_rep_sync_id> = iLast_rep_sync_id then
-- Call
Call pro_rep_shadow_rs_do (iLast_rep_sync_id, iMax_rep_sync_id );
-- Update Log
Update rep_de_proc_log set rid = iLast_rep_sync_id, eid = iMax_rep_sync_id where tbl = 'Rep _ shadow_rs ';
End if;

-- No exception occurred while running. commit the transaction.
Commit;
-- Set the return value to 1.
Set rtn = 1;
End;
|
Delimiter;
Drop procedure if exists pro_rep_shadow_rs_do;
Delimiter |
---------------------------------
-- Process data within the specified number range
-- Two parameters are required.
-- Last_rep_sync_id is the minimum value of the number.
-- Max_rep_sync_id is the maximum number.
-- No return value