Mysql uses a cursor to delete a stored procedure, mysql cursor

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.