CN111984669B - Functional SQL query method, device, equipment and medium supporting dynamic variables - Google Patents
Functional SQL query method, device, equipment and medium supporting dynamic variables Download PDFInfo
- Publication number
- CN111984669B CN111984669B CN202010650476.5A CN202010650476A CN111984669B CN 111984669 B CN111984669 B CN 111984669B CN 202010650476 A CN202010650476 A CN 202010650476A CN 111984669 B CN111984669 B CN 111984669B
- Authority
- CN
- China
- Prior art keywords
- parameter
- mark
- sql
- value
- script
- 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.)
- Active
Links
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
- G06F16/2448—Query languages for particular applications; for extensibility, e.g. user defined types
-
- G—PHYSICS
- G16—INFORMATION AND COMMUNICATION TECHNOLOGY [ICT] SPECIALLY ADAPTED FOR SPECIFIC APPLICATION FIELDS
- G16H—HEALTHCARE INFORMATICS, i.e. INFORMATION AND COMMUNICATION TECHNOLOGY [ICT] SPECIALLY ADAPTED FOR THE HANDLING OR PROCESSING OF MEDICAL OR HEALTHCARE DATA
- G16H50/00—ICT specially adapted for medical diagnosis, medical simulation or medical data mining; ICT specially adapted for detecting, monitoring or modelling epidemics or pandemics
- G16H50/70—ICT specially adapted for medical diagnosis, medical simulation or medical data mining; ICT specially adapted for detecting, monitoring or modelling epidemics or pandemics for mining of medical data, e.g. analysing previous cases of other patients
-
- 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)
- Theoretical Computer Science (AREA)
- Physics & Mathematics (AREA)
- Data Mining & Analysis (AREA)
- Databases & Information Systems (AREA)
- General Engineering & Computer Science (AREA)
- Mathematical Physics (AREA)
- General Physics & Mathematics (AREA)
- Medical Informatics (AREA)
- Computational Linguistics (AREA)
- Health & Medical Sciences (AREA)
- Public Health (AREA)
- Biomedical Technology (AREA)
- Pathology (AREA)
- Epidemiology (AREA)
- General Health & Medical Sciences (AREA)
- Primary Health Care (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention provides a method, a device, equipment and a medium for querying a functional SQL supporting dynamic variables, wherein the method comprises the following steps: s1, transmitting a formula of a functional SQL containing dynamic variables to a script parameter extractor to extract a parameter head mark, a parameter body mark and a parameter body containing the dynamic variables to a script parameter analyzer; s2, when the script parameter analyzer receives the content sent by the script parameter extractor, matching a parameter head mark, analyzing a parameter body mark, analyzing the type of the parameter head mark, and performing classification replacement processing on dynamic variables to obtain an sql segment corresponding to a formula; s3, backfilling the SQL segment into SQL by a script generator, so as to generate an SQL query language which can be directly executed by the database, and obtaining an SQL query result after the SQL query language is directly executed by the database. The invention expands the capability based on the original SQL, combines the functions with the dynamic variables, so that the SQL inquiry is more flexible, diversified and accurate, and is easy to expand.
Description
Technical Field
The invention relates to the technical field of computers, in particular to a functional SQL query method, device, equipment and medium supporting dynamic variables.
Background
The rapid development of the internet has led to a continuous rise in the number of people using the internet, a wider and wider range of applications, and explosive increases in the amount of data produced daily. The era of big data and cloud computing has evolved. However, these techniques have great dependencies on databases and query languages, and the larger amount of data and richer data types present greater challenges to the data type support and query languages of the databases. For example, in a large environment where intelligent medical treatment is vigorously developed, an electronic case system under a medical system is required to more accurately count and analyze various information such as cure time, consumption amount and the like of a month and year consultation patient. Patient self-help systems are also required to provide patients with more diverse and accurate query results so that the patient knows about themselves. However, the current SQL query statement cannot support a dynamic variable function, is not easy to expand and has poor flexibility, and can not provide more diversified and more accurate query services for the fields of intelligent medical systems and the like, so that a trigger is provided for the generation of the invention.
Disclosure of Invention
The invention aims to solve the technical problem of providing a function type SQL query method, device, equipment and medium supporting dynamic variables, and capability expansion is performed on the basis of original SQL. The functions are combined with the dynamic variables, so that SQL inquiry is more flexible, diversified and accurate, and is easy to expand.
In a first aspect, the present invention provides a method for supporting a function-based SQL query of dynamic variables, comprising the steps of:
s1, transmitting a formula of a functional SQL containing dynamic variables to a script parameter extractor, extracting a parameter head mark, a parameter body mark and parameter body content from the formula by the script parameter extractor, and transmitting the parameter head mark, the parameter body mark and the parameter body content to a script parameter analyzer, wherein the dynamic variables exist in the parameter body content;
s2, when the script parameter analyzer receives the content sent by the script parameter extractor, analyzing the parameter body mark, analyzing the type of the parameter head mark and carrying out the following classification treatment on the dynamic variable value in the parameter body content:
when the type of the parameter head mark obtained through analysis is a single parameter value type, acquiring parameter body content, and directly setting a dynamic variable value in the parameter body content as a variable value input by a user;
when the type of the parameter head mark is a multi-parameter value type through analysis, acquiring parameter body content, and converting a dynamic variable value IN the parameter body content into a static value of an IN mode IN SQL;
when the type of the parameter head mark is the function type, acquiring the parameter body content, transmitting the parameter body content to a script parameter function executor to execute the function, receiving a function execution result, and replacing the dynamic variable value in the parameter body content by the function execution result;
the script parameter analyzer obtains the sql segment corresponding to the formula and transmits the sql segment to the script generator;
the script parameter analyzer obtains the sql segment corresponding to the formula and transmits the sql segment to the script generator;
s3, backfilling the SQL segment received by the script generator into SQL, so as to generate an SQL query language which can be directly executed by the database, and obtaining an SQL query result after the SQL query language is directly executed by the database.
In a second aspect, the present invention provides a functional SQL query device supporting dynamic variables, which is characterized in that: comprising the following steps:
the script parameter extractor is used for transmitting a formula of the functional SQL containing the dynamic variables to the script parameter extractor, extracting a parameter head mark, a parameter body mark and a parameter body content from the formula by the script parameter extractor and transmitting the parameter head mark, the parameter body mark and the parameter body content to the script parameter analyzer, wherein the dynamic variables exist in the parameter body content;
the script parameter analyzer is used for receiving the content sent by the script parameter extractor, analyzing the parameter body mark, analyzing the type of the parameter head mark and carrying out the following classification treatment on the dynamic variable value in the parameter body content:
when the type of the parameter head mark obtained through analysis is a single parameter value type, acquiring parameter body content, and directly setting a dynamic variable value in the parameter body content as a variable value input by a user;
when the type of the parameter head mark is a multi-parameter value type through analysis, acquiring parameter body content, and converting a dynamic variable value IN the parameter body content into a static value of an IN mode IN SQL;
when the type of the parameter head mark is the function type, acquiring the parameter body content, transmitting the parameter body content to a script parameter function executor to execute the function, receiving a function execution result, and replacing the dynamic variable value in the parameter body content by the function execution result;
the script parameter analyzer obtains the sql segment corresponding to the formula and transmits the sql segment to the script generator;
and the script generator is used for backfilling the received SQL segment into SQL so as to generate an SQL query language which can be directly executed by the database and provide the SQL query language for the database to execute so as to obtain an SQL query result.
In a third aspect, the present invention provides an electronic device comprising a memory, a processor and a computer program stored on the memory and executable on the processor, the processor implementing the method of the first aspect when executing the program.
In a fourth aspect, the present invention provides a computer readable storage medium having stored thereon a computer program which when executed by a processor implements the method of the first aspect.
One or more technical solutions provided in the embodiments of the present invention at least have the following technical effects or advantages: the method has the advantages that the capability expansion is performed on the basis of the original SQL, and the parameter variables, the parameter types and the functions are added on the original SQL, so that the flexibility and the convenience of the SQL are greatly improved. The constant form which cannot be changed by the original SQL is changed. And the novel SQL query result is identical to the original SQL result, the result processing code of the original system is non-invasive, and the extra development work of a developer is not added.
The foregoing description is only an overview of the present invention, and is intended to be implemented in accordance with the teachings of the present invention in order that the same may be more clearly understood and to make the same and other objects, features and advantages of the present invention more readily apparent.
Drawings
The invention will be further described with reference to examples of embodiments with reference to the accompanying drawings.
FIG. 1 is a flow chart of a method according to a first embodiment of the invention;
FIG. 2 is a flowchart of a process for obtaining parameters in a method according to a first embodiment of the present invention;
FIG. 3 is a flow chart illustrating a process of resolving parameters in a method according to a first embodiment of the invention;
FIG. 4 is a flowchart illustrating a method for performing a function according to a first embodiment of the present invention;
FIG. 5 is a flow chart of the method for generating SQL according to the first embodiment of the invention;
FIG. 6 is a schematic diagram of a device according to a second embodiment of the present invention;
fig. 7 is a schematic structural diagram of an electronic device according to a third embodiment of the present invention;
fig. 8 is a schematic structural diagram of a medium in a fourth embodiment of the present invention.
Detailed Description
According to the method, the device, the equipment and the medium for querying the SQL by the function formula supporting the dynamic variables, the capability expansion is performed on the basis of the original SQL, and the functions are combined with the dynamic variables, so that the SQL query is more flexible, diversified and accurate, and is easy to expand.
According to the technical scheme in the embodiment of the application, the overall thought is as follows: adding a new mark into the original SQL to form a new SQL, writing a new SQL sentence by a developer, transmitting the new SQL sentence to a script parameter extractor, extracting the contents in a parameter head mark and a parameter body mark by the script parameter extractor, wherein a dynamic variable exists in the parameter body content, carrying out different replacement processing on the dynamic variable by a script parameter analyzer according to the type of the parameter head mark obtained by analysis, and analyzing and processing by the script parameter analyzer, wherein all the parameter head marks and the parameter body contents of the new SQL are analyzed into corresponding SQL fragments. The script generator acquires the SQL segment and backfills the SQL segment to generate a query language which can be directly executed by the database. Because the returned result of the script generator is SQL which can be directly operated by the database, the result obtained by the developer is similar to the original handwriting SQL, no learning cost and no code rewriting exist, and the result processing code of the original system is noninvasive, so that the extra development work of the developer is not increased.
Before describing the specific embodiment, the new flag in the SQL of the present application is described, specifically in the following table:
the formula is written as follows: the formula of the functional SQL is as follows: parameter header flag + start flag + parameter name +: + SQL script fragment containing dynamic variable flags + end flag in parameter body flags.
Namely: parameter header flag ($, @, #) + parameter body flag (start) ('[', { ')) + parameter name +': '+ SQL script fragment containing dynamic variable flag (including parameter name) +parameter body flag (end) (') 'and' ]).
The following examples are several common writing schemes:
A. the variable value is written in the single item selecting and filling mode:
SELECT FROM ep_file WHERE del_flag=0 $ [ item: AND process= | ]! { item })
B. The variable value is written in a plurality of ways when the variable value is necessary to be filled:
SELECT FROM ep_file write del_flag=0@item @ AND Projectin-! { item })
C. The variable value is written in the way of function filling:
SELECT FROM ep_file WHERE del_flag=0# item AND process= |! { item+1 } ].
In the practical application of the invention, the method is characterized by the following steps! The contents of the packet of the { } flags are dynamic variables whose values are filled by the user, and the dynamic variables are called dynamic because different values are generated according to the types of the parameter header flags and the parameter body flags.
Example 1
As shown in fig. 1 to 6, the present embodiment provides a functional SQL query method supporting dynamic variables, which includes the following steps:
s1, transmitting a formula of a functional SQL containing dynamic variables to a script parameter extractor, extracting a parameter head mark, a parameter body mark and parameter body content from the formula by the script parameter extractor, and transmitting the parameter head mark, the parameter body mark and the parameter body content to a script parameter analyzer, wherein the dynamic variables exist in the parameter body content;
as shown in fig. 2, three parameter header marks are defined in the new SQL as ' $ ', ' @ ', ' # ' and two parameter body marks as ' [ ] ', ' { } ' respectively, and the dynamic variable uses ' +|! The form of { variable name }'. The script parameter extractor reads and collects the head marks and the body marks in the novel SQL according to the marks, and then transmits the collected head marks and body mark contents to the script parameter analyzer for the next operation.
S2, as shown in FIG. 3, when the script parameter analyzer receives the content sent by the script parameter extractor, analyzing the parameter body mark, then analyzing the type of the parameter head mark and classifying the dynamic variables in the parameter body content as follows:
(1) When the type of the parameter head mark obtained by analysis is a single parameter value type, namely when the type of the parameter head mark is '$', the number of the values corresponding to the dynamic variables in the parameter body content after representing the parameter head mark is one, the parameter body content is obtained, and the dynamic variable value in the parameter body content is directly set as the variable value input by a user;
(2) When the type of the parameter head mark is analyzed to be a multi-parameter value type (such as an array), when the type of the parameter head mark is '@', the number of the values corresponding to the dynamic variables IN the parameter body content after representing the parameter head mark is a plurality of, the parameter body content is obtained, the variable value input by a user is converted into a static value of an IN mode IN SQL (namely, the dynamic variable value is changed into the static value, the built-IN mark is removed, and the dynamic variable value is changed into an SQL script fragment containing IN IN a database, so that an SQL grammar which can be identified by the database is generated.
(3) When the type of the parameter head mark is the function type through analysis, when the type of the parameter head mark is '#', the value corresponding to the dynamic variable in the parameter body content after representing the parameter head mark is a section of function, the script analyzer does not process the parameter body content, but acquires the parameter body content, and the parameter body content is transferred to the script parameter function executor to execute the section of function and receive the function execution result, and the function execution result is used for replacing the dynamic variable value in the parameter body content; as shown in fig. 4, when the script parameter parser parses that the type of the parameter header flag is # character, the content processing task is handed over to the script parameter function executor, and the script parameter function executor calculates the result value. The script parameter function executor is internally nested with a JavaScript language engine, so that the function can be normally executed as long as the function accords with the grammar standard of the JavaScript language. After the script function executor receives the function execution segment, the dynamic variable name in the function segment is extracted, the code segment translated into the variable mark and the return value in the JavaScript language is woven into the function, and then the code segment is transferred to the JavaScript language engine, the function is executed by the code segment to obtain the return value, and then the return value is transferred to the script parameter analyzer.
All dynamic variable contents in the novel SQL have set variable values according to the parameter head marks and the parameter body marks, and corresponding SQL fragments are generated. That is, the dynamic variables in the sql script fragment containing the dynamic variable flags have been replaced with variable values, and the script parameter parser may pass the resulting sql fragment to the script generator.
The matching parameter header label specifically comprises:
when the parameter head mark is a single parameter value type mark, matching the dynamic variable value into a single parameter value type, wherein the number of the values corresponding to the dynamic variables representing the parameter body content after the parameter head mark is one;
when the parameter head mark is a multi-parameter value type mark, matching the dynamic variable value into a multi-parameter value type, wherein the number of the values corresponding to the dynamic variable representing the parameter body content after the parameter head mark is a plurality of values;
when the parameter head mark is a function type mark, the dynamic variable value is matched into a function type, and the value corresponding to the dynamic variable representing the parameter body content after the parameter head mark is a section of function.
The analysis parameter body mark specifically comprises:
as shown in fig. 3, firstly, judging whether the parameter body mark is of a filling-selecting type or a filling-necessary type, wherein when the parameter body mark is '[' the parameter value is filling-selecting, and when the parameter body mark is '{' the parameter value is filling-necessary;
if the parameter body mark is a filling type mark '[' and if the parameter value is null, skipping the parameter body, and if the parameter value is non-null, acquiring the content of the parameter body so as to perform the classification processing on the dynamic variable;
if the parameter body mark is the filling type mark '{' and the parameter value is null, the processing is abnormal, and if the parameter value is non-null, the content of the parameter body is obtained, so as to perform the classification processing on the dynamic variable.
S3, backfilling the SQL segment received by the script generator into SQL, so as to generate an SQL query language which can be directly executed by the database, and obtaining an SQL query result after the SQL query language is directly executed by the database.
In this embodiment, as a more specific implementation manner, for example, in a large environment where intelligent medical science is vigorously developed, the function of the novel sql of the present invention may be combined with a medical system to perform statistics of electronic cases, and analyze various information such as cure duration, consumption amount, etc. of patients with monthly and annual consultation. Or for patient self-help systems, utilizing the dynamic variables of the new sql to provide patients with more varied queries and more accurate results for the patient to learn about themselves.
For example, when the statistics function of the electronic case is to be realized, a developer writes a new SQL formula as follows: SELECT sum (zxsj) FRomt_case WHERE DEL_FLAG=0 $ [ month: AND monta= | ]! { month } ] $ [ year: AND year= | ]! { year } ].
This example is used to count the cure duration of the month and year of the electronic case, t_case is the case table, zxsj is the field of the cure duration in the table, sum (zxsj) represents the sum of the cure durations. Only the variable value of month is needed to be filled when only the cure time of month is inquired. Only the variable values of the years need to be filled in when only the healing time of the years is queried. The healing time of the designated year and month can be inquired by inputting the month and year at the same time.
The new SQL formula is transferred to a script parameter extractor, the script parameter extractor extracts the content in the parameter head mark and the parameter body mark, the script parameter analyzer carries out the following replacement processing on the dynamic variable according to the type of the parameter head mark obtained by analysis, the input year is replaced by 2020, the month is replaced by 5, namely:
the following is carried out { year = > 2020-! { month = >5.
After parsing and processing by the script parameter parser, all the parameter header flags and parameter body contents of our new SQL have been parsed into corresponding SQL fragments:
year AND year= |! { year } ] = > AND year=2020;
month AND Month= |! { month } ] = > AND monta=5.
The script generator acquires the SQL segment and backfills the SQL segment to generate a query language which can be directly executed by the database, wherein the query language comprises the following components:
SELECT sum(zxsj)FROM t_case WHERE DEL_FLAG＝0 AND month＝ 5 AND year＝2020。
therefore, the obtained result is the same as the original handwriting SQL, no learning cost and no code rewriting exist, and the result processing code of the original system is non-invasive, so that no extra development work is added for developers.
Based on the same inventive concept, the present application also provides a device corresponding to the method in the first embodiment, and details of the second embodiment are described in the following.
Example two
As shown in fig. 6, in this embodiment, a functional SQL query device supporting dynamic variables is provided, which includes:
the script parameter extractor is used for transmitting a formula of the functional SQL containing the dynamic variables to the script parameter extractor, extracting a parameter head mark, a parameter body mark and a parameter body content from the formula by the script parameter extractor and transmitting the parameter head mark, the parameter body mark and the parameter body content to the script parameter analyzer, wherein the dynamic variables exist in the parameter body content;
the script parameter analyzer is used for receiving the content sent by the script parameter extractor, analyzing the parameter body mark, analyzing the type of the parameter head mark and carrying out the following classification treatment on the dynamic variable:
when the type of the parameter head mark obtained through analysis is a single parameter value type, acquiring parameter body content, and directly setting a dynamic variable value in the parameter body content as a variable value input by a user;
when the type of the parameter head mark is a multi-parameter value type through analysis, acquiring parameter body content, and converting a dynamic variable value IN the parameter body content into a static value of an IN mode IN SQL;
when the type of the parameter head mark is the function type, acquiring the parameter body content, transmitting the parameter body content to a script parameter function executor to execute the function, receiving a function execution result, and replacing the dynamic variable value in the parameter body content by the function execution result;
the script parameter analyzer obtains the sql segment corresponding to the formula and transmits the sql segment to the script generator;
and the script generator is used for backfilling the received SQL segment into SQL so as to generate an SQL query language which can be directly executed by the database and provide the SQL query language for the database to execute so as to obtain an SQL query result.
The matching parameter header label specifically comprises:
when the parameter head mark is a single parameter value type mark, matching the dynamic variable value into a single parameter value type, wherein the number of the values corresponding to the dynamic variables representing the parameter body content after the parameter head mark is one;
when the parameter head mark is a multi-parameter value type mark, matching the dynamic variable value into a multi-parameter value type, wherein the number of the values corresponding to the dynamic variable representing the parameter body content after the parameter head mark is a plurality of values;
when the parameter head mark is a function type mark, the dynamic variable value is matched into a function type, and the value corresponding to the dynamic variable representing the parameter body content after the parameter head mark is a section of function.
The analysis parameter body mark specifically comprises:
firstly judging whether the parameter body mark is of a filling type or a filling-necessary type;
if the parameter body mark is a filling type mark, if the parameter value is empty, skipping the parameter body, and not doing any operation, if the parameter value is not empty, acquiring the content of the parameter body, so as to do the classification processing on the dynamic variable;
if the parameter body mark is a filling type mark and the parameter value is null, the processing is abnormal, and if the parameter value is non-null, the content of the parameter body is obtained, so that the classification processing is carried out on the dynamic variable.
Since the device described in the second embodiment of the present invention is a device used for implementing the method in the first embodiment of the present invention, based on the method described in the first embodiment of the present invention, a person skilled in the art can understand the specific structure and the modification of the device, and therefore, the description thereof is omitted herein. All devices used in the method according to the first embodiment of the present invention are within the scope of the present invention.
Based on the same inventive concept, the application provides an electronic device embodiment corresponding to the first embodiment, and the details of the third embodiment are described in detail.
Example III
The present embodiment provides an electronic device, as shown in fig. 7, including a memory, a processor, and a computer program stored in the memory and capable of running on the processor, where the processor executes the computer program to implement any one of the implementation manners of the first embodiment.
Since the electronic device described in this embodiment is a device for implementing the method described in the first embodiment of the present application, those skilled in the art will be able to understand the specific implementation of the electronic device and various modifications thereof based on the method described in the first embodiment of the present application, so how the method described in the embodiment of the present application is implemented in this electronic device will not be described in detail herein. The apparatus used to implement the methods of the embodiments of the present application are within the scope of what is intended to be protected by the present application.
Based on the same inventive concept, the application provides a storage medium corresponding to the first embodiment, and the details of the fourth embodiment are described in detail.
Example IV
The present embodiment provides a computer readable storage medium, as shown in fig. 8, on which a computer program is stored, which when executed by a processor, can implement any implementation of the first embodiment.
The technical scheme provided in the embodiment of the application has at least the following technical effects or advantages: the method has the advantages that the capability expansion is performed on the basis of the original SQL, and the parameter variables, the parameter types and the functions are added to the original SQL, so that the flexibility and the convenience of the SQL are greatly improved. The constant form which cannot be changed by the original SQL is changed. And the novel SQL query result is identical to the original SQL result, the result processing code of the original system is non-invasive, and the extra development work of a developer is not increased.
It will be apparent to those skilled in the art that embodiments of the present invention may be provided as a method, apparatus or system, or a 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 flowchart illustrations and/or block diagrams, and combinations of flows and/or blocks in the flowchart illustrations 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 block or blocks and/or block diagram block or blocks.
While specific embodiments of the invention have been described above, it will be appreciated by those skilled in the art that the specific embodiments described are illustrative only and not intended to limit the scope of the invention, and that equivalent modifications and variations of the invention in light of the spirit of the invention will be covered by the claims of the present invention.
Claims (6)
1. A function type SQL query method supporting dynamic variables is characterized in that: comprising the following steps:
s1, transmitting a formula of a functional SQL containing dynamic variables to a script parameter extractor, extracting a parameter head mark, a parameter body mark and parameter body content from the formula by the script parameter extractor, and transmitting the parameter head mark, the parameter body mark and the parameter body content to a script parameter analyzer, wherein the dynamic variables exist in the parameter body content; the formula of the functional SQL is as follows: parameter header flag + start flag + parameter name +: +SQL script fragment with dynamic variable flag+end flag in parameter body flag;
the parameter header marks comprise $, @, and # wherein $ represents the dynamic variable value in the parameter body as a single item, @ represents the dynamic variable value in the parameter body as a plurality of items, and # represents the dynamic variable value in the parameter block as a function to calculate;
the parameter body marks comprise [ ] and { }, wherein [ ] represents that the dynamic variable in the parameter body is not necessarily filled, { represents that the dynamic variable in the parameter body is necessarily filled, [ and { are the start marks, ] and } are the end marks;
the dynamic variable is marked as-! { };
s2, when the script parameter analyzer receives the content sent by the script parameter extractor, analyzing the parameter body mark, analyzing the type of the parameter head mark and performing the following classification treatment on the dynamic variable value in the parameter body content:
when the type of the parameter head mark obtained through analysis is a single parameter value type, acquiring parameter body content, and directly setting a dynamic variable value in the parameter body content as a variable value input by a user;
when the type of the parameter head mark is the multi-parameter value type by analysis, acquiring parameter body content, and converting a dynamic variable value IN the parameter body content into a static value of an IN mode IN SQL, wherein the static value of the IN mode IN SQL refers to: changing the dynamic variable value into a static value, removing the built-IN mark, and changing the dynamic variable value into an sql script fragment containing IN IN the database, thereby generating an sql grammar which can be identified by the database;
when the type of the parameter head mark is the function type, the parameter body content is obtained, and is transferred to a script parameter function executor to execute the function, the function execution result is received, and the function execution result is used for replacing the dynamic variable value in the parameter body content;
the script parameter analyzer obtains the sql segment corresponding to the formula and transmits the sql segment to the script generator;
the analysis parameter body mark specifically comprises:
firstly judging whether the parameter body mark is of a filling type or a filling-necessary type;
if the parameter body mark is a filling type mark, if the parameter value is empty, skipping the parameter body, and not doing any operation, if the parameter value is not empty, acquiring the content of the parameter body, and doing the classification processing on the dynamic variable;
if the parameter body mark is a filling type mark and the parameter value is null, processing the parameter body mark as abnormal, and if the parameter value is non-null, acquiring the content of the parameter body so as to perform the classification processing on the dynamic variable;
s3, backfilling the SQL segment received by the script generator into SQL, so as to generate an SQL query language which can be directly executed by the database, and obtaining an SQL query result after the SQL query language is directly executed by the database.
2. The method for supporting dynamic variable function type SQL query according to claim 1, wherein: the parameter header flags include:
the single parameter value type mark represents that the number of the values corresponding to the dynamic variables of the parameter body content after the parameter head mark is one;
the multi-parameter value type mark represents that the number of the values corresponding to the dynamic variables of the parameter body content after the parameter head mark is a plurality of values;
the function type mark represents that the value corresponding to the dynamic variable of the parameter body content after the parameter head mark is a section of function.
3. A function type SQL query device supporting dynamic variables is characterized in that: comprising the following steps:
the script parameter extractor is used for transmitting a formula of the functional SQL containing the dynamic variables to the script parameter extractor, extracting a parameter head mark, a parameter body mark and a parameter body content from the formula by the script parameter extractor and transmitting the parameter head mark, the parameter body mark and the parameter body content to the script parameter analyzer, wherein the dynamic variables exist in the parameter body content; the formula of the functional SQL is as follows: parameter header flag + start flag + parameter name +: +SQL script fragment with dynamic variable flag+end flag in parameter body flag;
the parameter header marks comprise $, @, and # wherein $ represents the dynamic variable value in the parameter body as a single item, @ represents the dynamic variable value in the parameter body as a plurality of items, and # represents the dynamic variable value in the parameter block as a function to calculate;
the parameter body marks comprise [ ] and { }, wherein [ ] represents that the dynamic variable in the parameter body is not necessarily filled, { represents that the dynamic variable in the parameter body is necessarily filled, [ and { are the start marks, ] and } are the end marks;
the dynamic variable is marked as-! {}
The script parameter analyzer is used for receiving the content sent by the script parameter extractor, analyzing the parameter body mark, analyzing the type of the parameter head mark and carrying out the following classification treatment on the dynamic variable value in the parameter body content:
when the type of the parameter head mark obtained through analysis is a single parameter value type, acquiring parameter body content, and directly setting a dynamic variable value in the parameter body content as a variable value input by a user;
when the type of the parameter head mark is a multi-parameter value type through analysis, acquiring parameter body content, and converting a dynamic variable value IN the parameter body content into a static value of an IN mode IN SQL; the static value of the IN mode IN the SQL refers to: changing the dynamic variable value into a static value, removing the built-IN mark, and changing the dynamic variable value into an sql script fragment containing IN IN the database, thereby generating an sql grammar which can be identified by the database;
when the type of the parameter head mark is the function type, the parameter body content is obtained, and is transferred to a script parameter function executor to execute the function, the function execution result is received, and the function execution result is used for replacing the dynamic variable value in the parameter body content;
the script parameter analyzer obtains the sql segment corresponding to the formula and transmits the sql segment to the script generator;
the analysis parameter body mark specifically comprises:
firstly judging whether the parameter body mark is of a filling type or a filling-necessary type;
if the parameter body mark is a filling type mark, if the parameter value is empty, skipping the parameter body, and not doing any operation, if the parameter value is not empty, acquiring the content of the parameter body, and doing the classification processing on the dynamic variable;
if the parameter body mark is a filling type mark and the parameter value is null, processing the parameter body mark as abnormal, and if the parameter value is non-null, acquiring the content of the parameter body so as to perform the classification processing on the dynamic variable;
and the script generator is used for backfilling the received SQL segment into SQL so as to generate an SQL query language which can be directly executed by the database and provide the SQL query language for the database to execute so as to obtain an SQL query result.
4. A functional SQL query device supporting dynamic variables according to claim 3, wherein: the parameter header flags include:
the single parameter value type mark represents that the number of the values corresponding to the dynamic variables of the parameter body content after the parameter head mark is one;
the multi-parameter value type mark represents that the number of the values corresponding to the dynamic variables of the parameter body content after the parameter head mark is a plurality of values;
the function type mark represents that the value corresponding to the dynamic variable of the parameter body content after the parameter head mark is a section of function.
5. An electronic device comprising a memory, a processor and a computer program stored on the memory and executable on the processor, characterized in that the processor implements the method of claim 1 or 2 when executing the program.
6. A computer readable storage medium, on which a computer program is stored, characterized in that the program, when being executed by a processor, implements the method according to claim 1 or 2.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010650476.5A CN111984669B (en) | 2020-07-08 | 2020-07-08 | Functional SQL query method, device, equipment and medium supporting dynamic variables |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010650476.5A CN111984669B (en) | 2020-07-08 | 2020-07-08 | Functional SQL query method, device, equipment and medium supporting dynamic variables |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN111984669A CN111984669A (en) | 2020-11-24 |
| CN111984669B true CN111984669B (en) | 2023-06-27 |
Family
ID=73439140
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202010650476.5A Active CN111984669B (en) | 2020-07-08 | 2020-07-08 | Functional SQL query method, device, equipment and medium supporting dynamic variables |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN111984669B (en) |
Families Citing this family (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN112434059B (en) * | 2021-01-26 | 2021-06-22 | 腾讯科技（深圳）有限公司 | Data processing method, data processing device, computer equipment and storage medium |
| CN113111239B (en) * | 2021-04-08 | 2024-03-29 | 北京联创新天科技有限公司 | General database operation method, device and storage medium thereof |
| CN113157726B (en) * | 2021-04-25 | 2024-05-03 | 中化石化销售有限公司 | Database processing method and device |
Citations (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN108365974A (en) * | 2018-01-17 | 2018-08-03 | 南京邮电大学 | A method of it is configured for network element device multi-service automatized script |
| CN111221741A (en) * | 2020-01-17 | 2020-06-02 | 北京工业大学 | Method for automatically generating abnormal unit test based on genetic algorithm and log analysis |
Family Cites Families (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| KR20030013656A (en) * | 2001-08-08 | 2003-02-15 | 한국전자통신연구원 | Dynamic query language tuning system and method |
| CN106802901A (en) * | 2015-11-26 | 2017-06-06 | 北京国双科技有限公司 | The method and device of data base querying |
| CN110175031B (en) * | 2019-04-25 | 2023-08-18 | 平安科技（深圳）有限公司 | Method, device, computer equipment and storage medium for converting SQL language into DSL language |
| CN111078702B (en) * | 2019-12-17 | 2022-08-26 | 南京南瑞继保工程技术有限公司 | SQL sentence classification management and unified query method and device |
-
2020
- 2020-07-08 CN CN202010650476.5A patent/CN111984669B/en active Active
Patent Citations (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN108365974A (en) * | 2018-01-17 | 2018-08-03 | 南京邮电大学 | A method of it is configured for network element device multi-service automatized script |
| CN111221741A (en) * | 2020-01-17 | 2020-06-02 | 北京工业大学 | Method for automatically generating abnormal unit test based on genetic algorithm and log analysis |
Also Published As
| Publication number | Publication date |
|---|---|
| CN111984669A (en) | 2020-11-24 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN111984669B (en) | Functional SQL query method, device, equipment and medium supporting dynamic variables | |
| Watson et al. | On learning meaningful assert statements for unit test cases | |
| Herrmannsdoerfer et al. | Language evolution in practice: The history of GMF | |
| Papadakis et al. | Stavies: A system for information extraction from unknown web data sources through automatic web wrapper generation using clustering techniques | |
| Stevenson et al. | A survey of grammatical inference in software engineering | |
| Bacchelli et al. | Extracting structured data from natural language documents with island parsing | |
| CN110716749B (en) | Code searching method based on functional similarity matching | |
| CN109408821B (en) | Corpus generation method and device, computing equipment and storage medium | |
| Dit et al. | A dataset from change history to support evaluation of software maintenance tasks | |
| Ruan et al. | DeepLink: Recovering issue-commit links based on deep learning | |
| US20050246353A1 (en) | Automated transformation of unstructured data | |
| GB2413664A (en) | Extracting chemical data from text documents | |
| CN115048111B (en) | Code generation method, device, equipment and medium based on metadata | |
| CN107797805B (en) | Code packaging method and device and computer readable storage medium | |
| CN116661855A (en) | Code annotation generation method and device | |
| CN110413882A (en) | Information-pushing method, device and equipment | |
| Seiler et al. | Comparing traceability through information retrieval, commits, interaction logs, and tags | |
| CN117076742A (en) | Data blood edge tracking method and device and electronic equipment | |
| Basten et al. | Ambidexter: Practical ambiguity detection | |
| Krebber et al. | Efficient pattern matching in Python | |
| Wen et al. | Code similarity detection using ast and textual information | |
| Szalontai et al. | Detecting and fixing nonidiomatic snippets in python source code with deep learning | |
| CN104239475A (en) | Method and device for analyzing time series data | |
| CN107577476A (en) | A kind of Android system source code difference analysis method, server and medium based on Module Division | |
| CN114201376A (en) | Log analysis method and device based on artificial intelligence, terminal equipment and medium |
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 |