Mysql uses a cursor to delete a stored procedure, mysql cursor
BEGIN
DECLARE hprocessInstanceId bigint DEFAULT 0; -- Historical Process instance id
DECLARE hprocessInstanceIdStarttime CHAR default ''; -- Historical Process instance Start Time
DECLARE hprocessInstanceIdEndtime CHAR default ''; -- end time of the historical process instance
DECLARE hactinstId BIGINT default 0; -- historical active 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; -- id of the instance that is executing the process
DECLARE rvarId bigint default 0; -- execution process variable id
DECLARE rtaskId bigint default 0; -- the id of the manual task being executed
DECLARE rswinmlanceId bigint DEFAULT 0; -- swimming track id. to delete the partation table record, no record exists in this project.
DECLARE processCompleteFlag int default 0; -- indicates whether the process ends.
DECLARE taskCompleteFlag int default 0; -- indicates whether the task ends.
DECLARE doneFlag int default 0; -- completion ID, 0: unfinished; 1: Completed
DECLARE notfound int default 0; -- whether the data tag is not found
-- Start things
-- 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 (History tables that are not completed for various reasons )**/
DECLARE rexecutionRS cursor for select dbid _ FROM jbpm4_execution where DBID _ = hprocessInstanceId;
/** Declare the cursor that is executing the process variable (only delete the process variable corresponding to the process instance that was not 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 ongoing manual task (only corresponding to the process instance that started on January 1, 2014 )**/
DECLARE rtaskRS cursor for select dbid _ FROM jbpm4_task where DBID _ = rtaskId;
/** Declare the result set cursor of the swimming track. to delete the paritation table, there are no records 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 '200' SET doneFlag = 1;
/** Delete s and use nested loops .....**/
OPEN hprocessInstanceRS;
FETCH hprocessInstanceRS INTO hprocessInstanceId, hprocessInstanceIdStarttime, hprocessInstanceIdEndtime; -- get the data of the historical process instance table
REPEAT
IF hprocessInstanceIdEndtime = ''THEN
-- If the process is not finished, delete the table of the ongoing process instance.
/** 1. query the records of an ongoing process instance **/
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 the records of all ongoing process variables under the process instance. s 2 **/
OPEN hvarRS;
FETCH hvarRS INTO rvarId;
REPEAT
/** 3. Delete the manual task record s corresponding to the process variable being executed **/
Delete from jbpm4_task where dbid _ = rvarId;
/** 3. Delete the manual task record corresponding to the ongoing process variable 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 variable records of the ongoing process under the process instance. e 2 **/
Delete from jbpm4_execution where dbid _ = rexecutionId; -- delete a single 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 the history manual activity table record s **/
OPEN htaskRS;
FETCH htaskRS INTO htaskId;
REPEAT
/** 3. delete a historical manual task **/
Delete from jbpm4_hist_task where dbid _ = htaskId;
FETCH htaskRS INTO htaskId;
UNTIL doneFlag end repeat;
CLOSE htaskRS;
/** 2. query the history manual activity table record s **/
FETCH hactinstRS INTO hactinstId, htaskId;
UNTIL doneFlag end repeat;
CLOSE hactinstRS;
/** 1. query the active instance Table e **/
/***** ====== Delete data related to the history process record table ===== **/
/** Delete the table of historical active instances **/
Delete from jbpm4_hist_actinst where HPROCI _ = hprocessInstanceId;
SET doneFlag = 0;
FETCH hprocessInstanceRS INTO hprocessInstanceId, hprocessInstanceIdStarttime, hprocessInstanceIdEndtime; -- 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?
Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.