A stored procedure in which MySQL deletes operations using cursors

BEGIN
DECLARE Hprocessinstanceid bigint DEFAULT 0; --Historical Process instance ID
DECLARE hprocessinstanceidstarttime CHAR Default '; --Historical process instance start time
DECLARE hprocessinstanceidendtime CHAR Default '; --History Process instance end time

DECLARE Hactinstid BIGINT default 0; --Historical Activity Instance ID

DECLARE htaskid BIGINT default 0; --Historical human task ID

DECLARE Hvarid BIGINT default 0; --Historical process variable ID

DECLARE rexecutionid bigint default 0; --Executing process instance ID

DECLARE rvarid bigint default 0; --process variable ID being executed

DECLARE rtaskid bigint default 0; --The human task ID is being executed

DECLARE Rswinmlanceid bigint DEFAULT 0; --Lane ID, in order to delete partation table records, this item is not recorded

DECLARE Processcompleteflag int default 0; --whether the process ends the identification
DECLARE Taskcompleteflag int default 0; --whether the task ends the identity
DECLARE Doneflag INT DEFAULT 0; --Completion identification, 0: not completed; 1: Completed
DECLARE NotFound INT DEFAULT 0;--If no data marker found
--Start things
--START TRANSACTION;

/* Cursors declaring historical process instances */
DECLARE hprocessinstancers CURSOR for SELECT dbid_,start_,end_ from Jbpm4_hist_procinst where start_>= ' 2014-0-0 0:0:0 ' and start_< ' 2015-0-0 0:0:0 ';

/* Cursors that declare historical activity instances */
DECLARE hactinstrs CURSOR for SELECT dbid_,htask_ from Jbpm4_hist_actinst where Hproci_=hprocessinstanceid;

/* Cursors that declare historical activity instances */
DECLARE htaskrs CURSOR for SELECT dbid_ from Jbpm4_hist_task where Dbid_=hactinstid;

/* Cursors that declare historical activity instances */
DECLARE hvarrs CURSOR for SELECT dbid_ from Jbpm4_hist_var where Htask_=rtaskid;

/** declares a cursor that is executing a process instance (the history table is not completed for various reasons) **/
DECLARE Rexecutionrs CURSOR for SELECT dbid_ from Jbpm4_execution where Dbid_=hprocessinstanceid;

/** declares a cursor that is executing a process variable (only the process variables corresponding to the process instances that were not completed for various reasons in the 2014 condition) */
DECLARE rvarrs CURSOR for SELECT dbid_ from jbpm4_variable where Execution_=hprocessinstanceid;

/** declares a cursor for the human task being executed (only for the process instance that started in 2014) **/
DECLARE rtaskrs CURSOR for SELECT dbid_ from Jbpm4_task where Dbid_=rtaskid;

/** declares a result set cursor for a swimlane, in order to delete the Paritation table, the item is not logged, and the actual number of deleted bars is 0 **/
DECLARE rswinmlancers CURSOR for SELECT dbid_ from Jbpm4_swimlane where Dbid_=rswinmlanceid;

/* Exception Handling */
DECLARE CONTINUE HANDLER for SQLSTATE ' 02000 ' SET doneflag = 1;

/** Delete s, use nested loops ... **/

OPEN hprocessinstancers;
FETCH hprocessinstancers into hprocessinstanceid,hprocessinstanceidstarttime,hprocessinstanceidendtime;-- Get data data for the history process instance table

REPEAT
IF Hprocessinstanceidendtime= ' Then
--no end, execution delete the executing process instance table
/** 1. Querying the process instance record being executed s **/
SET Rexecutionid=hprocessinstanceid; --The unfinished process instance corresponds to the process instance ID being executed
OPEN Rexecutionrs;
FETCH Rexecutionrs into Rexecutionid;
REPEAT
/** 2. Query all executing process variable records under the process instance s 2**/
OPEN Hvarrs;
FETCH Hvarrs into Rvarid;
REPEAT
/** 3. Delete the human task record corresponding to the process variable being executed s 3**/
Delete from Jbpm4_task where Dbid_=rvarid;
/** 3. Delete the human task record for the executing process variable e 3**/
Delete from jbpm4_variable where Dbid_=rvarid; --Single Delete process variable record
FETCH Hvarrs into Rvarid;
UNTIL Doneflag END REPEAT;
CLOSE Hvarrs;
/** 2. Query all executing process variable records under the process instance e 2**/
Delete from jbpm4_execution where Dbid_=rexecutionid; --Single Delete process object record
FETCH Rexecutionrs into Rexecutionid;
UNTIL Doneflag END REPEAT;
CLOSE Rexecutionrs;

END IF;
/*** ====== Delete history flow record table related data ===== **/
/** 1. Query activity Instance Table s **/
OPEN Hactinstrs;
FETCH hactinstrs into Hactinstid,htaskid;
REPEAT
/** 2. Query history manual Activity table records s **/
OPEN Htaskrs;
FETCH Htaskrs into Htaskid;
REPEAT
/** 3. Delete Historical human tasks **/
Delete from Jbpm4_hist_task where dbid_=htaskid;
FETCH Htaskrs into Htaskid;
UNTIL Doneflag END REPEAT;
CLOSE Htaskrs;
/** 2. Query history manual Activity table records s **/
FETCH hactinstrs into Hactinstid,htaskid;
UNTIL Doneflag END REPEAT;
CLOSE Hactinstrs;

/** 1. Query Activity Instance Table E **/
/*** ====== Delete history flow record table related data ===== **/
/** Delete A History activity instance table **/
Delete from Jbpm4_hist_actinst where Hproci_=hprocessinstanceid;

SET doneflag=0;
FETCH hprocessinstancers into hprocessinstanceid,hprocessinstanceidstarttime,hprocessinstanceidendtime;-- Get data data for the history process instance table
UNTIL Doneflag END REPEAT;
CLOSE hprocessinstancers;
END

After using nesting, 100,000-millions data is deleted very slowly, what workaround is there?

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

A stored procedure in which MySQL deletes operations using cursors