Configurable automatic data archiving method and system based on storage process

Abstract

The invention provides a configurable automatic data archiving method and system based on a storage process, and belongs to the technical field of database data life cycle management.The method comprises the following processes that firstly, an archiving task is initialized, and a configuration table archiving rule is loaded; automatically judging and creating an archiving history table; secondly, dynamically calculating an archiving reference date; archiving conditions are generated, and data archiving and source table cleaning are executed in batches; abnormities are processed through log and transaction control, and data archiving and source table cleaning are completed; and finally, task circulation and exception handling are performed, and configurable and automatic historical data management is realized. The method solves the problems that an existing data archiving scheme is high in hard coding dependency, lacks unified configuration, is high in batch processing risk, is weak in fault tolerance and traceability, and is disordered in historical table management, is suitable for batch backup and archiving of historical data and source table cleaning in a core business system, guarantees the performance of a core table, and meets the requirements of data retention compliance.

Description

TECHNICAL FIELD

[0001] The present application relates to the field of information technology, and more particularly, to a data automatic archiving method and system based on a stored procedure. BACKGROUND

[0002] In core business systems such as finance and government, as the business volume grows, the database core tables (such as transaction log tables and account detail tables) will accumulate a large amount of historical data. If not cleaned up for a long time, it will lead to high table space occupation, query performance degradation, and even affect the response speed of core business. The existing data archiving scheme has the following problems: Hard coded dependence: archiving rules (such as archiving tables, time ranges, and condition fields) need to be implemented through code hard coding. When adding or modifying archiving tasks, the program needs to be modified and redeployed, which is high in maintenance cost; Lack of unified configuration: there is no centralized configuration table to manage archiving rules, and the archiving logic of different tables is scattered, making it difficult to monitor and maintain uniformly; High risk of batch processing: direct full archiving may lead to large transactions, triggering database lock waiting or transaction timeout, and even causing core system business interruption; Weak fault tolerance and traceability: there is a lack of perfect log recording and exception handling mechanism. After archiving fails, it is difficult to locate the problem, and historical archiving records cannot be traced back; Historical table management is chaotic: manual creation of archiving historical tables is required. When the table structure is inconsistent with the source table, data loss may occur, and there is no unified archiving timestamp identifier.

[0003] To solve the above problems, a configuration, automation, and high fault tolerance data archiving scheme is needed to realize batch and controllable archiving of historical data without affecting core business. SUMMARY

[0004] The present application provides a data automatic archiving method and system based on a stored procedure to solve the technical problems in the prior art. It is suitable for batch backup, archiving, and source table cleaning of historical data in core business systems, ensuring core table performance and meeting data retention compliance requirements.

[0005] According to a first aspect of the present application, a data automatic archiving method based on a stored procedure is provided, comprising: Archiving task initialization and loading of configuration table archiving rules; Automatic judgment and creation of archiving historical tables; Dynamic calculation of archiving reference date; Generation of archiving conditions and batch execution of data archiving and source table cleaning; Handling exceptions through logs and transaction control, completing data archiving and source table cleaning; Task cycle and exception handling are performed to realize configuration and automatic history data management.

[0006] Based on the above technical solution, the application can also be improved as follows.

[0007] Optionally, the loading configuration table archiving rule comprises: Starting a storage procedure through a database automatic task; Declaring an autonomous transaction to ensure that the archiving operation is isolated from the business transaction; Reading the archiving rule in the configuration table through a cursor, including: source table name, archiving history table name, condition field type, condition field name, time dimension, and time quantity, and loading the task in sequence.

[0008] Optionally, the automatic judgment and creation of the archiving history table comprises: For each archiving task loaded, querying the user table to judge whether the history table exists; If the history table does not exist: when the condition field type is 'B', full archiving is performed, and a backup statement is executed to create an empty history table with the same structure as the source table; When it is of other types, a history table containing an archiving timestamp field is created to mark the data archiving time; If the history table already exists, the subsequent steps are entered.

[0009] Optionally, the dynamic calculation of the archiving reference date comprises: Calling a custom function to calculate the archiving cutoff date according to the configured time rule with the current date as the reference; If the time dimension is archiving by year: the current date is reduced by the time quantity multiplied by 12 months to obtain the cutoff date; If the time dimension is archiving by month: the current date is reduced by the time quantity multiplied by months to obtain the cutoff date; If the time dimension is archiving by day: the current date is reduced by the time quantity multiplied by days to obtain the cutoff date.

[0010] If the calculation process is abnormal, the function returns a default date and records an error log to ensure that the task is not interrupted.

[0011] Optionally, the generation of the archiving condition and the batch execution of data archiving and source table cleaning comprise: According to the condition field type, the condition field smaller than the archiving cutoff date is taken as the archiving condition; If the additional condition in the configuration table is not empty, the additional condition is spliced on the already spliced archiving condition; A batch processing threshold is set, a transaction is committed once per predetermined number of data backup, and the non-full archiving task needs to add the backup commit quantity of a transaction.

[0012] Optionally, the data archiving and source table cleaning through the log and transaction control processing exception includes: The dynamic SQL is executed to insert the data of the source table meeting the condition into the history table. If it is a non-full-amount archiving, the data of the source table is obtained according to the dynamically spliced backup condition and inserted into the history table. If it is a full-amount archiving, the history table is emptied first, and then the data of the source table meeting the condition is inserted.

[0013] Optionally, after the insertion is completed, the dynamic SQL is executed to delete the data of the source table that has been archived. The number of the archived data is recorded, and the latest archiving time of the archiving configuration table is updated. The transaction is committed, if the number of the deleted data is greater than a predetermined value, it is indicated that there is still unarchived data, the data archiving and source table cleaning are repeated until the data of the source table meeting the condition is all archived. If the number is less than the predetermined value, the next task is processed.

[0014] Optionally, the task loop and exception processing include: All the archiving tasks loaded by the cursor are processed in sequence until all the tasks are executed. When there is a problem in any link, the exception is captured, and the next task is continued to be processed. When all the tasks are executed, the log of “archiving task end” is recorded, and the final transaction is committed.

[0015] According to the second aspect of the present application, a data automatic archiving system based on a storage procedure is provided, including: A data acquisition module is used for archiving task initialization and loading the archiving rules of the configuration table. An automatic judgment and archiving creation module is used for automatically judging and creating an archiving history table. A dynamic calculation module is used for dynamically calculating an archiving benchmark date. A batch execution module is used for generating archiving conditions and executing data archiving and source table cleaning in batches, and processing exceptions through logs and transaction control to complete data archiving and source table cleaning. A data exception processing module is used for task loop and exception processing to realize configuration and automatic history data management.

[0016] According to the third aspect of the present application, an electronic device is provided, including a memory and a processor, the processor is used to execute a computer program stored in the memory to realize a data automatic archiving method based on a storage procedure.

[0017] The technical effects and advantages of the present application are as follows: The application provides a data automatic archiving method and system based on a storage process, which realizes automatic creation of a history table, dynamic calculation of an archiving condition, batch data archiving in batches, and source table cleaning through unified management of archiving rules in a configuration table, and guarantees the stability of the archiving process through logs and transaction control, finally reduces maintenance cost, improves core table performance, and meets data retention compliance requirements.

[0018] Other features and advantages of the present application will be set forth in the following description, and in part will become apparent to those skilled in the art from the description, or can be learned by practice of the application. The objects and other advantages of the application will be realized and attained by the structure particularly pointed out in the written description and claims thereof as well as the appended drawings. BRIEF DESCRIPTION OF DRAWINGS

[0019] Figure 1 A flowchart of the data automatic archiving method based on the storage process is provided for the embodiments of the application. DETAILED DESCRIPTION

[0020] The technical solutions in the embodiments of the application will be clearly and completely described below with reference to the drawings in the embodiments of the application. Obviously, the described embodiments are only part of the embodiments of the application, rather than all the embodiments of the application. Based on the embodiments in the application, all other embodiments obtained by those skilled in the art without creative labor fall within the protection scope of the application.

[0021] It can be understood that based on the defects in the background art, the embodiments of the application provide a data automatic archiving method based on a storage process, as shown in Figure 1 The method comprises the following steps: Step S1, archiving task initialization and loading of configuration table archiving rules; The archiving task initialization and loading of configuration table archiving rules specifically comprise the following implementation steps: 1.1. Starting a storage process through a database automatic task; 1.2. Declare autonomous transaction to ensure that archiving operation and business transaction are isolated to avoid mutual influence; 1.3. Setting a system trigger control identifier pt_pertune_status.autoflag = 'Y' to prevent the trigger of account, flow table, etc. from being executed for redundant calculation when archiving deletes source table data, thereby reducing performance consumption; 1.4. Reading archiving rules in the archiving configuration table through a cursor, including: source table name, archiving history table name, condition field type, condition field name, time dimension, and time quantity, and loading tasks in sequence according to the sequence number.

[0022] Step S2, automatic judgment and creation of an archiving history table; The automatic determination and creation of the archive history table comprises: 2.1. For each loaded archive task, query the user table to determine whether the history table exists; 2.2. If the history table does not exist: when the condition field type is 'B', it is full archive, and a backup statement is executed to create an empty history table consistent with the source table structure; When it is of other types (incremental archive), a history table containing an archive timestamp field is created to mark the data archive time; 2.3. If the history table already exists, go to step S3.

[0023] Step S3, dynamically calculate the archive reference date; The dynamic calculation of the archive reference date comprises: 3.1. Call the custom function to calculate the archive cutoff date (i.e. "data earlier than the date needs to be archived") according to the "year-month-day" of the current date and the configured time rules; If the time dimension is 'Y' (archive by year): the current date minus the time quantity multiplied by 12 months is the cutoff date; If the time dimension is 'M' (archive by month): the current date minus the time quantity multiplied by months is the cutoff date; If the time dimension is 'D' (archive by day): the current date minus the time quantity multiplied by days is the cutoff date; 3.2. If the calculation process is abnormal (such as illegal time parameters), the function returns the default date 20150323 and records an error log to ensure that the task is not interrupted.

[0024] Step S4, generate archive conditions and execute data archiving and source table cleaning in batches; The generation of archive conditions and the execution of data archiving and source table cleaning in batches comprises: 4.1. According to the condition field type, the condition field less than the archive cutoff date is taken as the archive condition; 4.2. If the additional condition in the configuration table is not empty, splice the additional condition on the already spliced archive condition; 4.3. Set the batch processing threshold, commit a transaction for every predetermined number of data backups, and add the backup commit number of a transaction for non-full archive tasks.

[0025] Step S5, handle exceptions through logs and transaction control to complete data archiving and source table cleaning; The handling of exceptions through logs and transaction control to complete data archiving and source table cleaning comprises: 5.1. Execute dynamic SQL to insert the data of the source table meeting the conditions into the history table; If it is not full-quantity archiving, source table data is obtained according to the dynamic splicing backup condition and inserted into the history table; If it is full-quantity archiving, the history table is emptied first, and then the data of the source table meeting the condition is inserted.

[0026] 5.2. After the insertion is completed, dynamic SQL is executed to delete the data of the source table that has been archived; 5.3. The number of data archived this time is recorded, and the latest archiving time of the archiving configuration table is updated; 5.4. The transaction is committed, and if the number of deleted data this time is greater than a predetermined value (≥200000, indicating that there is still unarchived data), steps 4.3-5.3 are repeated until all data of the source table meeting the condition is archived; If the number is less than the predetermined value (<200000), the next task is processed.

[0027] Step S6, task loop and exception handling are performed to realize configuration and automatic history data management.

[0028] The task loop and exception handling include: 6.1. All archiving tasks loaded by the cursor are processed in sequence until all tasks are executed; 6.2. When a problem occurs in any link, an exception is captured, and the next task is continued by skipping the current task; 6.3. When all tasks are executed, the "archiving task end" log is recorded, and the final transaction is committed.

[0029] The specific implementation is as follows: Step 1: Archiving task initialization and configuration loading; 1.1. The storage process PT_DATA_AUTO_BACKUP is started, the process name is set through the log module cbsd_log, and the "archiving task start" log is recorded, which is convenient for subsequent tracing; 1.2. PRAGMA AUTONOMOUS TRANSACTION (autonomous transaction) is declared to ensure that the archiving operation is isolated from the core business transaction to avoid mutual influence; 1.3. The system trigger control identifier pt_pertune_status.autoflag is set to 'Y' to prevent the trigger of the account, flow, etc. from being triggered when the source table data is deleted during archiving, and to reduce performance consumption; 1.4. Read the archiving rules in the configuration table PT_DATA_BACK_PARA by the cursor c_back_para, including: seq_no (execution order), tab_name (source table name), hist_tab_name (archiving history table name), data_col_type (condition field type), data_col_name (condition field name), time_type (time dimension), time_num (time quantity), and load the task in seq_no order.

[0030] Step 2: Automatic preprocessing of the archiving history table; 2.1. For each loaded archiving task, query user_tables to determine whether the hist_tab_name (history table) exists. 2.2. If the history table does not exist: If data_col_type = 'B' (full archiving), execute dynamic SQL to create an empty history table with the same structure as the source table (CREATE TABLE history table AS SELECT x FROM source table WHERE 1=2). If it is of other types (incremental archiving), create a history table containing the BACK_TIME_STAMP (archiving timestamp) field (CREATE TABLE history table AS SELECT, SYSDATE BACK_TIME_STAMP FROM source table WHERE 1=2), which is used to mark the data archiving time. 2.3. If the history table already exists, directly enter the subsequent archiving steps to avoid repeated creation.

[0031] Step 3: Dynamic calculation of archiving reference date; 3.1. Take the current date as the reference date, and calculate the archiving cutoff date according to the preset time rules. Data earlier than this date needs to be archived.

[0032] The calculation rules are as follows: If the time type (time_type) is "Y" (archiving by year), the archiving cutoff date (v_back_date) is calculated by ADD_MONTHS (v_consign_date, -12x time_num), i.e., "time_num×12 months" from the reference date. If the time type is "M" (archiving by month), it is calculated by ADD_MONTHS (v_consign_date, -time_num), i.e., "time_num months" from the reference date. If the time type is "D" (archiving by day), it is calculated by v_consign_date - time_num, i.e. "time_num days" from the reference date.

[0033] 3.2. If the archiving deadline calculation process is abnormal (e.g. time parameters are illegal), the function will automatically return the default date "20150323" and record error logs, ensuring that the archiving task is not interrupted and the overall process continues to advance.

[0034] Step 4: Archiving condition generation and batch data processing; 4.1. Generate dynamic archiving conditions (v_condition) according to field types (data_col_type), with the following specific rules: If the field type is "D" (date field), set v_condition to "condition field name < TO_DATE(TO_CHAR (v_back_date, 'YYYYMMDD'), 'YYYYMMDD')"; If the field type is "C" (character field), set v_condition to "condition field name < TO_CHAR(v_back_date, 'YYYYMMDD')"; If the field type is "R" (serial number field, and the first 8 digits of the serial number are the date), set v_condition to "condition field name < TO_CHAR (v_back_date, 'YYYYMMDD') || '00000000'"; If the field type is "S" (custom SQL condition), replace the "#YYYYMMDD#" placeholder in the condition field name with TO_CHAR (v_back_date, 'YYYYMMDD') using the REPLACE function to generate the final v_condition; If the field type is "B" (full archiving), set v_condition to "1=1" (if there is a configured custom condition, use the custom condition).

[0035] 4.2. If the uncludesql (additional condition) field in the configuration table is not empty, concatenate the additional condition with the generated v_condition, with the concatenation rule being "v_condition = v_condition ||'AND'|| uncludesql". For example, if the additional condition is "exclude data with status 'invalid'", the concatenated condition will include this filtering logic.

[0036] 4.3. Set the batch processing threshold v_once_count to 200000 (i.e. commit a transaction every 200,000 data processing), and for non-full archiving tasks, append "AND ROWNUM<= v_once_count" to the generated v_condition to avoid task timeout due to excessive data processing in a single transaction, ensuring efficient and stable archiving process.

[0037] Step 5: Data archiving and source table cleaning; 5.1. Execute dynamic SQL to insert data that meets the conditions of the source table into the history table: Non-full archiving: INSERT INTO history table (source table column name, BACK_TIME_STAMP) SELECT source table column name, SYSDATE FROM source table WHERE v_condition (source table column name is automatically obtained through the get_column_name (source table name) function to avoid hardcoding column names); Full archiving: first execute DELETE FROM history table to empty the history table, then insert data that meets the conditions of the source table (INSERT INTO history table SELECT x FROM source table WHERE v_condition); 5.2. After insertion, execute dynamic SQL to delete archived data from the source table: DELETE FROM source table WHERE v_condition; 5.3. Record the number of this archiving / deletion (SQL% ROWCOUNT), and update the configuration table PT_DATA_BACK_PARA with the current task's back_timestamp = SYSDATE (mark the latest archiving time); 5.4. Commit the transaction, and if the number of deletions this time ≥200000 (indicating there are still unarchived data), repeat steps 4.3-5.3 until all data that meets the conditions in the source table is archived; if the number <200000, proceed to the next archiving task.

[0038] Step 6: Task loop and exception handling; 6.1. Loop through all archiving tasks in the c_back_para cursor in seq_no order until all tasks are executed; 6.2. Any abnormality occurs in any link (such as SQL execution failure, table does not exist), the error is captured through EXCEPTION, SQLERRM (error information) and DBMS_UTILITY.format_error_backtrace (error stack) are recorded, the current task is skipped and the next task is continued, and the overall task is avoided to be interrupted; 6.3. After all tasks are executed, the "archiving task end" log is recorded, and the final transaction is committed.

[0039] The data automatic archiving method based on a stored procedure provided by the embodiment of the application is suitable for batch backup, archiving and source table cleaning of historical data in a core business system, guarantees the performance of core tables and meets data retention compliance requirements.

[0040] By adopting the above technical solution, the embodiment of the application has the following beneficial effects: Configuration management reduces maintenance cost: the archiving rules are centralized in the PT_DATA_BACK_PARA table, and new / modified tasks do not need to modify the stored procedure code, only need to update the configuration, and the deployment frequency is reduced; Automatic execution reduces manual intervention: by scheduling the stored procedure (such as Oracle Job) at regular intervals, periodic archiving can be realized without manual operation, and manual operation errors are avoided; Batch controllable, guarantee system stability: set 20 million data in batches, avoid transaction too large to cause lock waiting or timeout, and isolate archiving operation from core business through autonomous transaction, which does not affect the operation of the core system; Strong fault tolerance and traceability: perfect log recording (archiving start / end, SQL execution, exception information) and exception skipping mechanism facilitate problem positioning, and the BACK_TIME_STAMP field of the historical table can trace the data archiving time; Strong compatibility, wide application range: support for date, character, serial number and other condition field types, can adapt to transaction serial number, account details, business log and archiving requirements of various core tables, and automatically create a historical table to avoid inconsistent table structures.

[0041] In addition, the data automatic archiving system based on a stored procedure provided by the embodiment of the application comprises: A data acquisition module is configured to initialize an archiving task and load archiving rules of a configuration table; An automatic judgment and archiving creation module is configured to automatically judge and create an archiving historical table; A dynamic calculation module is configured to dynamically calculate an archiving reference date; The batch execution module is used for generating archiving conditions and performing data archiving and source table cleaning in batches, and processing exceptions through logs and transaction controls to complete data archiving and source table cleaning. The data exception processing module is used for task circulation and exception processing to realize configured and automated historical data management.

[0042] It can be understood that the data automatic archiving system based on the storage procedure provided by the present application corresponds to the data automatic archiving method based on the storage procedure provided by the foregoing embodiments, and the related technical features of the data automatic archiving system based on the storage procedure can refer to the related technical features of the data automatic archiving method based on the storage procedure, which will not be repeated here.

[0043] The present application shows an entity structure of an electronic device, which comprises a processor, a communications interface, a memory and a communications bus, wherein the processor, the communications interface and the memory complete mutual communication through the communications bus.

[0044] In addition, the logical instructions in the memory described above can be implemented in the form of a software functional unit and sold or used as an independent product, and can be stored in a computer readable storage medium. Based on such understanding, the technical solutions of the present application essentially or the part that contributes to the prior art or part of the technical solutions can be embodied in the form of a software product, and the computer software product is stored in a storage medium, including a plurality of instructions to make a computer device (which can be a personal computer, a server, or a network device, etc.) execute all or part of the steps of the method described in the embodiments of the present application. The foregoing storage medium includes a U disk, a mobile hard disk, a read-only memory (ROM), a random access memory (RAM), a magnetic disk or an optical disk, and various program code storage media.

[0045] Those skilled in the art can clearly understand the implementation of the various embodiments by means of software and necessary general hardware platforms through the above description of the embodiments, and of course, the embodiments can also be implemented by hardware. Based on such understanding, the above technical solutions, essentially or in other words, the part that contributes to the prior art, can be embodied in the form of a software product. The computer software product can be stored in a computer readable storage medium, such as a ROM / RAM, a magnetic disk, an optical disk, and the like, and includes a number of instructions for causing a computer device (which can be a personal computer, a server, or a network device, etc.) to execute the methods described in the various embodiments or some parts of the embodiments.

[0046] Although the preferred embodiments of the present application have been described, those skilled in the art, once they know the basic inventive concept, can make further changes and modifications to the embodiments. Therefore, the appended claims are intended to be interpreted as including the preferred embodiments and all changes and modifications falling within the scope of the present application.

[0047] Obviously, those skilled in the art can make various modifications and variations to the present application without departing from the spirit and scope of the present application. Thus, if these modifications and variations of the present application fall within the scope of the claims of the present application and their equivalent technologies, the present application also intends to include these modifications and variations.

[0048] Finally, it should be noted that: the above only for the preferred embodiments of the present application, and not for the purpose of limiting the present application, although the present application has been described in detail with reference to the foregoing embodiments, for those skilled in the art, it still can modify the technical solutions recorded in the foregoing embodiments, or make equivalent replacement to some technical features, any modification, equivalent replacement, improvement, etc. within the spirit and principles of the present application, should be included in the protection scope of the present application.

Claims

1. A method for automatic data archiving based on stored procedures, characterized in that, Includes the following steps: The archiving task is initialized, and the archiving rules in the configuration table are loaded; Automatically identify and create archived history tables; Dynamically calculate the archiving base date; Generate archiving conditions and perform data archiving and source table cleanup in batches; Exceptions are handled through logs and transaction control, and data archiving and source table cleanup are completed. Perform task loops and exception handling to achieve configurable and automated historical data management.

2. The automatic data archiving method based on a stored procedure configurable according to claim 1, characterized in that, The rules for loading configuration table archives include: Start stored procedures via database auto-tasks; Declare autonomous transactions to ensure that archiving operations are isolated from business transactions; The archiving rules in the configuration table are read using a cursor, including: source table name, archive history table name, condition field type, condition field name, time dimension, and time quantity, and the tasks are loaded in sequence.

3. The automatic data archiving method based on configurable stored procedures according to claim 1, characterized in that, The automatic determination and creation of the archived history table includes: For each archive task loaded, query the user table to determine if the history table exists; If the history table does not exist: if the condition field type is 'B', then a full archive is performed, and a backup statement is executed to create an empty history table with the same structure as the source table; For other data types, create a history table with an archive timestamp field to mark the data archive time; If the history table already exists, proceed to the next step.

4. The automatic data archiving method based on configurable stored procedures according to claim 1, characterized in that, The dynamically calculated archiving baseline date includes: Call a custom function to calculate the archiving deadline based on the current date's "year, month, and day" and according to the configured time rules; If the time dimension is archived by year: then the current date minus the number of time periods multiplied by 12 months is the cutoff date; If the time dimension is archived by month: then the current date minus the number of months is the end date; If the time dimension is archived by day: then the current date minus the number of days is the end date. If the calculation process encounters an error, the function returns a default date and logs the error to ensure the task is not interrupted.

5. The automatic data archiving method based on a stored procedure configurable according to claim 1, characterized in that, The process of generating archiving conditions and performing data archiving and source table cleanup in batches includes: Based on the condition field type, conditions that are less than the archiving deadline are used as archiving conditions; If the additional conditions in the configuration table are not empty, then append the additional conditions to the already concatenated archiving conditions. Set a batch processing threshold, commit a transaction once for each set of backup data records, and add the number of backup commits for non-full archive tasks.

6. The automatic data archiving method based on a stored procedure configurable according to claim 1, characterized in that, The process of handling exceptions through logs and transaction control, and completing data archiving and source table cleanup includes: Execute dynamic SQL to insert data from the source table that meets the conditions into the history table; If it is not a full archive, the source table data is obtained according to the dynamically assembled backup conditions and inserted into the historical table; If it is a full archive, the history table will be cleared first, and then the data that meets the conditions in the source table will be inserted.

7. The automatic data archiving method based on a stored procedure configurable according to claim 6, characterized in that, After insertion is complete, execute dynamic SQL to delete the archived data in the source table; Record the number of data archived this time, and update the latest archive time in the archive configuration table; Submit the transaction. If the number of records deleted this time is greater than the predetermined value, it means that there is still unarchived data. Duplicate data will be archived and the source table will be cleaned up until all data in the source table that meets the conditions is archived. If the number of items is less than the predetermined value, then process the next task.

8. The automatic data archiving method based on a stored procedure configurable according to claim 1, characterized in that, The process of performing task looping and exception handling includes: Process all archive tasks loaded by the cursor sequentially in a loop until all tasks are completed. If a problem occurs in any step, the exception will be captured, and the current task will be skipped to continue to the next task; Once all tasks are completed, log "Archiving task completed" and commit the final transaction.

9. A data automatic archiving system based on stored procedures, characterized in that, include: The data acquisition module is used to initialize the archiving task and load the archiving rules in the configuration table; The automatic judgment and archive creation module is used to automatically judge and create archive history tables; The dynamic calculation module is used to dynamically calculate the archiving baseline date; The batch execution module is used to generate archiving conditions and perform data archiving and source table cleanup in batches; and to handle exceptions through logs and transaction control to complete data archiving and source table cleanup. The data anomaly handling module is used for task looping and anomaly handling to achieve configurable and automated historical data management.

10. An electronic device, characterized in that, The system includes a memory and a processor, wherein the processor is used to execute a computer program stored in the memory to implement a stored procedure-based configurable data automatic archiving method as described in any one of claims 1 to 8.

Images