Stored Procedure for mysql to delete objects using a cursor _ MySQL

BEGINDECLAREhprocessInstanceIdbigintDEFAULT0; -- History process instance idDECLAREhprocessInstanceIdStarttimeCHARdefault; -- History process instance start time DECLAREhproces

BEGINDECLARE hprocessInstanceId bigint DEFAULT 0; -- History process instance idDECLARE hprocessInstanceIdStarttime CHAR default ''; -- History process instance startup time DECLARE hprocessInstanceIdEndtime CHAR default ''; -- DECLARE hactinstId BIGINT default 0; -- History activity instance idDECLARE htaskId BIGINT default 0; -- history manual task idDECLARE hvarId BIGINT default 0; -- History process variable idDECLARE rexecutionId bigint default 0; -- executing process instance idDECLARE rvarId bigint default 0; -- executing process variable idDECLARE rtaskId bigint default 0; -- the manual task idDECLARE rswinmlanceId bigint DEFAULT 0; -- the swimming track id. to delete the partation table record, no DECLARE processCompleteFlag int default 0 is logged in this project; -- process end id DECLARE taskCompleteFlag int default 0; -- whether the task end id DECLARE doneFlag int default 0; -- completion ID, 0: unfinished; 1: completed DECLARE notfound int default 0; -- do not find the data tag -- start transaction;/* DECLARE the CURSOR of the historical process instance */DECLARE hprocessInstanceRS cursor for select dbid _, START _, END _ FROM jbpm4_hist_procinst where START _> = '2017-0-0 'and START _ <'2017-0-0 '; /* DECLARE the CURSOR of the historical active instance */DECLARE hactinstRS cursor for select dbid _, HTASK _ FROM jbpm4_hist_actinst where HPROCI _ = hprocessInstanceId; /* DECLARE the CURSOR of the historical active instance */DECLARE htaskRS cursor for select dbid _ FROM jbpm4_hist_task where DBID _ = hactinstId; /* DECLARE the CURSOR of the historical active instance */DECLARE hvarRS cursor for select dbid _ FROM jbpm4_hist_var where HTASK _ = rtaskId; /** DECLARE the CURSOR of the instance that is executing the process (not completed in the history table FOR various reasons) **/DECLARE rexecutionRS cursor for select dbid _ FROM jbpm4_execution where DBID _ = hprocessInstanceId; /** declare the cursor of the variable being executed (only delete the process variable corresponding to the process instance that has not been completed for various reasons under the 2014 condition) */DECLARE rvarRS cursor for select dbid _ FROM jbpm4_variable where EXECUTION _ = hprocessInstanceId;/** DECLARE the CURSOR of the manual task being executed (only corresponding to the process instance starting January 1, 2014) **/DECLARE rtaskRS cursor for select dbid _ FROM jbpm4_task where DBID _ = rtaskId;/** declares the result set CURSOR of the swimming track. in order to delete the paritation table, no records are recorded FOR this project, the actual number of deleted entries is 0 **/DECLARE rswinmlanceRS cursor for select dbid _ FROM jbpm4_lane where dbid _ = rswinmlanceId; /* Exception handling */declare continue handler for sqlstate '000000' SET doneFlag = 1;/** delete s and use nested loops ..... **/OPEN hprocessInstanceRS; FETCH hprocessInstanceRS INTO hprocessInstanceId, hprocessInstanceIdStarttime, timeout; -- get the REPEATIF token = ''THEN of the historical process instance table -- no end, execute to delete the ongoing process instance table/** 1. query the records of an ongoing process instance (s) **/SET rexecutionId = hprocessInstanceId; -- the unfinished process instance corresponds to the id of the ongoing process instance (OPEN rexecutionRS); FETCH rexecutionRS INTO rexecutionId; REPEAT/** 2. query all the process variable records being executed under the process instance s 2 **/OPEN hvarRS; FETCH hvarRS INTO rvarId; REPEAT/** 3. delete the manual task record s 3 **/delete from jbpm4_task where dbid _ = rvarId;/** 3. delete the manual task record e 3 **/delete from jbpm4_variable where dbid _ = rvarId; -- delete a single process variable record FETCH hvarRS INTO rvarId; UNTIL doneFlag end repeat; CLOSE hvarRS;/** 2. query all the process variable records being executed under the process instance e 2 **/delete from jbpm4_execution where dbid _ = rexecutionId; -- a single delete process object record FETCH rexecutionRS INTO rexecutionId; UNTIL doneFlag end repeat; CLOSE rexecutionRS; end if;/***** ====== delete data related to the History process record table =======** // ** 1. query the active 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 manual 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 in s **/FETCH hactinstRS INTO hactinstId, htaskId; UNTIL doneFlag end repeat; CLOSE hactinstRS;/** 1. query the active instance Table e ** // *** ====== delete the data related to the historical process record table =====** // ** delete the historical active instance table **/delete from jbpm4_hist_actinst where HPROCI _ = hprocessInstanceId; SET doneFlag = 0; FETCH hprocessInstanceRS INTO hprocessInstanceId, hprocessInstanceIdStarttime, period; -- get the data of the historical process instance table UNTIL doneFlag end repeat; CLOSE hprocessInstanceRS; END

After nesting is used, the deletion of 0.1 million-million data records is very slow. Is there any solution?