Mysql Stored Procedure example (includes transaction, output parameter, nested call) _mysql

drop procedure if exists pro_rep_shadow_rs;
Delimiter |
----------------------------------
--Rep_shadow_rs
--Used to process information additions, updates, and deletions
--Update only data that hasn't been done since last time
--According to the different logo bit
--Requires an output parameter,
--if returned to 0, the call fails and the transaction rolls back
--if returned as 1, the call succeeds and the transaction commits
--
--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 precede statements that are not declared
declare ilast_rep_sync_id int default-1;
declare imax_rep_sync_id int default-1;

--If an exception occurs, or is automatically processed and rollback, the caller is no longer notified
--If you want the application to get an exception, you need to remove the following sentence, as well as the statements that start the transaction and commit the transaction
Declare exit handler for SqlException rollback;

--Find the last time
Select Eid into ilast_rep_sync_id from Rep_de_proc_log where tbl= ' rep_shadow_rs ';

--Add a row if it does not exist
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 Number
Set ilast_rep_sync_id=ilast_rep_sync_id+1;

--Set the default return value of 0: failed
Set rtn=0;

--Start a transaction
Start transaction;

--Find the maximum number
Select Max (rep_sync_id) into the 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;

--run without exception, commit transaction
Commit
--Set return value of 1
Set rtn=1;
End


delimiter;
drop procedure if exists pro_rep_shadow_rs_do;

Delimiter |
---------------------------------
--process data within a specified number range
--You need to enter 2 parameters
--LAST_REP_SYNC_ID is the minimum value of the number
--MAX_REP_SYNC_ID is the maximum value of the number
--No return value
---------------------------------
CREATE PROCEDURE Pro_rep_shadow_rs_do (last_rep_sync_id int, max_rep_sync_id int)
Begin
DECLARE irep_operationtype varchar (1);
DECLARE irep_status varchar (1);
declare irep_sync_id int;
declare iId int;

--this is used to handle the last line of cursor arrival
declare stop int default 0;
--Declaring a cursor
Declare cur cursor FOR select id,rep_operationtype,irep_status,rep_sync_id from Rep_shadow_rs where rep_sync_id between L ast_rep_sync_id and max_rep_sync_id;
--declares exception handling for cursors, setting a stop tag
Declare CONTINUE HANDLER for SQLSTATE ' 02000 ' SET stop=1;

--Open cursor
Open cur;

--read a row of data to a variable
Fetch cur into iid,irep_operationtype,irep_status,irep_sync_id;
--This is to determine whether the cursor has reached the final
While stop <> 1 do
--All kinds of judgments
If irep_operationtype= ' I ' then
Insert into rs0811 (ID,FNBM) Select ID,FNBM from Rep_shadow_rs where rep_sync_id=irep_sync_id;
ElseIf irep_operationtype= ' U ' then
Begin
If irep_status= ' A ' then
Insert into rs0811 (ID,FNBM) Select ID,FNBM from Rep_shadow_rs where rep_sync_id=irep_sync_id;
ElseIf irep_status= ' B ' then
Delete from rs0811 where id=iid;
End If;
End
ElseIf irep_operationtype= ' D ' then
Delete from rs0811 where id=iid;
End If;

--Read the next line of data
Fetch cur into iid,irep_operationtype,irep_status,irep_sync_id;

End while; --End of Loop
Close cur; --Close cursor
End