Method and system for converting stored procedures into code implementations

Abstract

The application provides a method and system for converting a storage process into code implementation, comprising: reading original data from a database, wherein the original data comprises tables and fields of the tables used in business logic processing; processing the original data to convert SQL logic in the storage process into code to obtain result data; and storing the result data in the database. The application solves the business logic analysis process of converting the storage process into code by using a standardized storage process processing scheme, effectively reduces business relevance and workload, and completes the conversion of the whole process without understanding the business scenario.

Description

TECHNICAL FIELD

[0001] The present application relates to the field of code implementation, in particular, to a method and system for converting a stored procedure into code implementation. BACKGROUND

[0002] Due to the existence of a batch of businesses in history, the stored procedures in commercial databases such as Oracle and DB2 are developed. The development programs of this kind have the following problems: 1. The running environment of the database depends on a specific mini-computer. Some old equipment has procurement difficulties. This leads to the fact that when the database computing resources are insufficient, the database cannot be expanded. 2. The stored procedure depends on the database and the corresponding version. There are cases where the database version is old and cannot be compatible with the upgrade, and it is difficult to update other databases. 3. The early commercial database does not meet the requirements of scientific and technological innovation, and a large amount of resources need to be invested for upgrading.

[0003] Under the existing technical conditions, the main idea to solve the above problems is to write tools to migrate the stored procedure from A data source to B data source. For example, the patent document CN106339384A storage process conversion method and device discloses a method for converting the stored procedure in the closed-source database SQLServer into the stored procedure of the open-source database MySQL. However, after the underlying data storage is changed, the storage process business code cannot be smoothly transferred to the target database according to the patent document CN106339384A.

[0004] The existing technology also has a scheme of directly parsing the SQL statements in the stored procedure to generate the corresponding syntax. However, this method cannot play a role in optimizing the execution of the stored procedure, especially if there is a large table reading and writing. SUMMARY

[0005] In view of the defects in the prior art, the purpose of the present application is to provide a method and system for converting a stored procedure into code implementation.

[0006] According to the method for converting a stored procedure into code implementation provided by the present application, the method comprises the following steps:

[0007] Step S1: reading original data from a database, wherein the original data includes tables and fields of the tables used in business logic processing;

[0008] Step S2: processing the original data to convert the SQL logic in the stored procedure into code to obtain result data;

[0009] Step S3: storing the result data in the database.

[0010] Preferably, in the step S1, the original data is read into the memory at one time; in the step S2, the data of the main table in the original data is traversed, and the SQL logic in the stored procedure is converted into corresponding code to obtain the result data; and in the step S3, the result data is written back into the database row by row according to the result data of the business processing, so as to complete the business requirement.

[0011] Preferably, the step S1, the step S2 and the step S3 respectively realize data reading, data processing and data storage; in the data reading, multiple tables are read concurrently; in the data processing, the data is processed concurrently according to the data; and in the data storage, the data is written concurrently according to the data.

[0012] The data reading, the data processing and the data storage all use statements meeting the SQL standard.

[0013] Preferably, in the step S2:

[0014] For the execution process of the update of the SQL, the data meeting the condition is subjected to replacement update processing; wherein, for the operation related to the association, only the associated table is subjected to condition processing.

[0015] For the execution process of the insert of the SQL, the data meeting the condition is subjected to addition processing; wherein, the data meeting the condition includes new data or data filtered according to the associated table.

[0016] For the execution process of the delete of the SQL, the data meeting the condition is subjected to replacement deletion processing; wherein, for the operation related to the association, only the associated table is subjected to condition processing.

[0017] According to the system for converting the stored procedure into code for implementation provided by the application, comprising:

[0018] The module M1 reads original data from the database, wherein the original data includes tables and fields of the tables used in the business logic processing;

[0019] The module M2 processes the original data, converts the SQL logic in the stored procedure into code to obtain result data;

[0020] The module M3 stores the result data in the database.

[0021] Preferably, in the module M1, the original data is read into the memory at one time; in the module M2, the data of the main table in the original data is traversed, and the SQL logic in the stored procedure is converted into corresponding code to obtain the result data; and in the module M3, the result data is written back into the database row by row according to the result data of the business processing, so as to complete the business requirement.

[0022] Preferably, the module M1, the module M2 and the module M3 respectively realize data reading, data processing and data storage; in the data reading, multiple tables are read concurrently; in the data processing, data is processed concurrently; in the data storage, data is written concurrently;

[0023] The data reading, the data processing and the data storage all use statements meeting the SQL standard.

[0024] Preferably, in the module M2:

[0025] For the execution process of the update of the SQL, the data meeting the condition is subjected to replacement update processing; wherein, for the operation related to the association, only the table related to the association is subjected to condition processing;

[0026] For the execution process of the insert of the SQL, the data meeting the condition is subjected to addition processing; wherein, the data meeting the condition includes newly added data or data filtered according to the table related to the association;

[0027] For the execution process of the delete of the SQL, the data meeting the condition is subjected to replacement deletion processing; wherein, for the operation related to the association, only the table related to the association is subjected to condition processing.

[0028] According to the present application, a computer readable storage medium storing a computer program is provided, and the computer program is executed by a processor to realize the steps of the method for converting a stored procedure into code implementation.

[0029] According to the present application, an electronic device is provided, which comprises the system for converting a stored procedure into code implementation or the computer readable storage medium storing the computer program.

[0030] Compared with the prior art, the present application has the following beneficial effects:

[0031] 1. The present application solves the business logic analysis process of converting a stored procedure into code by using a standardized stored procedure processing scheme, effectively reduces the business relevance and workload, and the whole process can be converted without understanding the business scenario.

[0032] 2. Even after the underlying data storage is changed, the business code of the stored procedure can still be smoothly transferred to the target database.

[0033] 3. The present application solves the problem of slow serial business processing by using the method of serializing the whole process and parallelizing the processing logic, effectively improving the overall execution efficiency. BRIEF DESCRIPTION OF DRAWINGS

[0034] Other features, objects, and advantages of the application will become more apparent from the following detailed description when read in conjunction with the accompanying drawings:

[0035] Figure 1 The storage process processing framework in the application is shown in the figure. DETAILED DESCRIPTION

[0036] The application will be described in detail below with specific embodiments. The following embodiments will help those skilled in the art to further understand the application, but do not limit the application in any form. It should be pointed out that, for those skilled in the art, without departing from the concept of the application, a number of changes and improvements can be made. These all belong to the protection scope of the application.

[0037] The technical solution mainly proposes a fast development framework for converting a storage process into code implementation. Tables involved in business logic processing in the storage process are loaded into the memory at one time, and the multi-thread concurrent processing technology is used to quickly process the business logic. The scheme is mainly used for quickly modifying the business logic realized by the storage process in the existing system into code implementation.

[0038] Specifically, the application provides a method for converting a storage process into code implementation, comprising:

[0039] Step S1: reading original data from a database. Step S1 mainly extracts related tables used in business logic processing in the storage process and fields of the tables from the database as original data, and reads the original data into the memory at one time.

[0040] Step S2: processing the original data. The data of the main table in the original data is traversed, and the SQL logic in the storage process is converted into corresponding code to obtain result data.

[0041] Step S3: storing the result data. According to the result data of the business processing, the result data is written back into the database row by row to complete the business requirements.

[0042] From the perspective of data flow, step S1, step S2, step S3 respectively realize data reading, data processing, data storage. Step S1 is to read original data from the database; step S2 is to obtain required result data after processing the original data; step S3 is to write the result data back to the database; thus the processing logic of the whole storage process is simulated, and the related business requirements are completed. Among them, step S1 data reading, step S2 data processing, step S3 data storage are executed in sequence, and further, parallel mechanisms are used in each step; for example, in data reading, multiple tables are read concurrently; for example, in data processing, concurrent processing is performed according to data; for example, in data storage, concurrent writing operation is performed according to data.

[0043] From the perspective of development, in the case of data flow specification, step S1 is realized by code for reading original data from the database; step S2 is realized by code for simulating the execution process of SQL to complete the business logic; and step S3 is realized by code for writing result data into the database. The above-mentioned code is converted into a configuration document by the present application to simplify the development work.

[0044] In the preferred example, in the step S2:

[0045] The execution process of SQL mainly includes update, insert and delete operations. Even more complex SQL is to increase the association relationship, but it will also perform the above-mentioned three operations on a certain table (referred to as the main table). For update, the data meeting the conditions (if there is an associated operation, only the associated table is processed as a condition) is replaced and updated; for insert, the data meeting the conditions (the data can be new or filtered according to the associated table) is added; for delete, the data meeting the conditions (if there is an associated operation, only the associated table is processed as a condition) is replaced and deleted.

[0046] Through the conversion method of the present application, even after the underlying data storage is changed, the storage process business code can still smoothly transition to the target database, because there are SQL 92, SQL 99 and other standards in the syntax of SQL statements, but the SQL syntax used in the storage process often exceeds the above-mentioned standard content. In the present application, the SQL used after the storage process is processed through step S1, step S2, step S3 is a statement meeting the SQL 99 standard, and the complex SQL statement has been converted into JAVA code and is independent of the specific database. Therefore, even after the underlying data storage is changed, the storage process business code can still smoothly transition to the target database.

[0047] The method for converting a stored procedure into code implementation provided by the application further comprises:

[0048] A data configuration step: loading the configuration of the library table requiring reading data in step S1, the data processing script in step S2, and the configuration of the library table requiring storing data in step S3. In step S1, the configuration of the library table requiring reading data is relied on to read the relevant library table data into the data module; in step S2, all data are traversed in sequence, and the data processing script is run to implement the business logic; in step S3, the configuration of the library table requiring storing data is relied on to write the data back to the database.

[0049] According to the above-mentioned stored procedure processing logic and the development model, the developer can complete the code writing and conversion by the following three steps: step one: arranging the tables involved in the stored procedure and completing the configuration according to the framework rules; step two: writing the corresponding business processing script according to the processing logic in the stored procedure; and step three: arranging the tables changed in the stored procedure and completing the configuration according to the framework rules.

[0050] After the above-mentioned framework and development steps are implemented, concurrent operation is added at the data traversal place, so that the entire business processing process can be accelerated.

[0051] The application further provides a system for converting a stored procedure into code implementation, and the person skilled in the art can implement the system for converting a stored procedure into code implementation by executing the flow steps of the method for converting a stored procedure into code implementation, that is, the method for converting a stored procedure into code implementation can be understood as the preferred implementation manner of the system for converting a stored procedure into code implementation.

[0052] The person skilled in the art knows that, in addition to implementing the system, device and each module thereof provided by the application in the form of pure computer readable program code, the same program can also be implemented in the form of logic gates, switches, application specific integrated circuits, programmable logic controllers and embedded microcontrollers, etc. by logically programming the method steps. Therefore, the system, device and each module thereof provided by the application can be considered as a hardware component, and the modules included therein for implementing various programs can also be considered as structures in the hardware component; the modules for implementing various functions can also be considered as both software programs for implementing methods and structures in the hardware component.

[0053] Specifically, according to the system for converting a stored procedure into code implementation provided by the application, the system comprises:

[0054] Module M1: reading original data from a database, wherein the original data comprises tables and fields of the tables used in business logic processing;

[0055] Module M2: processing the original data, converting the SQL logic in the stored procedure into code, and obtaining result data;

[0056] Module M3: storing the result data in the database.

[0057] In the module M1, the original data is read into the memory at one time; in the module M2, the data of the main table in the original data is traversed, and the corresponding code is converted according to the SQL logic in the stored procedure, and the result data is obtained; in the module M3, according to the result data of the business processing, the result data is written back to the database row by row, so as to complete the business requirements.

[0058] The module M1, the module M2 and the module M3 respectively realize data reading, data processing and data storage; in the data reading, multiple tables are read concurrently; in the data processing, the data is processed concurrently according to the data; in the data storage, the data is written concurrently according to the data;

[0059] The data reading, the data processing and the data storage all use the statements meeting the SQL standard.

[0060] In the module M2:

[0061] For the execution process of the update of the SQL, the data meeting the condition is replaced and updated; wherein, for the associated operation, only the associated table is taken as the condition processing;

[0062] For the execution process of the insert of the SQL, the data meeting the condition is added; wherein, the data meeting the condition includes the newly added data or the data filtered according to the associated table;

[0063] For the execution process of the delete of the SQL, the data meeting the condition is replaced and deleted; wherein, for the associated operation, only the associated table is taken as the condition processing.

[0064] According to the present application, a computer readable storage medium storing a computer program is provided, and the computer program is executed by a processor to realize the steps of the method for converting the stored procedure into code.

[0065] According to the present application, an electronic device is provided, which comprises the system for converting the stored procedure into code, or comprises the computer readable storage medium storing the computer program.

[0066] The specific embodiments of the present application are described above. It needs to be understood that the present application is not limited to the specific embodiments described above, and various changes or modifications can be made by those skilled in the art within the scope of the claims, which does not affect the essential content of the present application. The embodiments of the present application and the features in the embodiments can be combined with each other at will without conflict.

Claims

1. A method for converting stored procedures into code implementation, characterized in that, include: Step S1: Read raw data from the database, wherein the raw data includes tables used in business logic processing and the fields of the tables; Step S2: Process the raw data, convert the SQL logic in the stored procedure into code, and obtain the result data; Step S3: Store the result data in the database; In step S1, the original data is read into memory all at once; in step S2, the data in the main table of the original data is traversed, and the SQL logic in the stored procedure is converted into corresponding code to obtain the result data; in step S3, the result data is written back to the database line by line according to the result data of the business processing to complete the business requirements. Steps S1, S2, and S3 respectively implement data reading, data processing, and data storage; in data reading, multiple tables are read concurrently; in data processing, concurrent processing is performed based on data; in data storage, concurrent write operations are performed based on data. Data reading, data processing, and data storage all use statements that conform to the SQL standard; In step S2: For the execution process of SQL update, data that meets the conditions is replaced and updated; for related operations, only the related tables are used as conditions. For the execution process of SQL INSERT, add data that meets the conditions; the data that meets the conditions includes newly added data or data filtered based on the related tables. Regarding the execution process of SQL's delete, data that meets the conditions is replaced or deleted; for related operations, only the related tables are treated as conditions.

2. A system for converting stored procedures into code implementations, characterized in that, include: Module M1: Reads raw data from the database, wherein the raw data includes tables used in business logic processing and the fields of the tables; Module M2: Processes the raw data, converts the SQL logic in the stored procedure into code, and obtains the result data; Module M3: Stores the resulting data in the database; In module M1, the raw data is read into memory all at once; in module M2, the data in the main table of the raw data is traversed, and the SQL logic in the stored procedure is converted into corresponding code to obtain the result data; in module M3, the result data is written back to the database line by line according to the result data of the business processing to complete the business requirements. Modules M1, M2, and M3 respectively implement data reading, data processing, and data storage. In data reading, multiple tables are read concurrently; in data processing, concurrent processing is performed based on the data; and in data storage, concurrent write operations are performed based on the data. Data reading, data processing, and data storage all use statements that conform to the SQL standard; In module M2: For the execution process of SQL update, data that meets the conditions is replaced and updated; for related operations, only the related tables are used as conditions. For the execution process of SQL INSERT, add data that meets the conditions; the data that meets the conditions includes newly added data or data filtered based on the related tables. Regarding the execution process of SQL's delete, data that meets the conditions is replaced or deleted; for related operations, only the related tables are treated as conditions.

3. A computer-readable storage medium storing a computer program, characterized in that, When the computer program is executed by the processor, it implements the steps of the method for converting a stored procedure into code as described in claim 1.

4. An electronic device, characterized in that, It includes the system for converting a stored procedure into code implementation as described in claim 2, or the computer-readable storage medium storing a computer program as described in claim 3.

Images