CN111752549A - SQL function generation method and device - Google Patents
SQL function generation method and device Download PDFInfo
- Publication number
- CN111752549A CN111752549A CN202010585018.8A CN202010585018A CN111752549A CN 111752549 A CN111752549 A CN 111752549A CN 202010585018 A CN202010585018 A CN 202010585018A CN 111752549 A CN111752549 A CN 111752549A
- Authority
- CN
- China
- Prior art keywords
- sql function
- function
- sql
- generating
- name
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Granted
Links
- 238000000034 method Methods 0.000 title claims abstract description 90
- 230000006870 function Effects 0.000 claims description 463
- 238000004590 computer program Methods 0.000 claims description 16
- 238000003780 insertion Methods 0.000 claims description 9
- 230000037431 insertion Effects 0.000 claims description 9
- 238000003860 storage Methods 0.000 claims description 8
- 238000012217 deletion Methods 0.000 claims description 6
- 230000037430 deletion Effects 0.000 claims description 6
- 238000013507 mapping Methods 0.000 claims description 6
- 238000010586 diagram Methods 0.000 description 15
- 238000010606 normalization Methods 0.000 description 8
- 238000012545 processing Methods 0.000 description 5
- 238000011161 development Methods 0.000 description 4
- 230000002452 interceptive effect Effects 0.000 description 2
- 238000012216 screening Methods 0.000 description 2
- 238000012800 visualization Methods 0.000 description 2
- 230000009471 action Effects 0.000 description 1
- 238000006243 chemical reaction Methods 0.000 description 1
- 239000000470 constituent Substances 0.000 description 1
- 230000002708 enhancing effect Effects 0.000 description 1
- 238000012966 insertion method Methods 0.000 description 1
- 238000004519 manufacturing process Methods 0.000 description 1
- 238000012986 modification Methods 0.000 description 1
- 230000004048 modification Effects 0.000 description 1
- 230000003287 optical effect Effects 0.000 description 1
- 238000002360 preparation method Methods 0.000 description 1
- 230000008569 process Effects 0.000 description 1
- 238000005215 recombination Methods 0.000 description 1
- 230000006798 recombination Effects 0.000 description 1
- 238000006467 substitution reaction Methods 0.000 description 1
- 230000000007 visual effect Effects 0.000 description 1
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F8/00—Arrangements for software engineering
- G06F8/30—Creation or generation of source code
- G06F8/31—Programming languages or programming paradigms
-
- Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y02—TECHNOLOGIES OR APPLICATIONS FOR MITIGATION OR ADAPTATION AGAINST CLIMATE CHANGE
- Y02D—CLIMATE CHANGE MITIGATION TECHNOLOGIES IN INFORMATION AND COMMUNICATION TECHNOLOGIES [ICT], I.E. INFORMATION AND COMMUNICATION TECHNOLOGIES AIMING AT THE REDUCTION OF THEIR OWN ENERGY USE
- Y02D10/00—Energy efficient computing, e.g. low power processors, power management or thermal management
Landscapes
- Engineering & Computer Science (AREA)
- Software Systems (AREA)
- General Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Computing Systems (AREA)
- Physics & Mathematics (AREA)
- General Physics & Mathematics (AREA)
- Stored Programmes (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention discloses a method and a device for generating an SQL function, wherein the method comprises the following steps: generating an SQL function name according to the table name of the target database table; determining SQL function input parameters according to a structural body of a field of a target database table; determining SQL function statements according to the data structure of the target database table and the operation type of the target database table; determining an SQL function return method according to a preconfigured function return rule; and when the triggering condition for generating the SQL function is met, generating the SQL function according to the SQL function name, the SQL function input parameter, the SQL function statement and the SQL function return method. The method and the device can respectively determine the SQL function name, the function input parameter, the function statement and the like based on the table name, the field structure and the data structure of the target database table, automatically generate the SQL function based on the SQL function name, the SQL function statement and the like, and can improve the efficiency of generating the SQL function.
Description
Technical Field
The invention relates to the technical field of database operation, in particular to a method and a device for generating an SQL function.
Background
This section is intended to provide a background or context to the embodiments of the invention that are recited in the claims. The description herein is not admitted to be prior art by inclusion in this section.
In database development, operations such as adding, deleting, modifying, checking and the like are often required to be performed on a database table, so that SQL (Structured Query Language) functions corresponding to data operations need to be written and developed.
At present, in order to ensure the accuracy of the SQL function, developers basically write the corresponding SQL function manually. Specifically, a developer queries the database, checks fields, field lengths, field types and the like of the data table, writes corresponding field processing for each field, writes function names and returns. In addition, an auxiliary tool for automatically generating SQL sentences can be adopted for development.
However, manually writing the corresponding SQL function is inefficient; although the auxiliary tool for automatically generating the SQL statement can assist development, the generated SQL statement is usually simple, only contains field names, does not have information such as the type and length of the fields, and cannot assign the fields to variables. Secondly, the conversion of the SQL statements into SQL functions still requires the splitting and recombination of the statements, which causes unnecessary troubles. Finally, the SQL statement is not an SQL function, but still lacks the definition and return values of the function.
Therefore, the existing method for generating the SQL function has the problem of low efficiency.
Disclosure of Invention
The embodiment of the invention provides an SQL function generating method, which is used for improving the efficiency of generating an SQL function and comprises the following steps:
generating an SQL function name according to a table name of a target database table to be operated, which is acquired from a linked database;
determining SQL function input parameters according to a structural body of a field of a target database table;
determining SQL function statements according to the data structure of the target database table and the operation type of the target database table;
determining an SQL function return method according to a preconfigured function return rule;
and when the triggering condition for generating the SQL function is met, generating the SQL function according to the SQL function name, the SQL function input parameter, the SQL function statement and the SQL function return method.
The embodiment of the present invention further provides an SQL function generating device, configured to improve efficiency of generating an SQL function, where the device includes:
the name generation module is used for generating an SQL function name according to the table name of the target database table to be operated, which is acquired from the linked database;
the input parameter generation module is used for determining SQL function input parameters according to the structural body of the field of the target database table;
the statement generation module is used for determining SQL function statements according to the data structure of the target database table and the operation type of the target database table;
the return method generation module is used for determining an SQL function return method according to a preconfigured function return rule;
and the function generation module is used for generating the SQL function according to the SQL function name, the SQL function input parameter, the SQL function statement and the SQL function return method when the triggering condition for generating the SQL function is met.
The embodiment of the invention also provides computer equipment which comprises a memory, a processor and a computer program which is stored on the memory and can run on the processor, wherein the processor realizes the SQL function generation method when executing the computer program.
An embodiment of the present invention further provides a computer-readable storage medium, where a computer program for executing the SQL function generating method is stored in the computer-readable storage medium.
In the embodiment of the invention, SQL function input parameters of the SQL function name are respectively determined according to the table name of the target database table and the structural body of the field; determining SQL function statements based on the data structure of the target database table and the operation type of the target database table; determining an SQL function return method based on a preconfigured function return rule; and finally, when the triggering condition for generating the SQL function is met, generating the SQL function according to the SQL function name, the SQL function input parameter, the SQL function statement and the SQL function return method. The embodiment of the invention can respectively determine the SQL function name, the SQL function input parameter, the SQL function statement and the like based on the table name, the field structure and the data structure of the target database table, prepare for the subsequent generation of the SQL function, automatically generate the SQL function based on the SQL function name, the SQL function statement and the like when the triggering condition for generating the SQL function is met, and can improve the efficiency for generating the SQL function.
Drawings
In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings used in the description of the embodiments or the prior art will be briefly described below, it is obvious that the drawings in the following description are only some embodiments of the present invention, and for those skilled in the art, other drawings can be obtained according to the drawings without creative efforts. In the drawings:
fig. 1 is a flowchart illustrating an implementation of a method for generating an SQL function according to an embodiment of the present invention;
fig. 2 is a flowchart of another implementation of the SQL function generation method according to the embodiment of the present invention;
fig. 3 is a flowchart illustrating an implementation of step 101 in the SQL function generating method according to the embodiment of the present invention;
fig. 4 is a flowchart of another implementation of the SQL function generation method according to the embodiment of the present invention;
fig. 5 is a flowchart illustrating an implementation of step 103 in the SQL function generating method according to the embodiment of the present invention;
fig. 6 is a functional block diagram of an SQL function generating apparatus according to an embodiment of the present invention;
fig. 7 is another functional block diagram of an SQL function generating apparatus according to an embodiment of the present invention;
fig. 8 is a block diagram of a name generation module 601 in the SQL function generation apparatus according to the embodiment of the present invention;
fig. 9 is a further functional block diagram of an SQL function generating apparatus according to an embodiment of the present invention;
fig. 10 is a structural block diagram of a statement generating module 603 in the SQL function generating apparatus according to the embodiment of the present invention;
fig. 11 is a schematic view of a visualization interface for generating an SQL function according to an embodiment of the present invention.
Detailed Description
In order to make the objects, technical solutions and advantages of the embodiments of the present invention more apparent, the embodiments of the present invention are further described in detail below with reference to the accompanying drawings. The exemplary embodiments and descriptions of the present invention are provided to explain the present invention, but not to limit the present invention.
Fig. 1 shows an implementation flow of the SQL function generation method provided in the embodiment of the present invention, and for convenience of description, only the parts related to the embodiment of the present invention are shown, which are detailed as follows:
as shown in fig. 1, the SQL function generating method includes:
103, determining an SQL function statement according to the data structure of the target database table and the operation type of the target database table;
104, determining an SQL function return method according to a preconfigured function return rule;
and 105, when the triggering condition for generating the SQL function is met, generating the SQL function according to the SQL function name, the SQL function input parameter, the SQL function statement and the SQL function return method.
In the embodiment of the invention, when the SQL function is generated, the link with the database is established firstly. The data may include an oracle database, and as can be understood by those skilled in the art, the data may also include other databases besides the oracle database, which is not particularly limited by the embodiment of the present invention.
And after the link with the database is established, acquiring a target database table to be operated from the database. Specifically, the target database table may be obtained from the database according to the received table name input by the user. After the target database table is obtained, the name of the SQL function may be determined based on the table name of the target database table, in preparation for subsequent generation of the SQL function.
In addition, SQL function input parameters can be determined according to the structural body for acquiring the fields of the target database table. For example, the database table icmsprf has the corresponding SQL function input parameter MSPT _ pstrMSPT as a structure MSPT formed by each field in the database table icmspf. Accordingly, the input parameters of the SQL function can be obtained.
Secondly, the SQL function statement can be determined based on the obtained data structure of the target database table and the operation type of the target database table. The data structure of the target database table can be obtained by querying the database. In addition, when the field of the database table cannot be determined and the condition field or the update field of the database table needs to be limited, the data structure of the target database table can be obtained by querying the database.
In addition, in an embodiment of the invention, the operation type on the target database table comprises any one of the following types: query operation (select), insert operation (insert), update operation (update), delete operation (delete), and table flush operation (truncate).
Correspondingly, the generated SQL function comprises any one of the following: a query function, an insert function, an update function, a delete function, and a table empty function.
The query function: querying a function of the table by a SELECT statement, wherein the function comprises a basic query, a simple conditional query and other queries;
the insertion function: inserting a function of the table through an INSERT statement, including basic insertion;
updating a function: updating the functions of the table through the UPDATE statement, wherein the functions comprise full UPDATE and conditional UPDATE;
deletion function: deleting functions recorded by the table through a DELETE statement, wherein the functions comprise deleting and clearing the table according to specified conditions;
clear table function: the function of the table is cleared by the TRUNCATE statement.
And finally, determining a return method of the SQL function based on a preconfigured SQL function return rule. The preset SQL function return rule is a preset SQL function return rule according to actual conditions and specific requirements. Specifically, for example, the SQL function return method determined based on the preconfigured SQL function return rule may include the following two types: (1) return SQLCODE; (2) return SQL YUNL? SQLCODE: 0; .
So far, the SQL function name, the SQL function input parameter, the SQL function statement and the SQL function return method can be respectively obtained. When the triggering condition for generating the SQL function is met, the SQL function name, the SQL function input parameter, the SQL function statement and the SQL function return method are organically combined, and then the SQL function is generated.
In an embodiment of the present invention, in order to improve flexibility of generating an SQL function, when a trigger condition for generating the SQL function is satisfied, the generating the SQL function according to an SQL function name, an SQL function input parameter, an SQL function statement, and an SQL function return method includes:
and when receiving an instruction for generating the SQL function, generating the SQL function according to the SQL function name, the SQL function input parameter, the SQL function statement and the SQL function return method.
For example, the SQL function may be generated by receiving a command for generating the SQL function by one key input by the user, and based on the SQL function name, the SQL function input parameter, the SQL function statement, and the SQL function return method, the efficiency of generating the SQL function may be improved.
In the embodiment of the invention, SQL function input parameters of the SQL function name are respectively determined according to the table name of the target database table and the structural body of the field; determining SQL function statements based on the data structure of the target database table and the operation type of the target database table; determining an SQL function return method based on a preconfigured function return rule; and finally, when the triggering condition for generating the SQL function is met, generating the SQL function according to the SQL function name, the SQL function input parameter, the SQL function statement and the SQL function return method. The embodiment of the invention can respectively determine the SQL function name, the SQL function input parameter, the SQL function statement and the like based on the table name, the field structure and the data structure of the target database table, prepare for the subsequent generation of the SQL function, automatically generate the SQL function based on the SQL function name, the SQL function statement and the like when the triggering condition for generating the SQL function is met, and can improve the efficiency for generating the SQL function.
Fig. 2 shows another implementation flow of the SQL function generation method provided in the embodiment of the present invention, and for convenience of description, only the parts related to the embodiment of the present invention are shown, which is detailed as follows:
in an embodiment of the present invention, in order to improve the security of the database table, as shown in fig. 2, on the basis of the above method steps, the SQL function generating method further includes:
When the SQL function is generated, the database can be linked first, and the safety of the database can be improved. For example, a user name icms01 of the database input by the user, a password icms02 of the database corresponding to the user name, and a tnsname of the database are received, and an IP address of the database input by the user may be received. It will be understood by those skilled in the art that the database link information may include any one or more of the above information, and may also include other information besides the above information, and the embodiment of the present invention is not particularly limited thereto.
In the embodiment of the invention, when the database link instruction is received, the database is connected according to the received database link information, so that the safety of the database table can be improved.
Fig. 3 shows an implementation flow of step 101 in the SQL function generation method provided in the embodiment of the present invention, and for convenience of description, only the parts related to the embodiment of the present invention are shown, which is detailed as follows:
in an embodiment of the present invention, in order to improve the normative and accuracy of generating an SQL function name, as shown in fig. 3, step 101, generating an SQL function name according to a table name of a target database table to be operated, which is acquired from a linked database, includes:
When the SQL function name part is generated, a target database table to be operated may be obtained from a linked database, and it is assumed that the target database table obtained from the database is icmspf. And after the table name icmsptpf of the target database table is obtained, generating the SQL function name based on a pre-configured generation rule of the SQL function name. The generation rule of the preconfigured SQL function name is a generation rule of the preconfigured SQL function name according to actual conditions and specific requirements, and is described in the following with specific examples.
For example, the data type identification bit of the SQL function name may be determined first from the returned data type of the SQL function. Specifically, when the returned data type of the SQL function is int type, i represents the data type identification bit of the SQL function name; and when the returned data type of the SQL function is char type, c represents the data type identification bit of the SQL function name, and the like. The SQL function is not specifically exemplified when the type of the returned data is other type. It is understood that the data type identification bit of the SQL function name may be represented by one or more letters.
In addition, the function type identification bit of the SQL function name may be determined based on the type of the SQL function. For example, the query method uses G or Get as a function type identification bit of the SQL query function; the insertion method takes A or Add as a function type identification bit of an SQL insertion function; the updating method takes U or Upd as a function type identification bit of the SQL updating function; d or Del is used as a function type identification bit of the SQL deletion function in the deletion method; the table emptying method takes T or Tun as a function type identification bit of an SQL table emptying function, and the like. It is understood that the function type identification bits of the SQL function name may be represented by one or more letters.
Finally, the database table identification bit of the SQL function name can be determined based on the name of the database table. For example, the name naming rule of the database table is XX + Y + ZZZ + SS, where XX is the system name, Y is the module name, ZZZ represents the function name of the database table, and SS is the identifier of the table. Taking a target database table icmsptpf as an example, ic corresponds to a system name XX, m corresponds to a module name Y, spt corresponds to a function name ZZZ, and pf corresponds to a mark SS. Since the system name XX (ic) and the identification SS (pf) have little influence on the naming of the database table in the same system, they are not reflected in the naming of the SQL function name. The module name Y and the function name ZZZ of the database table can be used as the database table identification bits of the SQL function name, in one implementation, the module name Y (m) of the database table in the database table identification bits of the SQL function name is capital, and the function name ZZZ is small. E.g., the icmsptpf database table, the last constituent database table has identification bits of Mspt.
Therefore, the SQL function name can be determined according to the data type identification bit, the function type identification bit and the database table identification bit of the SQL function name. In one implementation, the data type identification bits, the function type identification bits, and the database table identification bits are directly combined in sequence to obtain the SQL function name.
For example, the function name of the SQL query function is iGetMspt, i is a data type identification bit, the returned data type representing the SQL query function is int type, Get represents the function type identification bit, the function type is query, and Mspt represents a database table identification bit. The function name of the SQL delete function is iDelMspt, i is a data type identification bit, the returned data type of the SQL delete function is int type, Del represents the function type identification bit, the function type is delete, Mspt represents a database table identification bit and the like, and other SQL function names are not illustrated one by one.
Those skilled in the art will appreciate that the SQL function name may also be generated based on other preconfigured SQL function name generation rules, which are not illustrated here in detail.
In the embodiment of the invention, the target database table to be operated is obtained from the linked database, the SQL function name is generated according to the table name of the target database table and the generation rule of the preconfigured SQL function name, and the normalization and the accuracy of generating the SQL function name can be improved.
Fig. 4 shows a further implementation flow of the SQL function generation method provided in the embodiment of the present invention, and for convenience of description, only the parts related to the embodiment of the present invention are shown, which are detailed as follows:
in an embodiment of the present invention, in order to improve the flexibility of generating the SQL function, as shown in fig. 4, on the basis of the above method steps, the method for generating the SQL function further includes:
Correspondingly, in step 105, when the trigger condition for generating the SQL function is satisfied, the SQL function is generated according to the SQL function name, the SQL function input parameter, the SQL function statement, and the SQL function return method, which includes:
and step 402, when the triggering condition for generating the SQL function is met, generating the SQL function according to the SQL function name, the SQL function input parameter, the SQL function condition parameter, the SQL function statement and the SQL function return method.
Specifically, some SQL functions may not have conditional parameters, but some SQL functions have conditional parameters. When the SQL function is a conditional SQL function, then when the conditional SQL function is generated, the condition parameter may be determined based on a mapping relationship between the database field type and the source code field type. For example,
the oracle database data type and the C source code type are mapped as follows:
that is, database data type VARCHAR2 corresponds to char type in C source code, database data type DATE corresponds to char type in C source code, database data type RAW corresponds to char type in C source code, and so on.
Sptcde in a target database table icmsprf in an oracle database is a query condition, a field initial is capitalized, a condition parameter of a conditional SQL function is const char psSptcde, and the fact that the data type in the source code of the SQL function corresponding to the data type of the target database table icmsprf is a char type and the Sptcde is a query condition of the SQL function is described.
Correspondingly, when generating the SQL function, the SQL function needs to be generated based on the SQL function condition parameter in addition to the SQL function name, the SQL function input parameter, the SQL function statement, and the SQL function return method.
In the embodiment of the invention, the SQL function condition parameters are determined according to the mapping relation between the database field type and the source code field type, so that the flexibility of generating the SQL function can be improved.
Fig. 5 shows an implementation flow of step 103 in the SQL function generation method provided in the embodiment of the present invention, and for convenience of description, only the parts related to the embodiment of the present invention are shown, which is detailed as follows:
in an embodiment of the present invention, in order to improve the accuracy and the normalization of generating the SQL function statement, as shown in fig. 5, step 103 is to determine the SQL function statement according to the data structure of the target database table and the operation type of the target database table, and includes:
When generating the SQL function statement part, a target database table to be operated may be obtained from a linked database, assuming that the database table obtained from the database is icmspf, and a data dictionary for obtaining the database table icmspf by querying is:
SPTCDE parameter code VARCHAR 26N
SPTNAM name VARCHAR 230Y
SPTVAL parameter value VARCHAR 230Y
SPTLVL parameter level 0-modifiable 1-non-modifiable VARCHAR 21Y
SPTREM notes VARCHAR 280Y.
After the data dictionary of the database table icmsptpf is obtained, the SQL function statement is determined based on the data dictionary of the database table icmsptpf and the operation type of the target database table. The following are respectively exemplified:
SQL query function:
the operation type of the target database table is query operation, the first line is fixed as EXEC SQL SELECT, the next lines are input query fields, next line is following with INTO, then fields are assigned to corresponding structural body fields, for example, SPTCDE is assigned to pstrMSPT- > SPTCDE, then fixed FROM keywords are used, then another line is used for writing table names, and finally WHERE keywords are used for circularly writing screening conditions. If the condition field is SPTCDE, the screening condition is SPTCDE ═ psSptcde, and the finally generated SQL statement is as follows:
SQL insert function:
the type of operation on the target database table is insertion operation, the first line of the insertion function fixes EXEC sqline internal, and the line immediately following the insertion is the table name, because the insertion is usually the whole line insertion, all fields in the table are followed by the VALUES key, and finally the structure in the table.
SQL update function:
the operation type of the target database table is an updating operation, the first line is fixed as EXEC SQL UPDATEICMPTPF, then the first line is followed by SET keywords, then a statement with an updating field is generated circularly, and finally a WHERE keyword is added, so that an updating condition is carried out.
SQL delete function:
the operation type of the target database table is deletion operation, the first action EXEC SQL DELETE FROM adds the table name, then follows the WHERE key word, and then generates the condition field statement circularly.
SQL clear table function:
the operation type of the target database TABLE is TABLE clearing operation, the statement part of a function for clearing the TABLE is only one sentence, EXECSQL TRUNCATE TABLE is added with the TABLE name of the database TABLE
EXEC SQL TRUNCATE TABLE ICMSPTPF；。
In the embodiment of the invention, the data dictionary of the database table is obtained according to the data structure of the target database table, the SQL function statement is determined according to the data dictionary of the target database table and the operation type of the target database table, and the accuracy and the normalization of generating the SQL function statement are improved.
The embodiment of the present invention further provides an SQL function generating device, as described in the following embodiments. Because the principle of solving the problems of the devices is similar to the SQL function generation method, the implementation of the devices can be referred to the implementation of the method, and repeated details are not repeated.
Fig. 6 shows functional modules of an SQL function generating apparatus according to an embodiment of the present invention, and for convenience of description, only the parts related to the embodiment of the present invention are shown, which are detailed as follows:
referring to fig. 6, each module included in the SQL function generating apparatus is used to execute each step in the embodiment corresponding to fig. 1, and please refer to fig. 1 and the related description in the embodiment corresponding to fig. 1 specifically, which is not described herein again. In the embodiment of the present invention, the SQL function generating apparatus includes a name generating module 601, an input parameter generating module 602, a statement generating module 603, a return method generating module 604, and a function generating module 605.
The name generating module 601 is configured to generate an SQL function name according to a table name of a target database table to be operated, which is obtained from a linked database.
And an input parameter generating module 602, configured to determine an SQL function input parameter according to a structure of a field of the target database table.
And the statement generating module 603 is configured to determine an SQL function statement according to the data structure of the target database table and the operation type of the target database table.
The return method generating module 604 is configured to determine an SQL function return method according to a preconfigured function return rule.
The function generating module 605 is configured to generate the SQL function according to the SQL function name, the SQL function input parameter, the SQL function statement, and the SQL function return method when the trigger condition for generating the SQL function is satisfied.
In the embodiment of the present invention, the name generation module 601 and the input parameter generation module 602 respectively determine SQL function input parameters of the SQL function name according to the table name of the target database table and the structure of the field; the statement generation module 603 determines an SQL function statement based on the data structure of the target database table and the operation type of the target database table; the return method generation module 604 also determines an SQL function return method based on a preconfigured function return rule; and finally, when the function generation module 605 meets the triggering condition for generating the SQL function, generating the SQL function according to the SQL function name, the SQL function input parameter, the SQL function statement and the SQL function return method. The embodiment of the invention can respectively determine the SQL function name, the SQL function input parameter, the SQL function statement and the like based on the table name, the field structure and the data structure of the target database table, prepare for the subsequent generation of the SQL function, automatically generate the SQL function based on the SQL function name, the SQL function statement and the like when the triggering condition for generating the SQL function is met, and can improve the efficiency for generating the SQL function.
In an embodiment of the present invention, the function generating module 605 is specifically configured to generate the SQL function according to the SQL function name, the SQL function input parameter, the SQL function statement, and the SQL function return method when receiving the instruction for generating the SQL function.
Fig. 7 shows another functional module of the SQL function generating apparatus according to the embodiment of the present invention, and for convenience of description, only the relevant parts related to the embodiment of the present invention are shown, which is detailed as follows:
in an embodiment of the present invention, referring to fig. 7, in order to improve the security of the database table, each module included in the SQL function generating apparatus is configured to execute each step in the embodiment corresponding to fig. 2, specifically please refer to fig. 2 and the related description in the embodiment corresponding to fig. 2, which is not described herein again. In the embodiment of the present invention, on the basis of the above functional modules, the SQL function generating apparatus further includes a linking module 701.
The link module 701 is configured to connect to a database according to the received database link information when receiving a database link instruction; the database link information includes a user name of the database, a password of the database, a tnsname of the database, and an IP address of the database.
In the embodiment of the present invention, when receiving a database link instruction, the link module 701 connects the database according to the received database link information, which can improve the security of the database table.
Fig. 8 shows a structural schematic diagram of a name generating module 601 in the SQL function generating apparatus according to the embodiment of the present invention, and for convenience of description, only the parts related to the embodiment of the present invention are shown, which is detailed as follows:
in an embodiment of the present invention, in order to improve the normalization and the accuracy of generating the name of the SQL function, referring to fig. 8, each module included in the name generating module 601 is configured to execute each step in the embodiment corresponding to fig. 3, and specifically refer to fig. 3 and the related description in the embodiment corresponding to fig. 3, which is not repeated herein. In the embodiment of the present invention, the name generating module 601 includes an obtaining unit 801 and a name generating unit 802.
An obtaining unit 801, configured to obtain a target database table to be operated from the linked database.
The name generating unit 802 is configured to generate an SQL function name according to the table name of the target database table and a generation rule of a preconfigured SQL function name.
In the embodiment of the present invention, the obtaining unit 801 obtains a target database table to be operated from a linked database, and the name generating unit 802 generates an SQL function name according to a table name of the target database table and a generation rule of a preconfigured SQL function name, which can improve the normalization and the accuracy of generating the SQL function name.
Fig. 9 shows another functional module of the SQL function generating apparatus according to the embodiment of the present invention, and for convenience of description, only the relevant parts related to the embodiment of the present invention are shown, which is detailed as follows:
in an embodiment of the present invention, in order to improve the flexibility of generating the SQL function, referring to fig. 9, each module included in the SQL function generating apparatus is configured to execute each step in the embodiment corresponding to fig. 4, specifically, please refer to fig. 4 and the related description in the embodiment corresponding to fig. 4, which is not described herein again. In the embodiment of the present invention, on the basis of the above module structure, the SQL function generating apparatus further includes a conditional parameter generating module 901.
The conditional parameter generating module 901 is configured to determine a conditional parameter of the SQL function according to a mapping relationship between a field type of the database and a field type of the source code.
Accordingly, the SQL function generation module 605 includes a function generation unit 902.
The function generating unit 902 is configured to generate the SQL function according to the SQL function name, the SQL function input parameter, the SQL function condition parameter, the SQL function statement, and the SQL function return method when the trigger condition for generating the SQL function is satisfied.
In the embodiment of the present invention, the condition parameter generating module 901 determines the SQL function condition parameter according to the mapping relationship between the database field type and the source code field type, which can improve the flexibility of generating the SQL function.
Fig. 10 shows a structural schematic diagram of the statement generating module 603 in the SQL function generating apparatus according to the embodiment of the present invention, and for convenience of description, only the parts related to the embodiment of the present invention are shown, which is detailed as follows:
in an embodiment of the present invention, in order to improve the accuracy and the normalization of generating the SQL function statement, referring to fig. 10, each unit included in the statement generating module 603 is configured to execute each step in the embodiment corresponding to fig. 5, specifically please refer to fig. 5 and the related description in the embodiment corresponding to fig. 5, which is not repeated herein. In the embodiment of the present invention, the sentence generation module 603 includes a data dictionary obtaining unit 1001 and a sentence generation unit 1002.
A data dictionary obtaining unit 1001, configured to obtain a data dictionary of a database table according to a data structure of a target database table;
and the statement generating unit 1002 is configured to determine an SQL function statement according to the data dictionary of the target database table and the operation type of the target database table.
In the embodiment of the present invention, the data dictionary obtaining unit 1001 obtains the data dictionary of the database table according to the data structure of the target database table, and the statement generating unit 1002 determines the SQL function statement according to the data dictionary of the target database table and the operation type of the target database table, so as to improve the accuracy and normalization of generating the SQL function statement.
Fig. 11 shows a schematic of a visualization interface for generating an SQL function according to an embodiment of the present invention, and for convenience of description, only the parts related to the embodiment of the present invention are shown, which are detailed as follows:
as shown in fig. 11, the method for generating the SQL function may be performed on a visual operation interface. When the SQL function is generated, the database is linked through the database link information by receiving a user name of the database input by a user, a password corresponding to the user name of the database, a tns name (tnsname) of the database and an IP address of the database. And receiving a table name icmspf of a target database table in the database to be accessed (database query, deletion, update and other operations) input by a user. Then, if the SQL function has a condition, the parameters of the SQL function can be input in the update field and the condition field.
Next, the database table's data structure can be queried using the datebase function in the interactive interface. The interactive interface further includes an operation method corresponding to the SQL function, for example, the query operation is a select button, the insert operation is an insert button, the update operation is an update button, the delete operation is a delete button, and the table clearing operation is a truncate button.
And when an instruction of clicking the go button by the user is received, visually displaying the code of the generated SQL function in the lower window based on the information. From the example can be seen the SQL query function (igemsvt) generated.
The embodiment of the invention also provides computer equipment which comprises a memory, a processor and a computer program which is stored on the memory and can run on the processor, wherein the processor realizes the SQL function generation method when executing the computer program.
An embodiment of the present invention further provides a computer-readable storage medium, where a computer program for executing the SQL function generating method is stored in the computer-readable storage medium.
In summary, in the embodiments of the present invention, SQL function input parameters are respectively determined according to the table name and the structure of the field of the target database table; determining SQL function statements based on the data structure of the target database table and the operation type of the target database table; determining an SQL function return method based on a preconfigured function return rule; and finally, when the triggering condition for generating the SQL function is met, generating the SQL function according to the SQL function name, the SQL function input parameter, the SQL function statement and the SQL function return method. The embodiment of the invention can respectively determine the SQL function name, the SQL function input parameter, the SQL function statement and the like based on the table name, the field structure and the data structure of the target database table, prepare for the subsequent generation of the SQL function, automatically generate the SQL function based on the SQL function name, the SQL function statement and the like when the triggering condition for generating the SQL function is met, and can improve the efficiency for generating the SQL function.
The invention can automatically generate the SQL access function of the table by one key by adding the basic information, the table name and the required condition field of the database, thereby improving the development efficiency, enhancing the code normalization, improving the product quality and greatly improving the working quality and efficiency of developers. The developer can correspondingly develop the data according to different programming language environments and database types.
As will be appreciated by one skilled in the art, embodiments of the present invention may be provided as a method, system, or computer program product. Accordingly, the present invention may take the form of an entirely hardware embodiment, an entirely software embodiment or an embodiment combining software and hardware aspects. Furthermore, the present invention may take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, and the like) having computer-usable program code embodied therein.
The present invention is described with reference to flowchart illustrations and/or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of the invention. It will be understood that each flow and/or block of the flow diagrams and/or block diagrams, and combinations of flows and/or blocks in the flow diagrams and/or block diagrams, can be implemented by computer program instructions. These computer program instructions may be provided to a processor of a general purpose computer, special purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, create means for implementing the functions specified in the flowchart flow or flows and/or block diagram block or blocks.
These computer program instructions may also be stored in a computer-readable memory that can direct a computer or other programmable data processing apparatus to function in a particular manner, such that the instructions stored in the computer-readable memory produce an article of manufacture including instruction means which implement the function specified in the flowchart flow or flows and/or block diagram block or blocks.
These computer program instructions may also be loaded onto a computer or other programmable data processing apparatus to cause a series of operational steps to be performed on the computer or other programmable apparatus to produce a computer implemented process such that the instructions which execute on the computer or other programmable apparatus provide steps for implementing the functions specified in the flowchart flow or flows and/or block diagram block or blocks.
The above-mentioned embodiments are intended to illustrate the objects, technical solutions and advantages of the present invention in further detail, and it should be understood that the above-mentioned embodiments are only exemplary embodiments of the present invention, and are not intended to limit the scope of the present invention, and any modifications, equivalent substitutions, improvements and the like made within the spirit and principle of the present invention should be included in the scope of the present invention.
Claims (10)
1. A SQL function generation method is characterized by comprising the following steps:
generating an SQL function name according to a table name of a target database table to be operated, which is acquired from a linked database;
determining SQL function input parameters according to a structural body of a field of a target database table;
determining SQL function statements according to the data structure of the target database table and the operation type of the target database table;
determining an SQL function return method according to a preconfigured function return rule;
and when the triggering condition for generating the SQL function is met, generating the SQL function according to the SQL function name, the SQL function input parameter, the SQL function statement and the SQL function return method.
2. The SQL function generation method of claim 1, further comprising:
when a database link instruction is received, connecting a database according to the received database link information; the database link information includes a user name of the database, a password of the database, a tnsname of the database, and an IP address of the database.
3. The SQL function generating method according to claim 1, wherein generating the SQL function name according to the table name of the target database table to be operated, which is acquired from the linked database, comprises:
acquiring a target database table to be operated from a linked database;
and generating the SQL function name according to the table name of the target database table and a generation rule of the preconfigured SQL function name.
4. The SQL function generation method of claim 1, further comprising:
determining SQL function condition parameters according to the mapping relation between the database field type and the source code field type;
correspondingly, when the triggering condition for generating the SQL function is satisfied, the SQL function is generated according to the SQL function name, the SQL function input parameter, the SQL function statement, and the SQL function return method, which includes:
and when the triggering condition for generating the SQL function is met, generating the SQL function according to the SQL function name, the SQL function input parameter, the SQL function condition parameter, the SQL function statement and the SQL function return method.
5. The SQL function generation method of claim 1, wherein determining the SQL function statement according to the data structure of the target database table and the operation type of the target database table comprises:
acquiring a data dictionary of the database table according to the data structure of the target database table;
and determining the SQL function statement according to the data dictionary of the target database table and the operation type of the target database table.
6. The SQL function generation method of claim 5, wherein the operation type on the target database table comprises any one of:
query operation, insertion operation, update operation, deletion operation and table clearing operation;
correspondingly, the generated SQL function comprises any one of the following:
a query function, an insert function, an update function, a delete function, and a table empty function.
7. The SQL function generating method according to claim 1, wherein when the trigger condition for generating the SQL function is satisfied, generating the SQL function according to the SQL function name, the SQL function input parameter, the SQL function statement, and the SQL function return method includes:
and when receiving an instruction for generating the SQL function, generating the SQL function according to the SQL function name, the SQL function input parameter, the SQL function statement and the SQL function return method.
8. An SQL function generation apparatus, comprising:
the name generation module is used for generating an SQL function name according to the table name of the target database table to be operated, which is acquired from the linked database;
the input parameter generation module is used for determining SQL function input parameters according to the structural body of the field of the target database table;
the statement generation module is used for determining SQL function statements according to the data structure of the target database table and the operation type of the target database table;
the return method generation module is used for determining an SQL function return method according to a preconfigured function return rule;
and the function generation module is used for generating the SQL function according to the SQL function name, the SQL function input parameter, the SQL function statement and the SQL function return method when the triggering condition for generating the SQL function is met.
9. A computer device comprising a memory, a processor and a computer program stored on the memory and executable on the processor, wherein the processor implements the SQL function generation method according to any one of claims 1 to 7 when executing the computer program.
10. A computer-readable storage medium storing a computer program for executing the SQL function generating method according to any one of claims 1 to 7.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010585018.8A CN111752549B (en) | 2020-06-24 | 2020-06-24 | SQL function generation method and device |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010585018.8A CN111752549B (en) | 2020-06-24 | 2020-06-24 | SQL function generation method and device |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN111752549A true CN111752549A (en) | 2020-10-09 |
| CN111752549B CN111752549B (en) | 2023-07-21 |
Family
ID=72676821
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202010585018.8A Active CN111752549B (en) | 2020-06-24 | 2020-06-24 | SQL function generation method and device |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN111752549B (en) |
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN112579604A (en) * | 2020-12-23 | 2021-03-30 | 平安普惠企业管理有限公司 | Test system number making method, device, equipment and storage medium |
| CN112822284A (en) * | 2021-01-22 | 2021-05-18 | 平安壹钱包电子商务有限公司 | Data processing method and device based on dynamic proxy and computer equipment |
Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101464862A (en) * | 2007-12-21 | 2009-06-24 | 英业达股份有限公司 | Structured query language generation system and method |
| KR101260436B1 (en) * | 2012-10-24 | 2013-05-09 | 주식회사 소프트잼 | Automatic procoessing system and method for structured query language, and computer readable recoding medium for performing it |
| CN106126540A (en) * | 2016-06-15 | 2016-11-16 | 中国传媒大学 | Data base access system and access method thereof |
| CN109582691A (en) * | 2018-11-15 | 2019-04-05 | 百度在线网络技术（北京）有限公司 | Method and apparatus for controlling data query |
-
2020
- 2020-06-24 CN CN202010585018.8A patent/CN111752549B/en active Active
Patent Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101464862A (en) * | 2007-12-21 | 2009-06-24 | 英业达股份有限公司 | Structured query language generation system and method |
| KR101260436B1 (en) * | 2012-10-24 | 2013-05-09 | 주식회사 소프트잼 | Automatic procoessing system and method for structured query language, and computer readable recoding medium for performing it |
| CN106126540A (en) * | 2016-06-15 | 2016-11-16 | 中国传媒大学 | Data base access system and access method thereof |
| CN109582691A (en) * | 2018-11-15 | 2019-04-05 | 百度在线网络技术（北京）有限公司 | Method and apparatus for controlling data query |
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN112579604A (en) * | 2020-12-23 | 2021-03-30 | 平安普惠企业管理有限公司 | Test system number making method, device, equipment and storage medium |
| CN112822284A (en) * | 2021-01-22 | 2021-05-18 | 平安壹钱包电子商务有限公司 | Data processing method and device based on dynamic proxy and computer equipment |
Also Published As
| Publication number | Publication date |
|---|---|
| CN111752549B (en) | 2023-07-21 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US5659723A (en) | Entity/relationship to object oriented logical model conversion method | |
| US5640550A (en) | Computer system for generating SQL statements from COBOL code | |
| US5550971A (en) | Method and system for generating a user interface adaptable to various database management systems | |
| US5937409A (en) | Integrating relational databases in an object oriented environment | |
| US8086998B2 (en) | transforming meta object facility specifications into relational data definition language structures and JAVA classes | |
| US20120011134A1 (en) | Systems and methods for database query translation | |
| JP2006244478A (en) | Composable query building api and query language | |
| CN107145549B (en) | Database cache control method and system | |
| CN112860265A (en) | Method and device for detecting operation abnormity of source code database | |
| US7409410B2 (en) | System and method of presenting multilingual metadata | |
| CN114090671A (en) | Data import method and device, electronic equipment and storage medium | |
| CN111752549A (en) | SQL function generation method and device | |
| EP1385098A2 (en) | Simulation of data types in a relational database | |
| Daniel et al. | Gremlin-ATL: a scalable model transformation framework | |
| CA2306974A1 (en) | Management of application programming interface interoperability | |
| KR20080038306A (en) | Nullable and post binding | |
| CN112925726A (en) | Method for automatically generating database test case and storage medium | |
| US8433729B2 (en) | Method and system for automatically generating a communication interface | |
| CN110209699B (en) | Data interface dynamic generation and execution method based on openEHR Composition template | |
| CN101000621A (en) | Interdynamic access method and tool of IMS data base | |
| CN111881220A (en) | Data operation method and device under list storage, electronic equipment and storage medium | |
| Chen et al. | Implementing typeful program transformations | |
| CN116955393A (en) | Data processing method and device, electronic equipment and storage medium | |
| Jakob et al. | View creation of meta models by using modified triple graph grammars | |
| CN119621143B (en) | Program code analysis method and memory state analysis method based on analysis system |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |