Storage process batch generation method and system based on EXCEL

Abstract

The invention discloses a storage process batch generation method and system based on EXCEL. The generation method comprises the following steps: maintaining metadata used for generating a storage process; generating a plurality of storage processes in batches according to the maintained metadata information, and selectively generating one or more storage process files; checking whether the generated storage process follows specifications corresponding to specified systems, databases and table types or not; the storage tool generates statistical information in the storage process. And the storage process is generated in batches, so that the daily workload is reduced, the development efficiency and accuracy are improved, and formation of a unified development specification is facilitated.

Description

Technical Field

[0001] The present invention relates to the field of data development, and in particular to a method and system for batch generation based on EXCEL stored procedures. Background Art

[0002] In the current data development process of various industries, ETL programs often need to be frequently written, such as loading data according to specified requirements. The database storage process encapsulates a series of SQL languages, which can process data indicators according to requirements. However, the storage process itself needs to be manually written according to requirements. Many data loading processes are repeated, and manual writing will waste a lot of manpower and material resources. In addition, the project duration is often short and cannot meet the needs of agile development.

[0003] The EXCEL stored procedure generation tool can generate executable stored procedures in a unified and standardized manner according to the specified template. Based on this technical background, batch generation of stored procedures can be achieved, which greatly reduces labor costs and avoids most of the quality problems of deliverables caused by human errors.

[0004] A stored procedure is a set of SQL statements in a database that are compiled and stored in the database to complete a specific function. Users can execute the stored procedure by specifying its name and giving parameters (if the stored procedure has parameters). They can perform complex query operations and retrieve specific data from one or more tables. For example, they can filter data based on multiple conditions, perform associated queries, use aggregate functions, etc. Encapsulating complex business logic in a stored procedure allows the application to not need to understand the specific implementation details. For example, in an order processing system, operations on multiple tables may be involved, such as checking inventory, updating order status, generating shipping notices, etc. These operations can be encapsulated in a stored procedure. At present, when generating stored procedures, system developers need to manually edit the stored procedures according to the requirements each time, which cannot efficiently complete the development of tasks with tight time cycles. In addition, manually edited stored procedures are prone to quality problems of deliverables due to human errors, resulting in rework or even production problems, which consumes a lot of time and energy. Based on the above problems, a method and tool for batch generating stored procedures based on EXCEL are proposed to solve these problems.

[0005] At present, when generating stored procedures, various systems generally rely on manual editing of stored procedures. The existing technical disadvantages of this method are as follows:

[0006] 1) Manually editing stored procedures is inefficient and cannot develop multiple tasks at the same time, which easily leads to a backlog of development tasks;

[0007] 2) Manual editing of the storage process is not easy to ensure the quality of the deliverables, which is prone to rework and even production problems;

[0008] 3) Manually editing stored procedures is prone to follow personal development habits and is not easy to form unified development specifications. Summary of the invention

[0009] In view of the above problems, the present invention is proposed to provide a method and system for batch generation of EXCEL stored procedures that overcome the above problems or at least partially solve the above problems.

[0010] According to one aspect of the present invention, a method for batch generation based on EXCEL stored procedures is provided, the generation method comprising:

[0011] Maintain the metadata used to generate stored procedures;

[0012] Generate multiple stored procedures in batches according to the maintained metadata information, and select to generate one or more stored procedure files;

[0013] Verify whether the generated stored procedures comply with the specifications corresponding to the specified system, database, and table type;

[0014] The storage tool generates statistics in the stored procedure.

[0015] Optionally, the metadata includes: system platform information, database information, table information and field information.

[0016] Optionally, the statistical information specifically includes: start time, end time and whether it is successful.

[0017] Optionally, the metadata used in the maintenance generation storage process specifically includes:

[0018] According to actual needs, determine the number of tables to be used and the number, name, type, length and order of fields corresponding to each table, and maintain the system name, table English name, table Chinese name, field English name, field Chinese name, field order, field type, field length, and whether it is a primary key by row in the configuration table;

[0019] Maintain table types based on table characteristics;

[0020] The creation time and the last modification time are the same during the initial creation. In subsequent iterations, only the last modification time needs to be modified.

[0021] Optionally, the batch generation of multiple stored procedures according to the maintained metadata information and the selection of the generation of one or more stored procedure files specifically include:

[0022] Read the information configuration table content in the EXCEL configuration page, traverse and parse the tables to be generated in order, and after parsing, generate the stored procedure in the same directory as the tool.

[0023] Optionally, the step of traversing and parsing the table to be generated in order specifically includes:

[0024] Generate the name of the stored procedure based on the English name of the table, and confirm the number of rows corresponding to each table in the information configuration table, which is the total number of fields in each table;

[0025] Determine the logic of the generated stored procedure based on the table type, and generate the specified processing SQL based on the order, type and length of the fields;

[0026] According to the system name, select the stored procedure template of the system, fill the generated logic into the template, complete the assembly of the stored procedure, and generate the complete stored procedure in the same directory as the tool. If the generation fails, it will be returned and prompted to check whether the content in the information configuration table is correct.

[0027] Optionally, the verification of whether the generated stored procedure complies with the specifications corresponding to the specified system, database, and table type specifically includes:

[0028] Maintain the development specifications that have been formed in the current system in the verification logic, and perform secondary specification verification on the generated stored procedures;

[0029] During the verification, the system name will be used to select the existing development specifications of the current system that have been configured, and the layout of SQL statements and stored procedures will be verified. Any inconsistencies with the specifications will be reported and prompted to be modified.

[0030] During the entire generation process, the execution records of each step are stored.

[0031] Optionally, the statistical information generated by the storage tool during the storage process specifically includes:

[0032] Import the generated stored procedure into the corresponding database, execute the test case according to the data situation, and verify the correctness of the logic;

[0033] According to the database used by each system, create the source table and target table for processing after logging in, and after preparing sufficient test data, import the generated stored procedure into the database, execute the stored procedure, check whether the execution is normal, and whether the data is successfully written into the target table;

[0034] Prepare the expected result data according to the test data, and check it with the data in the target table to confirm whether they are completely consistent. If they are consistent, complete the verification. If they are inconsistent, start troubleshooting the information configuration table again until the final result is completely consistent with the expected result.

[0035] The present invention also provides a batch generation system based on EXCEL stored procedures, applying the above-mentioned batch generation method based on EXCEL stored procedures, and the generation system includes:

[0036] The EXCEL tool metadata maintenance module is used to maintain the metadata used to generate the stored procedure;

[0037] The batch generation stored procedure function module is used to batch generate multiple stored procedures according to the maintained metadata information, and select to generate one or more stored procedure files;

[0038] The stored procedure specification verification module is used to verify whether the generated stored procedure complies with the specifications corresponding to the specified system, database, and table type;

[0039] The log storage module is used to store the statistical information generated by the tool during the storage process.

[0040] The present invention provides a method and system for batch generation of stored procedures based on EXCEL, the generation method comprising: maintaining metadata used in generating stored procedures; batch generating multiple stored procedures according to the maintained metadata information, and selecting to generate one or more stored procedure files; verifying whether the generated stored procedures comply with the specifications corresponding to the specified system, database, and table type; and storing the statistical information generated by the tool in the stored procedures. Batch generation of stored procedures reduces daily workload, improves development efficiency and accuracy, and helps to form unified development specifications.

[0041] The above description is only an overview of the technical solution of the present invention. In order to more clearly understand the technical means of the present invention, it can be implemented according to the contents of the specification. In order to make the above and other purposes, features and advantages of the present invention more obvious and easy to understand, the specific implementation methods of the present invention are listed below. BRIEF DESCRIPTION OF THE DRAWINGS

[0042] In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the accompanying drawings required for use in the description of the embodiments will be briefly introduced below. Obviously, the accompanying drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other accompanying drawings can be obtained based on these accompanying drawings without paying creative work.

[0043] Figure 1A flow chart of a method for batch generation based on EXCEL stored procedures provided in an embodiment of the present invention;

[0044] Figure 2 A block diagram of a system for batch generation based on EXCEL stored procedures provided in an embodiment of the present invention. DETAILED DESCRIPTION

[0045] The exemplary embodiments of the present disclosure will be described in more detail below with reference to the accompanying drawings. Although the exemplary embodiments of the present disclosure are shown in the accompanying drawings, it should be understood that the present disclosure can be implemented in various forms and should not be limited by the embodiments set forth herein. On the contrary, these embodiments are provided to enable a more thorough understanding of the present disclosure and to fully convey the scope of the present disclosure to those skilled in the art.

[0046] The terms "comprises" and "having" and any variations thereof in the description embodiments, claims and drawings of the present invention are intended to cover non-exclusive inclusions, for example, including a series of steps or units.

[0047] The technical solution of the present invention is further described in detail below in conjunction with the accompanying drawings and embodiments.

[0048] like Figure 1 As shown, a batch generation method based on EXCEL stored procedure includes:

[0049] Confirm the number, name and execution logic of stored procedures in the required scope, and maintain them in the EXCEL configuration page. The header information of the information configuration table in the document includes: system name, table English name, table Chinese name, field English name, field Chinese name, field order, field type, field length, whether it is a primary key, table type, creation time, and last modification time. The specific steps are: According to actual needs, the number of tables required and the number, name, type, length and order of fields corresponding to each table will be determined. The system name, table English name, table Chinese name, field English name, field Chinese name, field order, field type, field length, and whether it is a primary key are maintained row by row in the configuration table. Then maintain the table type according to the characteristics of the table (such as snapshot table, flow table, zipper table, etc.). The creation time and the last modification time are the same when it is first created. In subsequent iterations, you only need to modify the last modification time;

[0050] The batch generation stored procedure function module reads the information configuration table content in the EXCEL configuration page, and traverses and parses the tables that need to be generated in order. After the parsing is completed, the stored procedure is generated in the same directory as the tool. The parsing method is: first, generate the name of the stored procedure based on the English name of the table, and confirm the number of rows corresponding to each table in the information configuration table, that is, the total number of fields in each table, and then determine the generated stored procedure logic based on the table type (such as snapshot table, flow table, zipper table, etc.), and then generate the specified processing SQL based on the order, type and length of the fields. Finally, according to the system name, select the stored procedure template of the system, fill the generated logic into the template, complete the assembly of the stored procedure, and generate the complete stored procedure in the same directory as the tool. If the generation fails, it will be refunded and prompted to check whether the content in the information configuration table is correct;

[0051] The stored procedure specification verification module maintains the development specifications that have been formed in the current system in the verification logic, and then performs a secondary specification verification on the generated stored procedures to ensure that the generated stored procedures can accurately follow the development specifications. During the verification, the system name will be used to select the existing development specifications that have been configured for the current system, and the SQL statements (syntax, association conditions, etc.) and the layout of the stored procedures (source table name, target table name, stored procedure name, maintainer, maintenance date, maintenance version number, line breaks, etc.) will be verified. Any inconsistencies with the specifications will be reported and prompted to be modified;

[0052] During the entire generation process, the execution records of each step are stored in the log storage module to facilitate historical problem tracing and version information retention;

[0053] Import the generated stored procedure into the corresponding database, execute the test case according to the data situation, and verify the correctness of the logic. According to the database used by each system, create the source table and target table for processing after logging in, and prepare sufficient test data, then import the generated stored procedure into the database, and then execute the stored procedure to check whether the execution is normal and whether the data is successfully written into the target table. At the same time, prepare the expected result data in advance according to the test data, and check it with the data in the target table to confirm whether it is completely consistent. If it is consistent, complete the verification. If it is inconsistent, start troubleshooting the information configuration table again until the final result is completely consistent with the expected result.

[0054] like Figure 2 As shown, a batch generation system based on EXCEL stored procedure includes:

[0055] EXCEL tool metadata maintenance module, batch generation stored procedure function module, stored procedure specification verification module, log storage module;

[0056] Metadata maintenance module: used to maintain the metadata used to generate the stored procedure, including but not limited to system platform information, database information, table information, and field information;

[0057] Batch generation of stored procedures function module: used to batch generate multiple stored procedures according to the maintained metadata information, system, database, and table type information, and select to generate one or more stored procedure files;

[0058] Stored procedure specification verification module: used to verify whether the generated stored procedure complies with the specifications corresponding to the specified system, database, and table type;

[0059] Log storage module: used to store statistical information generated by the tool during the storage process, such as start time, end time, and whether it is successful.

[0060] By using EXCEL to batch generate stored procedures, the following functions can be achieved:

[0061] 1) Batch generate stored procedures. Based on the quick generation template, the tool can quickly generate template code for stored procedures based on the database structure and requirements provided by the user. Developers only need to make a few modifications and adjustments on this basis to obtain stored procedures that meet specific functions, which greatly saves development time;

[0062] 2) The tool runs automatically, reducing the errors that may occur in the manual code writing process, such as grammatical errors, spelling errors, etc. The tool usually follows best practices and database specifications for code generation, improving the quality and reliability of the code;

[0063] 3) The deliverables are all generated according to a unified template with a unified code style. The stored procedures generated by the tool usually have a consistent code style and structure, which makes the code easier to read and understand. For a team development environment, a unified code style helps improve collaboration efficiency and reduce the cost of understanding and maintenance caused by differences in code style. For example, all generated stored procedures may use the same naming conventions, indentation formats, and comment specifications, allowing developers to become familiar with and maintain the code more quickly. At the same time, it is easy to modify and expand. When the database structure or business requirements change, the tool can relatively easily regenerate the stored procedure code or provide corresponding modification functions. This enables stored procedures to better adapt to changes and reduces the difficulty of maintenance. For example, if a new field is added to a database table, the tool can automatically update the relevant stored procedures to ensure that the new field can be processed correctly.

[0064] Beneficial effects: The present invention helps users quickly generate executable stored procedures, saves development time, reduces errors that may occur in the process of manually writing codes, and makes stored procedures have a consistent code style and structure, which is easier to read and understand, helps improve collaboration efficiency, and reduces the understanding and maintenance costs caused by differences in code styles. At the same time, it is easy to modify and expand, reducing the difficulty of maintenance.

[0065] Use EXCEL generation tools to maintain metadata information according to specified templates, generate stored procedures in batches, reduce daily workload, improve development efficiency and accuracy, and help form unified development specifications.

[0066] The above specific implementation methods further illustrate the objectives, technical solutions and beneficial effects of the present invention in detail. It should be understood that the above are only specific implementation methods of the present invention and are not intended to limit the scope of protection of the present invention. Any modifications, equivalent substitutions, improvements, etc. made within the spirit and principles of the present invention should be included in the scope of protection of the present invention.

Claims

1. A method for batch generation based on EXCEL stored procedure, characterized in that: The generation method comprises: Maintain the metadata used to generate stored procedures; Generate multiple stored procedures in batches according to the maintained metadata information, and select to generate one or more stored procedure files; Verify whether the generated stored procedures comply with the specifications corresponding to the specified system, database, and table type; The storage tool generates statistics in the stored procedure.

2. A method for batch generation based on EXCEL stored procedure according to claim 1, characterized in that: The metadata includes: system platform information, database information, table information and field information.

3. A method for batch generation based on EXCEL stored procedure according to claim 1, characterized in that: The statistical information specifically includes: start time, end time and whether it is successful.

4. A method for batch generation based on EXCEL stored procedure according to claim 1, characterized in that: The metadata used in the maintenance and generation storage process specifically includes: According to actual needs, determine the number of tables to be used and the number, name, type, length and order of fields corresponding to each table, and maintain the system name, table English name, table Chinese name, field English name, field Chinese name, field order, field type, field length, and whether it is a primary key by row in the configuration table; Maintain table types based on table characteristics; The creation time and the last modification time are the same during the initial creation. In subsequent iterations, only the last modification time needs to be modified.

5. A method for batch generation based on EXCEL stored procedure according to claim 1, characterized in that: The batch generation of multiple stored procedures according to the maintained metadata information and the selection of one or more stored procedure files specifically include: Read the information configuration table content in the EXCEL configuration page, traverse and parse the tables to be generated in order, and after parsing, generate the stored procedure in the same directory as the tool.

6. A method for batch generation based on EXCEL stored procedure according to claim 5, characterized in that: The traversal and analysis of the tables to be generated in order specifically includes: Generate the name of the stored procedure based on the English name of the table, and confirm the number of rows corresponding to each table in the information configuration table, which is the total number of fields in each table; Determine the logic of the generated stored procedure based on the table type, and generate the specified processing SQL based on the order, type and length of the fields; According to the system name, select the stored procedure template of the system, fill the generated logic into the template, complete the assembly of the stored procedure, and generate the complete stored procedure in the same directory as the tool. If the generation fails, it will be returned and prompted to check whether the content in the information configuration table is correct.

7. The method for batch generation based on EXCEL stored procedure according to claim 1, characterized in that: The verification of whether the generated stored procedure complies with the specifications corresponding to the specified system, database, and table type specifically includes: Maintain the development specifications that have been formed in the current system in the verification logic, and perform secondary specification verification on the generated stored procedures; During the verification, the system name will be used to select the existing development specifications of the current system that have been configured, and the layout of SQL statements and stored procedures will be verified. Any inconsistencies with the specifications will be reported and prompted to be modified. During the entire generation process, the execution records of each step are stored.

8. The method for batch generation based on EXCEL stored procedure according to claim 1, characterized in that: The statistical information generated by the storage tool during the storage process specifically includes: Import the generated stored procedure into the corresponding database, execute the test case according to the data situation, and verify the correctness of the logic; According to the database used by each system, create the source table and target table for processing after logging in, and after preparing sufficient test data, import the generated stored procedure into the database, execute the stored procedure, check whether the execution is normal, and whether the data is successfully written into the target table; Prepare the expected result data according to the test data, and check it with the data in the target table to confirm whether they are completely consistent. If they are consistent, complete the verification. If they are inconsistent, start troubleshooting the information configuration table again until the final result is completely consistent with the expected result.

9. A batch generation system based on EXCEL stored procedure, applying the batch generation method based on EXCEL stored procedure described in any one of claims 1 to 8, the generation system comprising: The EXCEL tool metadata maintenance module is used to maintain the metadata used to generate the stored procedure; The batch generation stored procedure function module is used to batch generate multiple stored procedures according to the maintained metadata information, and select to generate one or more stored procedure files; The stored procedure specification verification module is used to verify whether the generated stored procedure complies with the specifications corresponding to the specified system, database, and table type; The log storage module is used to store the statistical information generated by the tool during the storage process.

Images