A method and system for performing nested cursor query using cursor expressions

By recognizing the subquery statement in the cursor expression as a generalized query block and converting it into an independent query control block during the execution stage, the dependence problem between subquery and main query in the cursor expression is solved, and independent nested cursors are realized Query and efficient query results avoid information loss.

CN118410061BActive Publication Date: 2024-11-12JIANGSU HUAKU DATA TECH CO LTD +1
View PDF 0 Cites 0 Cited by

Patent Information

Application Number
CN202410888897.X
Authority / Receiving Office
CN · China
Patent Type
Patents(China)
Current Assignee / Owner
Filing Date
2024-07-04
Publication Date
2024-11-12
Estimated Expiration
2044-07-04

AI Technical Summary

Technical Problem

When processing cursor expressions, there is a dependency between the subquery and the main query, and independent nested cursor query cannot be performed at the same time. The optimization stage may cause the subquery-related information to be optimized and disappeared, resulting in the failure of nested cursor query. .

Method used

Define the subquery statement in the cursor expression as a generalized query block, temporarily store it and convert it into a query control block during the execution stage, and generate multiple independent cursor variables through the optimization module to avoid the loss of information in the optimization stage, and allow customers to The nested cursor query operation is performed on the end.

Benefits of technology

Ensure that the generated cursor variables exist independently, avoid information loss, improve query efficiency and accuracy, and do not require additional adjustments to optimization modules, and support efficient nested cursor queries.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN118410061B_ABST
    Figure CN118410061B_ABST
Patent Text Reader

Abstract

The present invention relates to the field of electronic digital data processing technology, and in particular to a method and system for performing nested cursor query using cursor expressions, wherein the method comprises the following steps: a syntax analysis module identifies a subquery statement as a generalized query block, and identifies a cursor CURSOR keyword and the generalized query block as a whole as a cursor expression; a storage module performs storage; a structured query language expression generation module converts the stored address information into a structured query language expression; an execution module converts the generalized query block in the cursor expression into a query control block; an optimization module optimizes the query control block to obtain an optimized query control block; a cursor variable generation module generates multiple independent cursor variables; and a client performs nested cursor query according to multiple independent cursor variables. The query results of the method and system provided by the present invention are accurate and reliable, and the query efficiency is relatively high.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] The present invention relates to the technical field of electronic digital data processing, and in particular to a method and system for performing nested cursor query using cursor expressions. Background Art

[0002] Cursor expression (CURSOR EXPRESSION) is a syntax form in structured query language (SQL). Its syntax form is CURSOR(...). The content in the brackets is a subquery statement. The structure of the CURSOR keyword followed by parentheses is used to generate a cursor variable. The cursor expression can be used as a projection column of a query statement or as a parameter of a function. There are many forms of expressions in structured query language, such as numeric expressions, character expressions, function expressions, etc. These expressions are collectively referred to as structured query language expressions. The existing technology for processing structured query language expressions usually has the following steps:

[0003] 1. Syntax analysis phase: Parse the SQL statement structure to ensure that it complies with grammatical rules.

[0004] 2. Translation analysis stage: further analyze the data objects involved in the query, such as tables, columns, data types, constraints, etc., as well as the semantic meaning of the query. This stage focuses on whether the semantics of the query are correct, such as whether the queried columns exist in the database, whether the relationship between the columns and tables is correct, whether the constraints are met, etc.

[0005] 3. Structured query language expression generation stage: Generate different types of expressions into structured query language expressions to facilitate the subsequent optimization module and executor operations. For cursor expressions, a query control block (Control Block) is usually generated by the subquery statement in the brackets, and then this query control block is sent to the structured query language expression to generate the structured query language expression of the cursor expression. Subsequent operations are performed based on this structured query language expression.

[0006] 4. Optimization phase: The optimization module considers various factors, including indexes, connection types, sorting methods, etc., and tries to find the most efficient execution plan to reduce resource consumption and improve performance when executing queries.

[0007] 5. Execution phase: The execution module makes different execution plans based on different structured query language expressions and generates execution results. According to the number of data rows in the query table, the execution plan is executed once for each row of data, and then all execution results are sent to the client. The next step is performed according to the instructions returned by the client. The execution phase is completed after receiving the end instruction sent by the client.

[0008] The existing processing method has the following defects: First, the subquery statement in the parentheses of the cursor expression is converted into a query control block in the structured query language expression generation stage, so the query operation on the main query is performed by the same query control block in the execution stage. There is no problem when the main query statement is executed as a SQL statement alone, but when the main query is opened as a cursor, its projection column is a cursor expression, and the cursor variables generated after execution are not independent. Because these cursor variables are generated by the same query control block to perform query operations on the main query, there is a dependency relationship between them, and these cursor variables cannot be independently nested cursor queries at the same time, and can only be queried one by one.

[0009] Second, the query control block will be optimized during the optimization phase. Similarly, the query control block generated by the subquery statement in the cursor expression brackets will also be optimized during the optimization phase. This optimization behavior will optimize the associated queries in the subquery that are related to the main query together. However, since the cursor variables generated by the cursor expression need to be used for the nested cursor query of the main query in the later stage, this optimization behavior has certain risks. When the query conditions are more complex, some information related to the main query may be optimized away, causing the nested cursor query to fail. In order to avoid this risk, it is necessary to further adjust the optimization module for the query control block generated by the subquery in the cursor expression brackets. Summary of the invention

[0010] The technical problem to be solved by the present invention is to provide a method and system for performing nested cursor query using cursor expressions. First, a subquery statement in a cursor expression is defined as a generalized query block so that an optimization module cannot recognize it, and the cursor expression is temporarily saved until it is taken out and converted into a query control block in the execution stage, and then optimized by the optimization module. A cursor variable generation module is used to generate multiple independent cursor variables and then transmitted to a client, which performs a nested cursor query operation on the variables. This avoids the risk of optimizing away information related to the subquery and the main query in the cursor expression during the optimization stage, does not require additional adjustments to the optimization module for the cursor expression, and can ensure that the generated cursor variables are independent, so that independent nested cursor queries can be performed in subsequent operations.

[0011] The present invention is achieved through the following technical solutions:

[0012] A method for performing nested cursor query using a cursor expression comprises the following steps:

[0013] S1: Syntax analysis stage: The syntax analysis module identifies the cursor CURSOR keyword and the subquery statement at the projection column or function parameter position of the query statement, identifies the subquery statement as a generalized query block, identifies the cursor CURSOR keyword and the generalized query block as a cursor expression, and sends the cursor expression to the storage module;

[0014] S2: Storage stage: The storage module stores the cursor expression and sends the stored address information to the structured query language expression generation module;

[0015] S3: structured query language expression generation stage: the structured query language expression generation module converts the stored address information into a structured query language expression, and sends the structured query language expression to the execution module;

[0016] S4: Execution phase: The execution module extracts the cursor expression from the storage module according to the address information provided by the structured query language expression, converts the generalized query block in the cursor expression into a query control block and transmits it to the optimization module;

[0017] S5: Optimization stage: The optimization module optimizes the query control block to obtain the optimized query control block and transmits it to the cursor variable generation module;

[0018] S6: cursor variable generation stage: the cursor variable generation module generates multiple independent cursor variables according to the optimized query control block, the number of data rows of the query object and the column information of each row, and sends the corresponding multiple independent cursor variables to the client;

[0019] S7: Query phase: The client performs nested cursor query based on the corresponding multiple independent cursor variables.

[0020] Furthermore, the query phase also includes a cleanup phase. After the client completes the nested cursor query, it feeds back information to the cursor variable generation module and the storage module. The cursor variable generation module releases all cursor variables, and the storage module clears the stored data of all cursor expressions.

[0021] Furthermore, the cursor variable generation module executes steps S4 to S6 once for each row of data in the query object according to the number of data rows in the query object, and finally generates a plurality of independent cursor variables corresponding to the number of data rows in the query object and sends them to the client.

[0022] Furthermore, the syntax analysis stage also includes a translation analysis stage. The syntax analysis module sends the cursor expression to the translation analysis module. The translation analysis module analyzes whether the data objects, data types and constraints involved in the generalized query block in the cursor expression are correct. If they are correct, the next stage of operation is entered. If not, the operation is stopped.

[0023] A system for performing nested cursor query using cursor expressions, used to execute any of the above methods for performing nested cursor query using cursor expressions, comprising a syntax analysis module, a storage module, a structured query language expression generation module, an execution module, an optimization module, a cursor variable generation module and a client;

[0024] The syntax analysis module is used to identify the cursor CURSOR keyword and the sub-query statement, identify the sub-query statement as a generalized query block, identify the cursor CURSOR keyword and the generalized query block as a cursor expression, and send the cursor expression to the storage module;

[0025] The storage module is used to store the cursor expression and send the stored address information to the structured query language expression generation module;

[0026] The structured query language expression generation module is used to convert the stored address information into a structured query language expression and send the structured query language expression to the execution module;

[0027] The execution module is used to extract the cursor expression from the storage module according to the address information provided by the structured query language expression, convert the generalized query block in the cursor expression into a query control block and transmit it to the optimization module;

[0028] The optimization module is used to optimize the query control block to obtain the optimized query control block and then transmit it to the cursor variable generation module;

[0029] The cursor variable generation module generates a plurality of independent cursor variables according to the optimized query control block, the number of data rows of the query object and the column information of each row, and sends the corresponding plurality of independent cursor variables to the client;

[0030] The client is used to perform nested cursor query according to corresponding cursor variables.

[0031] Furthermore, a system for performing nested cursor queries using cursor expressions also includes a language translation analysis module, which is used to analyze whether the data objects, data types and constraints involved in the generalized query block in the cursor expression are correct. If they are correct, the next stage of operation is entered; if not, the operation is stopped.

[0032] Beneficial effects of the invention:

[0033] The present invention stores the cursor expression after the subquery statement in the parentheses of the cursor expression is identified as a generalized query block in the syntax analysis stage, and performs optimization between the execution stage and the cursor variable generation stage. Since the query operation on the query object is being executed at this time, the generated query control block will contain relevant information of the query object. In the optimization process, the information related to the query object in the subquery will not be optimized out, the query result is accurate and reliable, and there is no need to perform additional adjustment on the optimization module for the cursor expression in the later stage.

[0034] In addition, in the execution phase, the present invention obtains a generalized query block according to the storage location information provided in the structured query language expression generated by the cursor expression, converts the query block into a query control block, and then optimizes the query control block, performs a query operation on the query object, generates a cursor variable, and executes this execution operation once for each row in the table of the query object. Therefore, it can be ensured that the cursor variable generated for each row of data is independent, and independent nested cursor queries can be performed in subsequent operations, and the query efficiency is relatively high. BRIEF DESCRIPTION OF THE DRAWINGS

[0035] Figure 1 It is a schematic diagram of the process of the present invention.

[0036] Figure 2 It is a schematic diagram of the system of the present invention. DETAILED DESCRIPTION

[0037] A method for nested cursor query using cursor expressions, the flow chart of which is as follows Figure 1 As shown, the specific steps include:

[0038] S1: Syntax analysis stage: The syntax analysis module identifies the cursor CURSOR keyword and the subquery statement at the projection column or function parameter position of the query statement, identifies the subquery statement as a generalized query block, identifies the cursor CURSOR keyword and the generalized query block as a cursor expression, and sends the cursor expression to the storage module;

[0039] The syntax analysis module first identifies the cursor CURSOR keyword, and then needs to recognize that it is followed by a parenthesis structure, in which is a subquery statement, that is, the CURSOR (...) form, and recognizes the subquery statement as a generalized query block, and recognizes the cursor CURSOR keyword and the generalized query block as a cursor expression as a whole.

[0040] Here, the generalized query block refers to a query block that includes a tree-linked list structure of each element in the query, so that it can be stored in subsequent operations, while according to the prior art, if a subquery statement, i.e., a complete SQL query statement, is identified, the storage operation cannot be performed.

[0041] The syntax analysis module can also first send the cursor expression to the language translation analysis module, which analyzes whether the data objects, data types and constraints involved in the generalized query block in the cursor expression are correct. If they are correct, the cursor expression is sent to the storage module. If not, the operation is stopped. This can avoid invalid operations. Here, judging whether the data object, data type and constraint conditions are correct mainly refers to whether the data object, i.e., the column queried by the query object, exists in the database, whether the relationship between the query object column and the table is correct, whether the constraint conditions meet the requirements, etc.

[0042] S2: Storage stage: The storage module stores the cursor expression and sends the stored address information to the structured query language expression generation module;

[0043] S3: Structured query language expression generation stage: The structured query language expression generation module converts the stored address information into a structured query language expression and sends the structured query language expression to the execution module; the structured query language expression here only contains the address information of the query block in the cursor expression in the storage module.

[0044] S4: Execution phase: The execution module extracts the cursor expression from the storage module according to the address information provided by the structured query language expression, converts the generalized query block in the cursor expression into a query control block and transmits it to the optimization module;

[0045] S5: Optimization stage: The optimization module optimizes the query control block to obtain the optimized query control block and transmits it to the cursor variable generation module;

[0046] Here, the optimization module optimizes the query control block mainly by performing constant folding on the query control block: calculating the value of the constant expression to avoid repeated calculations; condition merging: merging repeated or similar conditions to reduce the number and complexity of conditions; connection optimization: selecting the best connection order and connection method to reduce the number and complexity of connection operations; subquery optimization: optimizing the execution method of subqueries, eliminating redundant subqueries, expanding subqueries as part of the main query, and reducing the query level.

[0047] S6: cursor variable generation stage: the cursor variable generation module generates multiple independent cursor variables according to the optimized query control block, the number of data rows of the query object and the column information of each row, and sends the corresponding multiple independent cursor variables to the client;

[0048] Specifically, the cursor variable generation module may execute steps S4 to S6 once for each row of data in the query object according to the number of data rows in the query object, and finally generate a number of independent cursor variables corresponding to the number of data rows in the query object and send them to the client.

[0049] In the present invention, since the subquery statement in the parentheses of the cursor expression is identified as a generalized query block in the syntax analysis stage, the cursor expression is convenient to store, and optimization is performed between the execution stage and the cursor variable generation stage. Since the query operation on the query object is being performed at this time, the generated query control block will contain relevant information of the query object, and the information related to the query object in the subquery will not be optimized during the optimization process, so the query result is more accurate and reliable, and there is no need to perform additional adjustment on the optimization module for the cursor expression in the later stage.

[0050] In addition, since the present invention obtains a generalized query block according to the storage location information provided in the structured query language expression generated by the cursor expression during the execution phase, converts the query block into a query control block, and then optimizes the query control block, performs a query operation on the query object, generates a cursor variable, and performs this execution operation once for each row in the table of the query object, it can be ensured that the cursor variable generated for each row of data is independent, and an independent nested cursor query can be performed in subsequent operations, while the multiple cursor variables generated by the prior art can only be queried in sequence, and after querying one cursor variable, the next cursor variable can be queried, and each query of the cursor variable can obtain a row of results, and after querying all the rows contained in the cursor variable, the query is called completed. The multiple cursor variables generated by the present invention are independent of each other, can be queried in any order, and do not need to query one after the query is completed, so the query efficiency is relatively high.

[0051] S7: Query phase: The client performs nested cursor query based on the corresponding multiple independent cursor variables.

[0052] Furthermore, after the query phase, there is also a cleanup phase. After the client completes the nested cursor query, it feeds back information to the cursor variable generation module and the storage module. The cursor variable generation module releases all cursor variables, and the storage module clears the stored data of all cursor expressions. In this way, timely cleaning of the cursor variable generation module and the storage module can prevent a large amount of memory from being occupied, further speeding up the query speed.

[0053] A system for performing nested cursor query using cursor expressions, used to execute any of the above methods for performing nested cursor query using cursor expressions, and its system diagram is shown as follows: Figure 2 As shown, it includes a syntax analysis module, a storage module, a structured query language expression generation module, an execution module, an optimization module, a cursor variable generation module and a client;

[0054] The syntax analysis module is used to identify the cursor CURSOR keyword and the sub-query statement, identify the sub-query statement as a generalized query block, identify the cursor CURSOR keyword and the generalized query block as a cursor expression, and send the cursor expression to the storage module;

[0055] The storage module is used to store the cursor expression and send the stored address information to the structured query language expression generation module;

[0056] The structured query language expression generation module is used to convert the stored address information into a structured query language expression and send the structured query language expression to the execution module;

[0057] The execution module is used to extract the cursor expression from the storage module according to the address information provided by the structured query language expression, convert the generalized query block in the cursor expression into a query control block and transmit it to the optimization module;

[0058] The optimization module is used to optimize the query control block to obtain the optimized query control block and then transmit it to the cursor variable generation module;

[0059] The cursor variable generation module generates a plurality of independent cursor variables according to the optimized query control block, the number of data rows of the query object and the column information of each row, and sends the corresponding plurality of independent cursor variables to the client;

[0060] The client is used to perform nested cursor query according to corresponding cursor variables.

[0061] Furthermore, a system for performing nested cursor queries using cursor expressions also includes a language translation analysis module, which is used to analyze whether the data objects, data types and constraints involved in the generalized query block in the cursor expression are correct. If they are correct, the next stage of operation is entered; if not, the operation is stopped.

[0062] Specific implementation method: Assume that the SQL statement containing the cursor expression is as follows:

[0063] SELECT CURSOR(SELECT T2.C1 FROM T2 WHERE T2.C1=T1.C1) FROM T1 ORDERBY T1.C1;

[0064] In the syntax analysis phase, the syntax analysis module recognizes the SELECT T2.C1 FROM T2 WHERET2.C1=T1.C1 in the parentheses as a generalized query block, and recognizes the entire CURSOR (SELECT T2.C1 FROM T2 WHERE T2.C1=T1.C1) as a cursor expression, and transmits it to the storage module.

[0065] In the storage phase, the storage module stores the cursor expression with the generalized query block SELECT T2.C1 FROM T2 WHERE T2.C1=T1.C1 to obtain the storage address information;

[0066] In the structured query language expression generation stage, the structured query language expression generation module converts the stored address information into a structured query language expression;

[0067] The prior art does not recognize SELECT T2.C1 FROM T2 WHERE T2.C1=T1.C1 in the parentheses as a generalized query block, but directly generates different types of query expressions into structured query language expressions and then optimizes them. Since this embodiment contains complex conditions, including associated queries and sorted queries, there is a problem of optimizing away the effective information of T2.C1=T1.C1 in the associated query, resulting in the inability to use the generated cursor variable to perform nested cursor query operations later.

[0068] The present invention does not perform optimization here, but first enters the execution stage, and copies a query block identical to the query block in the cursor expression from the storage information in the corresponding address of the storage module according to the structured query language expression, that is, SELECT T2.C1 FROM T2 WHERE T2.C1=T1.C1, converts this query block into a query control block, and then optimizes this query control block through the optimization module. Since the query operation on the query object is being executed at this time, the generated query control block will contain relevant information of the query object, and the information related to the query object in the subquery will not be optimized during the optimization process, so the query result is more accurate and reliable, and there is no need to make additional adjustments to the optimization module for the cursor expression in the later stage.

[0069] Then enter the cursor variable generation stage. The cursor variable generation module performs query operations on the query object, that is, the main query table T1, according to the optimized query control block, the number of data rows of the query object, and the column information of each row, generates multiple independent cursor variables, and sends the corresponding multiple independent cursor variables to the client. The number of generated cursor variables corresponds to the number of data rows in the T1 table. Multiple cursor variables are independent of each other and can be queried in any order. There is no need to query one after another, so the query efficiency is relatively high.

[0070] The cursor expression query control block used in the conventional method is a fixed query control block generated in the structured query language expression generation stage. Therefore, when repeated operations are performed on each row, a non-independent cursor variable will be generated. These cursor variables are all generated based on the same query control block and can only be queried in sequence. After querying one cursor variable, the next cursor variable will be queried. Each query on the cursor variable can get a row of results. The query is completed only after all the rows contained in the cursor variable are queried.

[0071] In summary, the method and system for performing nested cursor queries using cursor expressions provided by the present invention avoid the risk of optimizing away information related to subqueries and main queries in cursor expressions during the optimization phase. The query results are accurate and reliable, and there is no need to make additional adjustments to the optimization module for the cursor expression in the later stage. It also ensures that the cursor variables generated for each row of data are independent, so that independent nested cursor queries can be performed in subsequent operations, and the query efficiency is relatively high.

[0072] The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. For those skilled in the art, the present invention may have various modifications and variations. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present invention shall be included in the protection scope of the present invention.

Claims

1. A method for performing nested cursor query using cursor expressions, characterized in that: The steps include: S1: Syntax analysis stage: The syntax analysis module identifies the cursor CURSOR keyword and the subquery statement at the projection column or function parameter position of the query statement, identifies the subquery statement as a generalized query block, identifies the cursor CURSOR keyword and the generalized query block as a cursor expression, and sends the cursor expression to the storage module; S2: Storage stage: The storage module stores the cursor expression and sends the stored address information to the structured query language expression generation module; S3: structured query language expression generation stage: the structured query language expression generation module converts the stored address information into a structured query language expression, and sends the structured query language expression to the execution module; S4: Execution phase: The execution module extracts the cursor expression from the storage module according to the address information provided by the structured query language expression, converts the generalized query block in the cursor expression into a query control block and transmits it to the optimization module; S5: Optimization stage: the optimization module optimizes the query control block to obtain the optimized query control block and transmits it to the cursor variable generation module; S6: cursor variable generation stage: the cursor variable generation module generates multiple independent cursor variables according to the optimized query control block, the number of data rows of the query object and the column information of each row, and sends the corresponding multiple independent cursor variables to the client; S7: Query phase: The client performs nested cursor query based on the corresponding multiple independent cursor variables.

2. A method for performing nested cursor query using cursor expressions according to claim 1, characterized in that: The query phase also includes a cleanup phase. After the client completes the nested cursor query, it feeds back information to the cursor variable generation module and the storage module. The cursor variable generation module releases all cursor variables, and the storage module clears the stored data of all cursor expressions.

3. A method for performing nested cursor query using cursor expressions according to claim 1, characterized in that: The cursor variable generation module executes steps S4 to S6 once for each row of data in the query object according to the number of data rows in the query object, and finally generates a number of independent cursor variables corresponding to the number of data rows in the query object and sends them to the client.

4. A method for performing nested cursor query using cursor expressions according to claim 1, characterized in that: The syntax analysis stage also includes a translation analysis stage. The syntax analysis module sends the cursor expression to the translation analysis module. The translation analysis module analyzes whether the data objects, data types and constraints involved in the generalized query block in the cursor expression are correct. If they are correct, the next stage of operation is entered. If not, the operation is stopped.

5. A system for performing nested cursor query using cursor expressions, used to execute a method for performing nested cursor query using cursor expressions as claimed in any one of claims 1 to 3, characterized in that: It includes a syntax analysis module, a storage module, a structured query language expression generation module, an execution module, an optimization module, a cursor variable generation module and a client; The syntax analysis module is used to identify the cursor CURSOR keyword and the sub-query statement, identify the sub-query statement as a generalized query block, identify the cursor CURSOR keyword and the generalized query block as a cursor expression, and send the cursor expression to the storage module; The storage module is used to store the cursor expression and send the stored address information to the structured query language expression generation module; The structured query language expression generation module is used to convert the stored address information into a structured query language expression and send the structured query language expression to the execution module; The execution module is used to extract the cursor expression from the storage module according to the address information provided by the structured query language expression, convert the generalized query block in the cursor expression into a query control block and transmit it to the optimization module; The optimization module is used to optimize the query control block to obtain the optimized query control block and then transmit it to the cursor variable generation module; The cursor variable generation module generates a plurality of independent cursor variables according to the optimized query control block, the number of data rows of the query object and the column information of each row, and sends the corresponding plurality of independent cursor variables to the client; The client is used to perform nested cursor query according to corresponding cursor variables.

6. A system for performing nested cursor query using cursor expressions according to claim 5, characterized in that: It also includes a language translation analysis module, which is used to analyze whether the data objects, data types and constraints involved in the generalized query block in the cursor expression are correct. If they are correct, the next stage of operation will be entered; if not, the operation will be stopped.