WO2022002275A1 - 分布式存储过程的运行方法、电子设备及存储介质 - Google Patents

分布式存储过程的运行方法、电子设备及存储介质 Download PDF

Info

Publication number
WO2022002275A1
WO2022002275A1 PCT/CN2021/104518 CN2021104518W WO2022002275A1 WO 2022002275 A1 WO2022002275 A1 WO 2022002275A1 CN 2021104518 W CN2021104518 W CN 2021104518W WO 2022002275 A1 WO2022002275 A1 WO 2022002275A1
Authority
WO
WIPO (PCT)
Prior art keywords
sql
stored procedure
engine
instruction
cursor
Prior art date
Application number
PCT/CN2021/104518
Other languages
English (en)
French (fr)
Inventor
李金虎
赵培
俞义方
Original Assignee
中兴通讯股份有限公司
Priority date (The priority date 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 date listed.)
Filing date
Publication date
Application filed by 中兴通讯股份有限公司 filed Critical 中兴通讯股份有限公司
Publication of WO2022002275A1 publication Critical patent/WO2022002275A1/zh

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
    • G06F16/2433—Query languages
    • 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/27—Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F9/00—Arrangements for program control, e.g. control units
    • G06F9/06—Arrangements for program control, e.g. control units using stored programs, i.e. using an internal store of processing equipment to receive or retain programs
    • G06F9/30—Arrangements for executing machine instructions, e.g. instruction decode
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F9/00—Arrangements for program control, e.g. control units
    • G06F9/06—Arrangements for program control, e.g. control units using stored programs, i.e. using an internal store of processing equipment to receive or retain programs
    • G06F9/30—Arrangements for executing machine instructions, e.g. instruction decode
    • G06F9/30003—Arrangements for executing specific machine instructions

Definitions

  • the embodiments of the present application relate to the field of databases, and in particular, to a method for operating a distributed storage process, an electronic device, and a storage medium.
  • the inventor found that in the process of executing the stored procedure, there were many message interactions between the Structured Query Language (SQL) processing engine and the stored procedure engine, which affected the execution storage. efficiency of the process.
  • SQL Structured Query Language
  • An embodiment of the present application provides a method for running a distributed stored procedure, including: acquiring a first structured query language SQL instruction in the stored procedure; judging the type of the SQL instruction in the first SQL instruction, and obtaining a first judgment result; If the first judgment result indicates that a non-cursor query-type SQL instruction exists in the first SQL instruction, a first processing request is generated, and the first processing request includes a non-cursor query-type SQL instruction and return indication information for returning result set information; The SQL processing engine sends a first processing request, so that the SQL processing engine returns the result status and result set information of the non-cursor query SQL instruction to the stored procedure engine.
  • Embodiments of the present application also provide a method for running a distributed stored procedure, including: receiving a request message sent by a stored procedure engine; judging whether there is a first processing request in the request message, and obtaining a fourth judgment result, the first processing request Including: non-cursor query-type SQL instructions and return indication information for returning result set information If the fourth judgment result indicates that there is a first processing request, return the result status and result set information of the non-cursor query-type SQL instructions to the stored procedure engine .
  • Embodiments of the present application further provide an electronic device, comprising: at least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores instructions executable by the at least one processor, and the instructions are executed by at least one processor.
  • One processor executes, so that at least one processor can execute the above-mentioned running method of the distributed stored procedure applied to the stored procedure engine, or execute the running method of the distributed stored procedure applied to the SQL processing engine.
  • Embodiments of the present application further provide a computer-readable storage medium storing a computer program, and when the computer program is executed by a processor, the above-mentioned method for running a distributed stored procedure applied to a stored procedure engine is implemented, or the execution method is applied to a SQL How to run the distributed stored procedure of the processing engine.
  • FIG. 1 is a schematic diagram of the architecture of a distributed database according to the related art
  • Fig. 2 is according to the interactive schematic diagram of stored procedure operation in the related art
  • FIG. 3 is a flowchart of a method for operating a distributed stored procedure according to the first embodiment of the present application
  • FIG. 4 is a flowchart of a method for operating a distributed stored procedure according to a second embodiment of the present application
  • FIG. 5 is an interactive schematic diagram of a stored procedure for running a non-cursor query-like SQL instruction according to the third embodiment of the present application;
  • FIG. 6 is a schematic diagram of interaction according to the stored procedure of the query-like SQL instruction in the execution cursor in the third embodiment of the present application;
  • FIG. 7 is an interactive schematic diagram of a stored procedure for running a query-like SQL instruction in a cursor according to a third embodiment of the present application.
  • FIG. 8 is a schematic diagram of obtaining the first SQL instruction in the method for running a distributed stored procedure according to the third embodiment of the present application.
  • FIG. 9 is a schematic diagram of error information processing in a method for running a distributed stored procedure according to a third embodiment of the present application.
  • FIG. 10 is a schematic structural diagram of an electronic device according to a fourth embodiment of the present application.
  • Stored Procedure is a set of statements stored in the database system that can complete specific functions. After the stored procedure is compiled for the first time, it does not need to be compiled again in subsequent calls. The user can specify the name of the stored procedure and give Run the stored procedure with parameters (if the stored procedure has parameters).
  • a distributed database system usually includes a stored procedure engine and a Structured Query Language (SQL) processing engine. The stored procedure engine is used to run the stored procedure, and the SQL processing engine is used to execute SQL instructions in the stored procedure.
  • SQL Structured Query Language
  • Figure 1 is a schematic diagram of the architecture of a distributed database.
  • the distributed database includes multiple storage nodes, such as DB_1, DB_2...DB_n.
  • the execution request for executing the stored procedure initiated by the user is sent to the SQL processing engine, which is assisted by the SQL processing engine, the stored procedure engine, the data storage node and the metadata management to complete the execution of the stored procedure.
  • the metadata management is used to manage the metadata , where the data storage node may be a storage device. The following describes the interaction process between the current SQL processing engine and the stored procedure engine in conjunction with FIG. 2 .
  • the SQL processing engine executes step S01: sending the request for executing the stored procedure to the stored procedure engine.
  • the stored procedure engine executes step S02: the stored procedure engine executes the stored procedure, and detects the SQL instruction in the stored procedure.
  • the stored procedure engine executes step S03: reconstructs a complete SQL instruction according to the detected SQL instruction, which can be sent to the SQL processing engine in the form of a message, and waits for the execution result of the SQL processing engine executing the SQL instruction.
  • the SQL processing engine executes step S04: receiving the SQL instruction sent by the stored procedure engine.
  • the SQL processing engine executes step S05: the SQL processing engine completes the execution of the received SQL statement, and sends the execution result status as a response to the SQL processing engine.
  • the stored procedure engine executes step S06: after receiving the SQL execution result status, it is judged whether it is necessary to request the SQL processing engine for a result set of one row according to the result status, and if so, the request is sent.
  • the SQL processing engine executes step S07: the SQL processing engine sends the result set of a row to the stored procedure engine as a response. If the stored procedure engine needs multiple rows of result sets, the above-mentioned obtaining steps are performed multiple times, that is, steps S06 and S07 are executed every time a row of result sets needs to be obtained.
  • the stored procedure engine executes step S08: after executing the result set of the current SQL instruction, the SQL processing engine is notified to release the corresponding result set.
  • the SQL processing engine repeatedly executes the next SQL instruction, that is, steps S03 to S08 are repeated.
  • the stored procedure engine executes step S09: after executing the stored procedure, the output parameters and the input and output parameters are spliced into a message, and sent to the SQL processing engine as a final response.
  • the SQL processing engine returns the final result to the user.
  • the result set of SQL is transferred from the SQL processing engine to the stored procedure engine. If there are many interactions between the SQL processing engine and the stored procedure engine, frequent network access will occur.
  • the stored procedure engine executes a result set of one row, it needs to send a fetch request to the SQL processing engine, and the result set returned by the SQL processing engine, if a result set of multiple rows is required, the SQL processing engine and There are many interactions between stored procedure engines, which affects the efficiency of executing stored procedures.
  • the purpose of the embodiments of the present application is to provide a method for running a distributed storage process, an electronic device and a storage medium, so as to improve the operation efficiency of the distributed storage process.
  • the first embodiment of the present application relates to a method for running a distributed stored procedure, which is applied to a stored procedure engine, and the process is shown in FIG. 3 .
  • Step 101 Acquire the first structured query language SQL instruction in the stored procedure.
  • Step 102 Determine the type of the SQL instruction in the first SQL instruction, and obtain a first determination result.
  • Step 103 If the first judgment result indicates that a non-cursor query-type SQL instruction exists in the first SQL instruction, a first processing request is generated, and the first processing request includes a non-cursor query-type SQL instruction and a return instruction for returning result set information information.
  • Step 104 Send a first processing request to the SQL processing engine, so that the SQL processing engine returns the result status and result set information of the non-cursor query-type SQL instruction to the stored procedure engine.
  • the embodiment of the present application judges the type of the first SQL instruction after acquiring the first SQL instruction in the stored procedure.
  • the SQL processing engine processes the non-cursor query SQL instruction according to the first processing request, and the SQL processing engine directly returns the processed result status and result set to the stored procedure engine,
  • the stored procedure engine can process the next first SQL instruction; since the stored procedure engine does not need to obtain the result set row by row from the SQL processing engine after receiving the result status, it reduces the time between the stored procedure engine and the SQL processing engine.
  • the number of interactions reduces the processing speed of non-cursor query-like SQL instructions and improves the running efficiency of the entire stored procedure.
  • the second embodiment of the present application relates to a method for running a distributed stored procedure.
  • the method for running a distributed stored procedure is applied to a SQL processing engine, and the process is shown in Figure 4:
  • Step 201 Receive a request message sent by the stored procedure engine.
  • Step 202 Determine whether there is a first processing request in the request message, and obtain a fourth determination result, where the first processing request includes: a non-cursor query-type SQL instruction and return indication information for returning result set information.
  • Step 203 If the fourth judgment result indicates that the first processing request exists, return the result status and result set information of the non-cursor query-like SQL instruction to the stored procedure engine.
  • the SQL processing engine After the SQL processing engine has processed the specified SQL instruction in the request message, it can determine whether there is a first processing request in the request message, and if there is a first processing request, send a request to the stored procedure
  • the engine returns the result status and result set information obtained by processing. Since there is no need to wait for the request to obtain the result set in the next interactive process of the stored procedure engine, the number of interactions with the stored procedure is reduced, and the result set is not required to be returned to the stored procedure engine line by line. The number of interactions with the stored procedure engine is further reduced, and the efficiency of executing the stored procedure is improved.
  • the third embodiment will introduce the method for running the distributed stored procedure in detail.
  • the running process is shown in FIG. 5 .
  • the SQL processing engine executes step S11: sending the request for executing the stored procedure to the stored procedure engine.
  • the client of the user initiates an execution request for executing the stored procedure to the SQL processing engine, and the SQL processing engine sends the request for executing the stored procedure to the stored procedure engine when processing the execution request.
  • the stored procedure engine executes step S12: acquiring the first structured query language SQL instruction in the stored procedure.
  • the stored procedure engine executes the stored procedure, it acquires and executes an SQL instruction in the stored procedure, and the SQL instruction may be used as the first SQL instruction, or multiple SQL instructions obtained in the execution of the stored procedure may be used as the first SQL instruction.
  • the first SQL instruction that is to say, the first SQL instruction may include one SQL instruction, or may include at least two SQL instructions.
  • the stored procedure engine executes step S13: judging the type of the SQL instruction in the first SQL instruction, and obtaining a first judgment result.
  • the types of SQL instructions in this example may include: conventional SQL instructions, non-cursor query-type SQL, query-type SQL instructions in a cursor, and conventional SQL instructions in a cursor.
  • the result after the SQL processing engine executes the non-cursor query SQL instruction includes three types: success, failure and result set.
  • the stored procedure engine needs to execute the result set returned by the SQL processing engine.
  • the returned result set can be one row or 0 rows.
  • the stored procedure engine needs to delay the execution of the result set.
  • a row in the result set is also required when executing a cursor's query-like SQL instruction in a stored procedure.
  • the result set includes 0 rows, 1 row, and multiple rows. Regular SQL commands do not require a result set, but require the result status returned by SQL, such as successful results and failed results.
  • the following processing is performed for each SQL command in the first SQL command: according to the SQL command and context information, it is determined whether the SQL command is a non-cursor query type SQL command; In the cursor, the SQL instruction belongs to the non-cursor type. Continue to judge whether the SQL instruction belongs to the query type according to the keyword of the SQL instruction. After two judgments, it can be determined whether the SQL instruction belongs to the non-cursor query type SQL instruction. It can be understood that other methods can also be used to make judgments, which will not be listed here.
  • the stored procedure engine executes step S14: if the first judgment result indicates that a non-cursor query-type SQL instruction exists in the first SQL instruction, a first processing request is generated, and the first processing request includes a non-cursor query-type SQL instruction and a returned result set The return of the message indicates the message.
  • the generated first processing request includes return indication information for returning the result set information.
  • the first processing request may carry the information of "whether a result set is included in the response". If the first judgment result indicates the first SQL If there is a non-cursor query SQL instruction in the instruction, the information of "whether the result set information is included in the response" is true; the true information is the return instruction information; if the first judgment result indicates that the first SQL instruction does not exist For non-cursor query-like SQL commands, the information "whether result set information is included in the response" is false.
  • the result set information may include: all result sets obtained after executing the first SQL instruction, or may be a result set with a specified number of rows.
  • the first processing request may include the non-cursor query-type SQL instruction; if the first SQL instruction has multiple SQL instructions, then the first processing request may also include other types of the first SQL instruction. SQL instruction.
  • the stored procedure engine executes step S15: sending a first processing request to the SQL processing engine, so that the SQL processing engine returns the result status and result set information of the non-cursor query-like SQL instruction to the stored procedure engine.
  • a request message is generated, and the request message may include the first processing request, and may also include other SQL instructions in the first SQL instruction.
  • the SQL processing engine executes step S16: receiving the request message sent by the stored procedure engine.
  • the SQL instruction carried in the request message is acquired, the SQL instruction is processed, and the result state of processing the SQL instruction and the corresponding result set information are acquired.
  • the SQL processing engine executes step S17: judging whether there is a first processing request in the request message, and obtaining a fourth judgment result, where the first processing request includes: a non-cursor query SQL instruction and return indication information for returning result set information.
  • the first processing request carries return indication information. If there is return indication information in the request message, it can be determined that there is a first processing request in the request message. If the "result set information" information is true, the fourth judgment result indicates that the first processing request exists.
  • the SQL processing engine executes step S18: if the fourth judgment result indicates that the first processing request exists, the result state and result set information of the non-cursor query-type SQL instruction are returned to the stored procedure engine.
  • the SQL processing engine obtains the result set of the non-cursor query-type SQL instruction; and determines whether the number of rows in the result set of the non-cursor query-type SQL instruction is greater than 1, If so, an error message is generated. If the number of rows in the result set of the non-cursor query SQL instruction is less than or equal to 1, a new type of status message is constructed, and the new status message contains the result status and result set information of the non-cursor query SQL instruction. ;
  • the result set information can include: all the result sets obtained after executing the first SQL instruction, or the result set with the specified number of rows.
  • the status message is constructed according to the existing method.
  • steps S11 to S18 continue to execute the next SQL instruction until all the instructions of this stored procedure are completed.
  • the stored procedure engine executes step S19: completes the result set of the current SQL instruction, and informs the SQL processing engine to release the corresponding result set.
  • the SQL processing engine repeatedly executes the next first SQL instruction, that is, steps S13 to S18 are repeated.
  • the stored procedure engine executes step S20: sending a message of the execution result of the stored procedure to the SQL processing engine.
  • the stored procedure engine executes the stored procedure, it splices the outgoing parameters and incoming and outgoing parameters into the message, and sends it to the SQL processing engine as the final response; the SQL processing engine returns the final result to the user's client.
  • This embodiment also provides a way of running the stored procedure of the query-like SQL instruction in the cursor.
  • the following results are introduced in Figure 6 and Figure 7.
  • the SQL processing engine executes step S21: sending the request for executing the stored procedure to the stored procedure engine.
  • step S11 Similar to step S11, it is not repeated here.
  • the stored procedure engine executes step S22: acquiring the first structured query language SQL instruction in the stored procedure.
  • the stored procedure engine executes the stored procedure, it obtains and executes an SQL instruction in the stored procedure, and the SQL instruction may be used as the first SQL instruction, or multiple SQL instructions in the execution of the stored procedure may be used as the first SQL instruction, that is to say, the first SQL instruction may include one SQL instruction, or may include at least two SQL instructions.
  • the stored procedure engine executes step S23: judging the type of the SQL instruction in the first SQL instruction, and obtaining a first judgment result.
  • This step is substantially the same as step S13, and will not be repeated here.
  • the stored procedure engine executes step S24: if the first judgment result indicates that there is a query-type SQL instruction in the cursor in the first SQL instruction, a second processing request is sent to the SQL processing engine, and the second processing request includes the query-type SQL instruction in the cursor , for the SQL processing engine to return status messages including the result status of query-like SQL instructions in the cursor.
  • the following processing is performed on the SQL command in the first SQL command: according to the SQL command and context information, it is determined whether the SQL command is a cursor query SQL command; for example, the position of the SQL command can be obtained, if the SQL command is If the SQL command is in the cursor, it belongs to the SQL command of the cursor class. According to the keyword of the SQL command, it is judged whether the SQL command belongs to the query class. After two judgments, it can be determined whether the SQL command belongs to the query class SQL command in the cursor. . It can be understood that the judgment can also be made in other manners, which will not be listed one by one here.
  • a request message may be generated, and the request message may include the second processing request and other SQL instructions in the first SQL instruction.
  • the SQL processing engine executes step S25: receiving the request message sent by the stored procedure engine.
  • the result state of processing the SQL instruction and the corresponding result set can be acquired.
  • the SQL processing engine executes step S26: judging whether there is a first processing request in the request message, and obtaining a fourth judgment result, where the first processing request includes a non-cursor query SQL instruction and return indication information for returning result set information.
  • the first processing request carries return indication information. If there is return indication information in the request message, it can be determined that there is a first processing request in the request message.
  • the "set information" information is false; the fourth judgment result indicates that there is no first processing request. If it is detected that the query SQL instruction included in the cursor is included, the fourth judgment result indicates that there is a second processing request.
  • the second processing request may further include identification information of the second processing request, so that the SQL processing engine can determine whether there is a second processing request according to the identification information.
  • the SQL processing engine executes step S27: if the fourth judgment result indicates that there is a second processing request, and the second processing request includes a query-type SQL instruction in the cursor, obtain the result state of the query-type SQL instruction in the cursor and the capacity information in the result set .
  • the SQL processing engine calculates the total number of rows in the result set or the size of one row of the result set.
  • the capacity information of the result set may include information about the total number of rows of the obtained result set or the size of the result set of one row, and the capacity information of the result set is carried in the status message sent by the SQL processing engine to the stored procedure engine.
  • the SQL processing engine executes step S28: returns a status message to the stored procedure engine, where the status message includes the result status of the query SQL instruction in the cursor and the capacity information in the result set, so that the stored procedure engine can send the result of obtaining n rows to the SQL processing engine Set the first get request.
  • the stored procedure engine executes step S29: judging whether there is capacity information of the result set in the received status message, and obtaining a second judgment result.
  • the stored procedure engine executes step S30: if the second judgment result indicates that the capacity information of the result set exists in the status message, the first obtaining request for obtaining the result set of n rows is sent to the SQL processing engine, so that the SQL processing engine returns the result set of n rows.
  • the result set where n is an integer greater than 1.
  • the value of n can be set by the stored procedure engine according to the total number of rows in the capacity information or the size of a row of result sets, or it can be dynamically set according to memory and network factors at the same time.
  • the SQL processing engine executes step S31: in response to the first obtaining request, a result set of n rows is returned to the stored procedure engine.
  • the result set of n rows is responded to the stored procedure engine; if there are no n rows, the actual row number is responded to result set.
  • the stored procedure engine executes step S32: notifying the SQL processing engine to release the result set.
  • steps S21 to S26 are still executed.
  • the SQL processing engine executes step S27': if the fourth judgment result indicates that there is a second processing request, the second processing request includes the query SQL instruction in the cursor, and sends the result status of the query SQL instruction in the cursor to the stored procedure engine, to For the stored procedure engine to send a second get request to get all result sets.
  • the stored procedure engine may also perform step S28': if the second judgment result indicates that the capacity information of the result set does not exist in the status message, then detect whether there is an SQL processing engine in the device where the stored procedure engine is located, and if so, send the SQL processing engine to the stored procedure engine.
  • the engine sends a second fetch request for fetching all result sets, so that the SQL processing engine returns storage information of all result sets.
  • the stored procedure engine detects whether there is an SQL processing engine on the electronic device where it is located, and if so, it indicates that the stored procedure engine and the SQL processing engine are deployed on the same electronic device.
  • the SQL processing engine executes step S29': write all the result sets of the query-type SQL instructions in the cursor to the storage medium of the device where the SQL processing engine is located to obtain storage information.
  • the SQL processing engine executes step S30': in response to the second obtaining request, sending storage information to the stored procedure engine, so that the stored procedure engine obtains all result sets according to the storage information.
  • the stored procedure engine executes step S31': obtains all result sets according to the stored information.
  • the stored procedure engine searches for the stored result set according to the storage location, and obtains the result set from the file, instead of repeatedly requesting the SQL processing engine for the result set, which further reduces SQL The number of interactions between the processing engine and the stored procedure engine.
  • the stored procedure engine executes step S32': notifies the SQL processing engine to release the result set.
  • steps S21 to S32 or steps S21 to S32', continue to execute the next SQL instruction until all instructions of this stored procedure are completed.
  • steps S21 to S32 can be used.
  • the stored procedure engine executes step S33: sending a message of the execution result of the stored procedure to the SQL processing engine.
  • the stored procedure engine executes the stored procedure, it splices the outgoing parameters and incoming and outgoing parameters into the message, and sends it to the SQL processing engine as the final response; the SQL processing engine returns the final result to the user's client.
  • the message is changed from passing a single-row result set to passing a multi-row result set message, that is, a multi-row result set is requested from the stored procedure engine to the SQL processing engine at one time, and the SQL processing engine Responds to a multi-row result set to the stored procedure engine.
  • the first SQL instruction may further include multiple SQL instructions.
  • the following describes the process of acquiring the first SQL instruction.
  • a schematic diagram of its specific implementation is shown in Figure 8.
  • Step S40 Obtain the second SQL instruction in the currently executing stored procedure.
  • Step S41 determine whether the second SQL instruction belongs to the conventional SQL instruction in the cursor, obtain the third determination result, if the third determination result indicates that the second SQL instruction is the conventional SQL instruction in the cursor, then execute step S42; the third determination result If it is indicated that the second SQL instruction belongs to the target type, step S43 is executed.
  • multiple SQL instructions can be executed for parallel requests, and multiple SQL instructions can be combined into one request message.
  • the SQL commands that can be requested in parallel meet the following conditions:
  • the mth SQL instruction is a regular SQL instruction in the cursor, and the m+1th SQL instruction is a regular SQL instruction. Requests can be made to the SQL processing engine at the same time, and m is an integer greater than 0.
  • the stored procedure engine When executing the stored procedure, the stored procedure engine obtains the SQL instruction in the currently executed stored procedure, and uses the obtained SQL instruction as a second SQL instruction, where the second SQL instruction is a currently executed SQL instruction.
  • the target type may include: a regular SQL instruction, or a query-like SQL instruction in the cursor, and any SQL instruction in the cursor is not sent to the SQL processing engine.
  • Step S42 Cache the second SQL instruction, and continue to return to the step of acquiring the next second SQL instruction.
  • Step S43 Combine the SQL instruction and the second SQL instruction in the buffer area as the first SQL instruction.
  • FIG. 9 a schematic diagram of the specific implementation of the processing of error information by the SQL processing engine is shown in FIG. 9 .
  • Step S50 Acquire error information.
  • Step S51 Determine whether the error information belongs to a preset error type, and obtain a fifth judgment result. If the fifth judgment result indicates that the error information belongs to the preset error type, step S52 is performed; otherwise, step S53 is performed.
  • the preset error types include: errors that occur during processing by the SQL processing engine itself, such as syntax parsing errors, insufficient memory, errors in dispatching tasks to lower-level DBs, operating system failures, etc.; and other unrecognized errors.
  • Step S52 Send a termination request for terminating the current stored process to the stored process engine, or send a prompt message that the current stored process fails to execute to the user's client.
  • the SQL processing engine sends a request for terminating the current stored procedure to the stored procedure engine, or the SQL processing engine directly reports the execution failure of the stored procedure to the user's client, and displays an error message.
  • Step S53 Send error information to the stored procedure engine.
  • the error information may be sent to the stored procedure engine, and the stored procedure engine will process the error according to the defined error handling method.
  • the identified error type is the error message returned by the lower-level DB node when processing the SQL, such as primary key conflict, table does not exist, etc., or the identified error message is the normal error of the SQL processing engine when processing the result set returned from the lower-level DB , if there is no next data. Both types of errors can be sent to the stored procedure engine. Stored procedures are processed by the stored procedure engine according to the error handling method in the stored procedure definition.
  • the SQL processing engine when the SQL processing engine makes an error in processing the SQL request, it increases the identification and processing of the error information to determine whether to send the error information to the stored procedure engine, which is convenient to reduce the interaction between the SQL processing engine and the stored procedure engine, and reduces the Processing of the stored procedure engine.
  • the fourth embodiment of the present application relates to an electronic device, as shown in FIG. 10 , comprising: at least one processor 401; and a memory 402 connected in communication with the at least one processor 401; Instructions executed by the processor 401, the instructions are executed by at least one processor 401, so that the at least one processor 401 can execute the above-mentioned method for running a distributed stored procedure.
  • the memory and the processor are connected by a bus, and the bus may include any number of interconnected buses and bridges, and the bus connects one or more processors and various circuits of the memory.
  • the bus may also connect together various other circuits, such as peripherals, voltage regulators, and power management circuits, which are well known in the art and therefore will not be described further herein.
  • the bus interface provides the interface between the bus and the transceiver.
  • a transceiver may be a single element or multiple elements, such as multiple receivers and transmitters, providing a means for communicating with various other devices over a transmission medium.
  • the data processed by the processor is transmitted on the wireless medium through the antenna, and further, the antenna also receives the data and transmits the data to the processor.
  • the processor is responsible for managing the bus and general processing, and can also provide various functions, including timing, peripheral interface, voltage regulation, power management, and other control functions. Instead, memory may be used to store data used by the processor in performing operations.
  • the fifth embodiment of the present application relates to a computer-readable storage medium storing a computer program.
  • the computer program is executed by the processor, the above-mentioned embodiments of the running method of the distributed stored procedure are implemented.
  • the aforementioned storage medium includes: U disk, mobile hard disk, Read-Only Memory (ROM, Read-Only Memory), Random Access Memory (RAM, Random Access Memory), magnetic disk or optical disk and other media that can store program codes .

Landscapes

  • Engineering & Computer Science (AREA)
  • Theoretical Computer Science (AREA)
  • Physics & Mathematics (AREA)
  • Databases & Information Systems (AREA)
  • General Engineering & Computer Science (AREA)
  • General Physics & Mathematics (AREA)
  • Data Mining & Analysis (AREA)
  • Software Systems (AREA)
  • Mathematical Physics (AREA)
  • Computational Linguistics (AREA)
  • Computing Systems (AREA)
  • Information Retrieval, Db Structures And Fs Structures Therefor (AREA)

Abstract

本申请实施例涉及数据库领域，公开了一种分布式存储过程的运行方法、电子设备及存储介质。方法包括：获取存储过程中的第一结构化查询语言SQL指令；判断第一SQL指令中SQL指令的类型，获得第一判断结果；若第一判断结果指示第一SQL指令中存在非游标的查询类SQL指令，则生成第一处理请求，第一处理请求包括非游标的查询类SQL指令以及返回结果集信息的返回指示信息；向SQL处理引擎发送第一处理请求，以供SQL处理引擎向存储过程引擎返回非游标的查询类SQL指令的结果状态以及结果集信息。

Description

分布式存储过程的运行方法、电子设备及存储介质
交叉引用
本申请基于申请号为“202010636364.4”、申请日为2020年07月03日的中国专利申请提出，并要求该中国专利申请的优先权，该中国专利申请的全部内容在此以引入方式并入本申请。
技术领域
本申请实施例涉及数据库领域，特别涉及一种分布式存储过程的运行方法、电子设备及存储介质。
背景技术
在实现本申请实施例的过程中，发明人发现，在执行存储过程的过程中，结构化查询语言(Structured Query Language，SQL)处理引擎与存储过程引擎之间的消息交互次多，影响执行存储过程的效率。
发明内容
本申请的实施例提供了一种分布式存储过程的运行方法，包括：获取存储过程中的第一结构化查询语言SQL指令；判断第一SQL指令中SQL指令的类型，获得第一判断结果；若第一判断结果指示第一SQL指令中存在非游标的查询类SQL指令，则生成第一处理请求，第一处理请求包括非游标的查询类SQL指令以及返回结果集信息的返回指示信息；向SQL处理引擎发送第一处理请求，以供SQL处理引擎向存储过程引擎返回非游标的查询类SQL指令的结果状态以及结果集信息。
本申请的实施例还提供了一种分布式存储过程的运行方法，包括：接收存储过程引擎发送的请求消息；判断请求消息中是否存在第一处理请求，获取第四判断结果，第一处理请求包括：非游标的查询类SQL指令以及返回结果集信息的返回指示信息若第四判断结果指示存在第一处理请求，则向存储过程引擎返回非游标的查询类SQL指令的结果状态以及结果集信息。
本申请的实施例还提供了一种电子设备，包括：至少一个处理器；以及，与至少一个处理器通信连接的存储器；其中，存储器存储有可被至少一个处理器执行的指令，指令被至少一个处理器执行，以使至少一个处理器能够执行上述应用于存储过程引擎的分布式存储过程的运行方法，或者，执行应用于SQL处理引擎的分布式存储过程的运行方法。
本申请的实施例还提供了一种计算机可读存储介质，存储有计算机程序，计算机程序被处理器执行时实现上述应用于存储过程引擎的分布式存储过程的运行方法，或者，执行应用于SQL处理引擎的分布式存储过程的运行方法。
附图说明
一个或多个实施例通过与之对应的附图中的图片进行示例性说明，这些示例性说明并不构成对实施例的限定。
图1是根据相关技术中分布式数据库的架构示意图；
图2是根据相关技术中存储过程运行的交互示意图；
图3是根据本申请第一实施例中的分布式存储过程的运行方法的流程图；
图4是根据本申请第二实施例中的分布式存储过程的运行方法的流程图；
图5是根据本申请第三实施例中的运行非游标的查询类SQL指令的存储过程的交互示意图；
图6是根据本申请第三实施例中的运行游标中的查询类SQL指令的存储过程的交互示意图；
图7是根据本申请第三实施例中运行游标中的查询类SQL指令的存储过程的交互示意图；
图8是根据本申请第三实施例中的分布式存储过程的运行方法中获取该第一SQL指令的示意图；
图9是根据本申请第三实施例中的分布式存储过程的运行方法中对错误信息处理的示意图；
图10是根据本申请第四实施例中的电子设备的结构示意图。
具体实施方式
为使本申请实施例的目的、技术方案和优点更加清楚，下面将结合附图对本申请的各实施例进行详细的阐述。然而，本领域的普通技术人员可以理解，在本申请各实施例中，为了使读者更好地理解本申请而提出了许多技术细节。但是，即使没有这些技术细节和基于以下各实施例的种种变化和修改，也可以实现本申请所要求保护的技术方案。以下各个实施例的划分是为了描述方便，不应对本申请的具体实现方式构成任何限定，各个实施例在不矛盾的前提下可以相互结合相互引用。
存储过程(Stored Procedure)是存储于数据库系统中的一组能够完成特定功能的语句集，存储过程经过第一次编译后，在后续调用时无需再次编译，用户可以通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来运行存储过程。分布式数据库系统中通常包括存储过程引擎和结构化查询语言(SQL)处理引擎，存储过程引擎用于运行存储过程，而SQL处理引擎用于执行存储过程中的SQL指令。
在分布式数据库中，存在多个数据存储结点，SQL处理引擎以及存储过程引擎。目前的存储过程的运行如图1、2所示。
图1为分布式数据库的架构示意图。该分布式数据库包括多个存储结点，如，DB_1、DB_2……DB_n。用户的发起的执行存储过程的执行请求发送至SQL处理引擎，由SQL处理引擎、存储过程引擎、数据存储结点以及元数据管理进行协助，完成存储过程的执行，元数据管理用于管理元数据，其中，数据存储结点可以为存储设备。下面结合图2介绍目前SQL处理引擎与存储过程引擎之间的交互过程。
SQL处理引擎执行步骤S01：向存储过程引擎发送该执行存储过程的请求。
存储过程引擎执行步骤S02：存储过程引擎执行该存储过程，检测存储过程中的SQL指令。
存储过程引擎执行步骤S03：根据检测到的SQL指令，重构出完整的SQL指令，可以以消息的形式发送至SQL处理引擎，等待SQL处理引擎执行该SQL指令的执行结果。
SQL处理引擎执行步骤S04：接收存储过程引擎发送的SQL指令。
SQL处理引擎执行步骤S05：SQL处理引擎完成对接收的SQL语句的执行，将执行的结果状态作为响应发给SQL处理引擎。
存储过程引擎执行步骤S06：收到SQL执行结果状态后，根据该结果状态，判断是否需要向SQL处理引擎请求一行的结果集，如是，则发送请求。
SQL处理引擎执行步骤S07：SQL处理引擎将一行的结果集作为响应发送给存储过程引擎。如果存储过程引擎需要多行的结果集，则多次进行上述的获取步骤，即每需要获取一行结果集就执行步骤S06和步骤S07。
存储过程引擎执行步骤S08：执行完当前的SQL指令的结果集，通知SQL处理引擎释放对应的结果集。SQL处理引擎重复执行下一条SQL指令，即重复步骤S03至步骤S08。
存储过程引擎执行步骤S09：执行完存储过程后，将出参、出入参拼接到消息中，作为最终响应发送给SQL处理引擎。SQL处理引擎将最终结果返回给用户。
存储过程执行中，SQL的结果集从SQL处理引擎传递到存储过程引擎中，若SQL处理引擎与存储过程引擎之间交互次数多，会导致频繁访问网络。
可见，现在的交互方式中若存储过程引擎每执行一行的结果集时，需要向SQL处理引擎发送一次获取请求，由SQL处理引擎返回的结果集，若需要多行的结果集，SQL处理引擎与存储过程引擎之间的交互次数多，影响执行存储过程的效率。
本申请实施例的目的在于提供一种分布式存储过程的运行方法、电子设备及存储介质，提高了分布式存储过程运行的效率。
本申请的第一实施例涉及一种分布式存储过程的运行方法，应用于存储过程引擎，其流程如图3所示。
步骤101：获取存储过程中的第一结构化查询语言SQL指令。
步骤102：判断第一SQL指令中SQL指令的类型，获得第一判断结果。
步骤103：若第一判断结果指示第一SQL指令中存在非游标的查询类SQL指令，则生成第一处理请求，第一处理请求包括非游标的查询类SQL指令以及返回结果集信息的返回指示信息。
步骤104：向SQL处理引擎发送第一处理请求，以供SQL处理引擎向存储过程引擎返回非游标的查询类SQL指令的结果状态以及结果集信息。
本申请实施例相对于相关技术而言，获取存储过程中的第一SQL指令后，对该第一SQL指令的类型进行判断，若第一SQL指令中存在非游标的查询类SQL指令，生成包含返回指示信息的第一处理请求，SQL处理引擎根据该第一处理请求，对该非游标的查询类SQL指令进行处理，该SQL处理引擎将处理的结果状态以及结果集直接返回该存储过程引擎，存储过程引擎即可对下一条第一SQL指令进行处理；由于存储过程引擎无需在接收到结果状态后，向SQL处理引擎逐行获取结果集，减少了存储过程引擎与SQL处理引擎获取之间的交互次数， 减小了对非游标的查询类SQL指令的处理速度，提升了整个存储过程的运行效率。
本申请的第二实施例涉及一种分布式存储过程的运行方法，该分布式存储过程的运行方法应用于SQL处理引擎，其流程如图4所示：
步骤201：接收存储过程引擎发送的请求消息。
步骤202：判断请求消息中是否存在第一处理请求，获取第四判断结果，第一处理请求包括：非游标的查询类SQL指令以及返回结果集信息的返回指示信息。
步骤203：若第四判断结果指示存在第一处理请求，则向存储过程引擎返回非游标的查询类SQL指令的结果状态以及结果集信息。
本实施例提供的分布式存储过程的运行方法，SQL处理引擎在处理了请求消息中的指定的SQL指令，可以判断请求消息中是否存在第一处理请求，若存在第一处理请求，向存储过程引擎返回处理得到的结果状态以及结果集信息，由于无需等待存储过程引擎下一交互过程中的获取结果集的请求，减少了与存储过程交互的次数，且无需逐行将结果集返回存储过程引擎，进一步减少与存储过程引擎的交互次数，提高了执行存储过程的效率。
为了更好的理解第一实施例以及第二实施例，第三实施例将详细介绍该分布式存储过程运行的方法。
第三实施例中的分布式存储过程运行的方法，其运行过程如图5所示。
分布式数据库中，存在多个数据存储结点、存储过程引擎和SQL处理引擎。下面结合存储过程引擎和SQL处理引擎之间的交互图5，具体介绍分布式存储过程的运行过程。
SQL处理引擎执行步骤S11：向存储过程引擎发送该执行存储过程的请求。
具体地，用户的客户端向SQL处理引擎发起执行存储过程的执行请求，SQL处理引擎在处理该执行请求时，向存储过程引擎发送该执行存储过程的请求。
存储过程引擎执行步骤S12：获取存储过程中的第一结构化查询语言SQL指令。
具体地，存储过程引擎执行该存储过程中，获取执行该存储过程中的一条SQL指令，可以将该条SQL指令作为第一SQL指令，也可以将执行存储过程中的获取的多条SQL指令作为第一SQL指令，也就是说第一SQL指令可以包含一条SQL指令，也可以包含至少两条SQL指令。
存储过程引擎执行步骤S13：判断第一SQL指令中SQL指令的类型，获得第一判断结果。
具体地，本示例中SQL指令的类型可以包括：常规SQL指令，非游标的查询类SQL、游标中的查询类SQL指令、游标中的常规SQL指令。SQL处理引擎执行该非游标的查询类SQL指令后的结果包括三种：成功、失败以及结果集。存储过程引擎针对非游标的查询类SQL指令，需要执行SQL处理引擎返回的结果集。返回的结果集可以为一行或0行。针对游标中的查询类SQL指令，存储过程引擎需要延后执行结果集。在存储过程中执行游标的查询类SQL指令时，也需要结果集中的一行。而结果集包括0行、1行，多行这三种情况。常规SQL指令，不需要结果集，需要SQL返回的结果状态，如：成功的结果、失败的结果。
针对第一SQL指令中的每条SQL指令进行如下处理：根据该SQL指令及上下文信息，判断该SQL指令是否为非游标的查询类的SQL指令；例如，可以通过获取SQL指令的位置，未处于游标中，则属于非游标类的SQL指令，继续根据SQL指令的关键词，判断该是否属于查询类，经过两次判断，即可确定该SQL指令是否属于非游标的查询类SQL指令。可以理解的 是，还可以采用其他的方式进行判断，此处不再一一列举。
存储过程引擎执行步骤S14：若第一判断结果指示第一SQL指令中存在非游标的查询类SQL指令，则生成第一处理请求，第一处理请求包括非游标的查询类SQL指令以及返回结果集信息的返回指示信息。
具体地，生成的第一处理请求中包括返回结果集信息的返回指示信息，例如，第一处理请求中可以携带“回应中是否带上结果集”的信息，若第一判断结果指示第一SQL指令中存在非游标的查询类SQL指令，则，“回应中是否带上结果集信息”的信息为true；该true信息即为返回指示信息；若第一判断结果指示第一SQL指令中不存在非游标的查询类SQL指令，则，“回应中是否带上结果集信息”的信息为false。结果集信息可以包括：执行该条第一SQL指令后得到的所有结果集，也可以是指定行数的结果集。
可以理解的是，第一处理请求中可以包括该非游标的查询类SQL指令；若第一SQL指令中具有多条SQL指令，那么该第一处理请求还可以包括第一SQL指令中的其他类型的SQL指令。
存储过程引擎执行步骤S15：向SQL处理引擎发送第一处理请求，以供SQL处理引擎向存储过程引擎返回非游标的查询类SQL指令的结果状态以及结果集信息。
具体地，根据第一处理请求，生成请求消息，该请求消息中可以包括该第一处理请求，还可以包括第一SQL指令中的其他SQL指令。
SQL处理引擎执行步骤S16：接收存储过程引擎发送的请求消息。
具体地，获取请求消息中的携带的SQL指令，处理该SQL指令，获取处理该SQL指令的结果状态以及对应的结果集信息。
SQL处理引擎执行步骤S17：判断请求消息中是否存在第一处理请求，获取第四判断结果，第一处理请求包括：非游标的查询类SQL指令以及返回结果集信息的返回指示信息。
具体地，第一处理请求中携带有返回指示信息，若请求消息中是否有返回指示信息，则可以判定请求消息中存在第一处理请求，例如：查找到请求消息中的“回应中是否带上结果集信息”信息为true，则第四判断结果指示存在第一处理请求。
SQL处理引擎执行步骤S18：若第四判断结果指示存在第一处理请求，则向存储过程引擎返回非游标的查询类SQL指令的结果状态以及结果集信息。
在一个例子中，若第四判断结果指示存在第一处理请求，SQL处理引擎获取非游标的查询类SQL指令的结果集；判断非游标的查询类SQL指令的结果集的行数是否大于1，若是，则生成错误信息。若非游标的查询类SQL指令的结果集的行数小于或等于1，则构造一个新的种类的状态消息，该新的状态消息中包含执行非游标的查询类SQL指令的结果状态和结果集信息；结果集信息可以包括：执行该条第一SQL指令后得到的所有结果集，也可以是指定行数的结果集。
在另一个例子中，若第四判断结果指示不存在第一处理请求，则按照已有方式构造状态消息。
按照上述步骤S11至步骤S18，继续执行下一条SQL指令，直至完成本次存储过程的所有指令。
存储过程引擎执行步骤S19：完成当前的SQL指令的结果集，通知SQL处理引擎释放对 应的结果集。SQL处理引擎重复执行下一条第一SQL指令，即重复步骤S13至步骤S18。
存储过程引擎执行步骤S20：向SQL处理引擎发送存储过程执行结果的消息。
具体地，存储过程引擎执行完存储过程后，将出参、出入参拼接到消息中，作为最终响应发送给SQL处理引擎；SQL处理引擎将最终结果返回给用户的客户端。
本实施例中还提供了运行游标中的查询类SQL指令的存储过程的方式。下面结果交互图6、图7介绍。
请参考图6。
SQL处理引擎执行步骤S21：向存储过程引擎发送该执行存储过程的请求。
与步骤S11类似，此处不再赘述。
存储过程引擎执行步骤S22：获取存储过程中的第一结构化查询语言SQL指令。
具体地，存储过程引擎执行该存储过程中，获取执行该存储过程中的一条SQL指令，可以将该SQL指令作为第一SQL指令，也可以将执行存储过程中的多条SQL指令作为第一SQL指令，也就是说第一SQL指令可以包含一条SQL指令，也可以包含至少两条SQL指令。
存储过程引擎执行步骤S23：判断第一SQL指令中SQL指令的类型，获得第一判断结果。
该步骤与步骤S13大致相同，此处不再赘述。
存储过程引擎执行步骤S24：若第一判断结果指示第一SQL指令中存在游标中的查询类SQL指令，则向SQL处理引擎发送第二处理请求，第二处理请求包括游标中的查询类SQL指令，以供SQL处理引擎返回状态消息，状态消息包括游标中的查询类SQL指令的结果状态。
具体地，针对第一SQL指令中的SQL指令进行如下处理：根据该SQL指令及上下文信息，判断该SQL指令是否为游标的查询类的SQL指令；例如，可以通过获取SQL指令的位置，若该SQL指令处于游标中，则属于游标类的SQL指令，根据SQL指令的关键词，判断该SQL指令是否属于查询类，经过两次判断，即可确定该SQL指令是否属于游标中的查询类SQL指令。可以理解的是，还可以采用其他的方式进行判断，此处不再一一列举。
第一判断结果指示第一SQL指令中存在游标中的查询类SQL指令，则可以生成请求消息，该请求消息中可以包括该第二处理请求、第一SQL指令中的其他SQL指令。
SQL处理引擎执行步骤S25：接收存储过程引擎发送的请求消息。
具体地，获取请求消息中的携带的SQL指令，处理该SQL指令，可以获取处理该SQL指令的结果状态以及对应的结果集。
SQL处理引擎执行步骤S26：判断请求消息中是否存在第一处理请求，获取第四判断结果，第一处理请求包括：非游标的查询类SQL指令以及返回结果集信息的返回指示信息。
具体地，第一处理请求中携带有返回指示信息，若请求消息中有返回指示信息，则可以判定请求消息中存在第一处理请求，例如：查找到请求消息中的“回应中是否带上结果集信息”信息为false；该第四判断结果指示不存在第一处理请求。若检测出包括游标中的查询类SQL指令，则第四判断结果指示存在第二处理请求。可以理解的是，第二处理请求中还可以包括第二处理请求的标识信息，以便该SQL处理引擎可以根据该标识信息，判断是否存在第二处理请求。
SQL处理引擎执行步骤S27：若第四判断结果指示存在第二处理请求，第二处理请求包括游标中的查询类SQL指令，则获取游标中的查询类SQL指令的结果状态以及结果集中的容量 信息。
具体地，若处理第二请求后，获得的结果是结果集时，SQL处理引擎计算结果集的总行数或一行的结果集的大小。结果集的容量信息可以包括获得的结果集的总行数或一行的结果集的大小的信息，SQL处理引擎发送给存储过程引擎的状态消息中带上结果集的容量信息。
SQL处理引擎执行步骤S28：向存储过程引擎返回状态消息，状态消息包括游标中的查询类SQL指令的结果状态以及结果集中的容量信息，以供存储过程引擎向SQL处理引擎发送获取n行的结果集的第一获取请求。
存储过程引擎执行步骤S29：判断接收的状态消息中是否存在结果集的容量信息，获取第二判断结果。
存储过程引擎执行步骤S30：若第二判断结果指示状态消息中存在结果集的容量信息，则向SQL处理引擎发送获取n行的结果集的第一获取请求，以供SQL处理引擎返回n行的结果集，n为大于1的整数。
具体地，存储过程引擎向SQL处理引擎请求n行的结果集可以根据存储过程引擎根据容量信息中的总行数或一行结果集的大小设置n的值，也可以同时根据内存、网络因素动态设定n的值，n为大于1的整数。
SQL处理引擎执行步骤S31：响应于第一获取请求，向存储过程引擎返回n行的结果集。
具体地，如果对结果集中剩余结果集行数有n行(即剩余结果集行数大于等于n行)，就向存储过程引擎回应n行的结果集；如没有n行，则回应实际行数的结果集。
存储过程引擎执行步骤S32：通知SQL处理引擎释放结果集。
若SQL处理引擎与存储过程引擎部署在同一个电子设备上时，请参考图7，依旧执行步骤S21至步骤S26。
SQL处理引擎执行步骤S27’：若第四判断结果指示存在第二处理请求，第二处理请求包括游标中的查询类SQL指令，向存储过程引擎发送游标中的查询类SQL指令的结果状态，以供存储过程引擎发送获取所有结果集的第二获取请求。
存储过程引擎还可以执行步骤S28’：若第二判断结果指示状态消息中不存在结果集的容量信息，则在存储过程引擎所处设备中检测是否存在SQL处理引擎，若是存在，则向SQL处理引擎发送获取所有结果集的第二获取请求，以供SQL处理引擎返回所有结果集的存储信息。
具体地，存储过程引擎检测所处的电子设备上是否存在SQL处理引擎，若是存在，表明存储过程引擎与SQL处理引擎被部署在同一个电子设备上。
SQL处理引擎执行步骤S29’：将游标中的查询类SQL指令的所有结果集写入SQL处理引擎所在设备的存储介质上，获取存储信息。
具体地，获取处理第二处理请求中游标中的查询类SQL指令的所有结果集，将该结果集存储在SQL处理引擎所在设备的存储介质上，例如，磁盘文件或共享内存上；获取该文件的存储位置。向存储过程引擎发送结果集所在的文件的存储位置。
SQL处理引擎执行步骤S30’：响应于第二获取请求，向存储过程引擎发送存储信息，以供存储过程引擎按照存储信息获取所有结果集。
存储过程引擎执行步骤S31’:按照存储信息获取所有结果集。
具体地，由于SQL处理引擎和存储过程引擎位于同一设备上，存储过程引擎按照存储位置查找存储的结果集，从文件中取用结果集，而不用反复向SQL处理引擎请求结果集，进一步减少SQL处理引擎与存储过程引擎之间的交互次数。
存储过程引擎执行步骤S32’：通知SQL处理引擎释放结果集。
按照上述步骤S21至步骤S32，或者步骤S21至步骤S32’，继续执行下一条SQL指令，直至完成本次存储过程的所有指令。
需要说明的是，在SQL处理引擎与存储过程引擎部署在不同设备或是同一个电子设备上时，都能采用步骤S21至步骤S32。
存储过程引擎执行步骤S33：向SQL处理引擎发送存储过程执行结果的消息。
具体地，存储过程引擎执行完存储过程后，将出参、出入参拼接到消息中，作为最终响应发送给SQL处理引擎；SQL处理引擎将最终结果返回给用户的客户端。
值得一提的是，对游标中的查询类SQL，将消息由传递单行结果集改为传递多行结果集消息，即从存储过程引擎向SQL处理引擎一次就请求多行结果集，SQL处理引擎向存储过程引擎回应多行结果集。
在一个例子中，第一SQL指令中还可以包括多个SQL指令，下面介绍获取该第一SQL指令的过程。其具体实现的示意图如图8所示。
步骤S40:获取当前执行存储过程中的第二SQL指令。
步骤S41：判断第二SQL指令是否属于游标中的常规SQL指令，获取第三判断结果，若第三判断结果指示第二SQL指令为游标中的常规SQL指令，则执行步骤S42；第三判断结果指示第二SQL指令属于目标类型，则执行步骤S43。
具体地，在存储过程引擎中，对可并行请求执行多个SQL指令，可以将多个SQL指令合并成一个请求消息。可并行请求的SQL指令满足如下条件：
(1)第m条SQL指令是游标中的常规SQL指令，而第m+1条SQL指令是一条常规SQL指令。可以同时向SQL处理引擎进行请求，m为大于0的整数。
(2)连续多个游标中的SQL指令，且没有查询Fetch操作。这些游标中的SQL可以同时向SQL处理引擎进行请求。
存储过程引擎在执行存储过程时，获取当前执行存储过程中的SQL指令，将获取的SQL指令作为第二SQL指令，该第二SQL指令为当前执行的一条SQL指令。
检测第二SQL指令是否属于游标中的常规SQL指令，若是，则缓存此游标中的查询类SQL指令，继续执行存储过程中下一条第二SQL指令，继续检测下一条第二SQL指令是否属于目标类型，目标类型可以包括：常规SQL指令，或者，游标中的查询类SQL指令，且该游标中任一条SQL指令未被发送至SQL处理引擎。
步骤S42：缓存第二SQL指令，继续返回获取下一条第二SQL指令的步骤。
步骤S43：将缓存区内的SQL指令以及第二SQL指令合并作为第一SQL指令。
在一个例子中，SQL处理引擎对错误信息的处理的具体实现的示意图如图9所示。
步骤S50：获取错误信息。
步骤S51：判断错误信息是否属于预设的错误类型，获取第五判断结果，若第五判断结果指示错误信息属于预设的错误类型，则执行步骤S52；否则，执行步骤S53。
具体地，预设的错误类型包括：SQL处理引擎本身在处理时出现的错误，如语法解析出错、内存不足、分发任务到下层DB出错、操作系统故障等；以及其它未识别的错误。
步骤S52：向存储过程引擎发送终止当前存储过程的终止请求，或者，向用户的客户端发送当前存储过程执行失败的提示信息。
具体地，SQL处理引擎向存储过程引擎发送终止当前存储过程的请求，或者，SQL处理引擎，直接向用户的客户端报告存储过程执行失败，并显示出错误信息。
步骤S53：将错误信息发送至存储过程引擎。
具体地，若不是预设的错误类型，则可以将错误信息发送至存储过程引擎，由该存储过程引擎按照定义中的错误处理方式进行处理。
例如，识别的错误类型为下层DB结点处理本SQL时返回的出错信息，如主键冲突，表不存在等，或者，识别错误信息为SQL处理引擎在处理从下层DB返回的结果集时正常出错，如没有下一条数据。以上两种类型的错误均可以发送至存储过程引擎。存储过程是由存储过程引擎根据存储过程定义中的错误处理方式进行处理。
值得一提的是，SQL处理引擎在处理SQL请求出错时，增加对错误信息进行识别处理，以判断是否将错误信息发送给存储过程引擎，便于减少SQL处理引擎与存储过程引擎的交互，并且减少存储过程引擎的处理。
此外，本领域技术人员可以理解，上面各种方法的步骤划分，只是为了描述清楚，实现时可以合并为一个步骤或者对某些步骤进行拆分，分解为多个步骤，只要包括相同的逻辑关系，都在本专利的保护范围内；对算法中或者流程中添加无关紧要的修改或者引入无关紧要的设计，但不改变其算法和流程的核心设计都在该专利的保护范围内。
本申请第四实施例涉及一种电子设备，如图10所示，包括：至少一个处理器401；以及，与至少一个处理器401通信连接的存储器402；其中，存储器402存储有可被至少一个处理器401执行的指令，指令被至少一个处理器401执行，以使至少一个处理器401能够执行上述的分布式存储过程的运行方法。
其中，存储器和处理器采用总线方式连接，总线可以包括任意数量的互联的总线和桥，总线将一个或多个处理器和存储器的各种电路连接在一起。总线还可以将诸如外围设备、稳压器和功率管理电路等之类的各种其他电路连接在一起，这些都是本领域所公知的，因此，本文不再对其进行进一步描述。总线接口在总线和收发机之间提供接口。收发机可以是一个元件，也可以是多个元件，比如多个接收器和发送器，提供用于在传输介质上与各种其他装置通信的单元。经处理器处理的数据通过天线在无线介质上进行传输，进一步，天线还接收数据并将数据传送给处理器。
处理器负责管理总线和通常的处理，还可以提供各种功能，包括定时，外围接口，电压调节、电源管理以及其他控制功能。而存储器可以被用于存储处理器在执行操作时所使用的数据。
本申请第五实施例涉及一种计算机可读存储介质，存储有计算机程序。计算机程序被处理器执行时实现上述分布式存储过程的运行方法实施例。
即，本领域技术人员可以理解，实现上述实施例方法中的全部或部分步骤是可以通过程序来指令相关的硬件来完成，该程序存储在一个存储介质中，包括若干指令用以使得一个设 备(可以是单片机，芯片等)或处理器(processor)执行本申请各个实施例方法的全部或部分步骤。而前述的存储介质包括：U盘、移动硬盘、只读存储器(ROM，Read-Only Memory)、随机存取存储器(RAM，Random Access Memory)、磁碟或者光盘等各种可以存储程序代码的介质。
本领域的普通技术人员可以理解，上述各实施例是实现本申请的具体实施例，而在实际应用中，可以在形式上和细节上对其作各种改变，而不偏离本申请的精神和范围。

Claims (10)

  1. 一种分布式存储过程的运行方法，包括：
    获取存储过程中的第一结构化查询语言SQL指令；
    判断第一SQL指令中SQL指令的类型，获得第一判断结果；
    若所述第一判断结果指示所述第一SQL指令中存在非游标的查询类SQL指令，则生成第一处理请求，所述第一处理请求包括所述非游标的查询类SQL指令以及返回结果集信息的返回指示信息；
    向所述SQL处理引擎发送所述第一处理请求，以供所述SQL处理引擎向存储过程引擎返回所述非游标的查询类SQL指令的结果状态以及所述结果集信息。
  2. 根据权利要求1所述的分布式存储过程的运行方法，其中，若所述第一判断结果指示存在游标中的查询类SQL指令，所述方法还包括：
    向所述SQL处理引擎发送第二处理请求，所述第二处理请求包括所述游标中的查询类SQL指令，以供所述SQL处理引擎返回状态消息，所述状态消息包括所述游标中的查询类SQL指令的结果状态；
    判断接收的所述状态消息中是否存在结果集的容量信息，获取第二判断结果；
    若所述第二判断结果指示所述状态消息中存在所述结果集的容量信息，则向所述SQL处理引擎发送获取n行的结果集的第一获取请求，以供所述SQL处理引擎返回n行的结果集，n为大于1的整数；
    若所述第二判断结果指示所述状态消息中不存在结果集的容量信息，则在所述存储过程引擎所处设备中检测是否存在所述SQL处理引擎，若是存在，则向所述SQL处理引擎发送获取所有结果集的第二获取请求，以供所述SQL处理引擎返回所有结果集的存储信息；按照所述存储信息获取所有结果集。
  3. 根据权利要求1或2所述的分布式存储过程的运行方法，其中，所述获取存储过程中的第一结构化查询语言SQL指令，包括：
    获取当前执行存储过程中的第二SQL指令；
    判断所述第二SQL指令是否属于游标中的常规SQL指令，获取第三判断结果；
    若所述第三判断结果指示所述第二SQL指令为游标中的常规SQL指令，则缓存所述第二SQL指令，继续返回获取下一条第二SQL指令的步骤；
    若所述第三判断结果指示所述第二SQL指令属于目标类型，则将缓存区内的SQL指令以及所述第二SQL指令合并作为所述第一SQL指令，其中，所述目标类型包括常规SQL指令以及属于游标中的查询类SQL指令且所述游标对应的SQL指令未被发送至所述SQL处理引擎。
  4. 一种分布式存储过程的运行方法，包括：
    接收存储过程引擎发送的请求消息；
    判断所述请求消息中是否存在第一处理请求，获取第四判断结果，所述第一处理请求包括：非游标的查询类SQL指令以及返回结果集信息的返回指示信息；
    若所述第四判断结果指示存在所述第一处理请求，则向所述存储过程引擎返回所述非游标的查询类SQL指令的结果状态以及所述结果集信息。
  5. 根据权利要求4所述的分布式存储过程的运行方法，其中，若所述第四判断结果指示存在第二处理请求，所述第二处理请求包括所述游标中的查询类SQL指令，所述方法还包括：
    获取所述游标中的查询类SQL指令的结果状态以及所述结果集中的容量信息；
    向所述存储过程引擎返回状态消息，所述状态消息包括所述游标中的查询类SQL指令的结果状态以及所述结果集中的容量信息，以供所述存储过程引擎向所述SQL处理引擎发送获取n行的结果集的第一获取请求，n为大于1的整数；
    响应于所述第一获取请求，向所述存储过程引擎返回n行的结果集。
  6. 根据权利要求4或5所述的分布式存储过程的运行方法，其中，若所述第四判断结果指示存在第二处理请求，所述第二处理请求包括所述游标中的查询类SQL指令，所述方法还包括：
    向所述存储过程引擎发送所述游标中的查询类SQL指令的结果状态，以供所述存储过程引擎发送获取所有结果集的第二获取请求；
    将所述游标中的查询类SQL指令的所有结果集写入所述SQL处理引擎所在设备的存储介质上，获取存储信息；
    响应于所述第二获取请求，向所述存储过程引擎发送所述存储信息，以供所述存储过程引擎按照所述存储信息获取所有结果集。
  7. 根据权利要求4至6中任一项所述的分布式存储过程的运行方法，其中，包括：
    获取错误信息；
    判断所述错误信息是否属于预设的错误类型，获取第五判断结果；
    若所述第五判断结果指示错误信息属于预设的错误类型，则向所述存储过程引擎发送终止当前存储过程的终止请求，或者，向用户的客户端发送当前存储过程执行失败的提示信息。
  8. 根据权利要求4至7中任一项所述的分布式存储过程的运行方法，其中，在所述判断所述请求消息中是否存在第一处理请求，获取第四判断结果之后，所述方法还包括：
    若所述第四判断结果指示存在所述第一处理请求，获取所述非游标的查询类SQL指令的结果集；
    判断所述非游标的查询类SQL指令的结果集的行数是否大于1，若是，则生成错误信息。
  9. 一种电子设备，包括：
    至少一个处理器；以及，
    与所述至少一个处理器通信连接的存储器；其中，
    所述存储器存储有可被所述至少一个处理器执行的指令，所述指令被所述至少一个处理器执行，以使所述至少一个处理器能够执行如权利要求1至3中任一项所述的分布式存储过程的运行方法，或者，执行如权利要求4至8中任一项所述的分布式存储过程的运行方法。
  10. 一种计算机可读存储介质，存储有计算机程序，所述计算机程序被处理器执行时实现权利要求1至3中任一项所述的分布式存储过程的运行方法，或者，执行如权利要求4至8中任一项所述的分布式存储过程的运行方法。
PCT/CN2021/104518 2020-07-03 2021-07-05 分布式存储过程的运行方法、电子设备及存储介质 WO2022002275A1 (zh)

Applications Claiming Priority (2)

Application Number Priority Date Filing Date Title
CN202010636364.4 2020-07-03
CN202010636364.4A CN113886415B (zh) 2020-07-03 2020-07-03 分布式存储过程的运行方法、电子设备及存储介质

Publications (1)

Publication Number Publication Date
WO2022002275A1 true WO2022002275A1 (zh) 2022-01-06

Family

ID=79013318

Family Applications (1)

Application Number Title Priority Date Filing Date
PCT/CN2021/104518 WO2022002275A1 (zh) 2020-07-03 2021-07-05 分布式存储过程的运行方法、电子设备及存储介质

Country Status (2)

Country Link
CN (1) CN113886415B (zh)
WO (1) WO2022002275A1 (zh)

Citations (5)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US7945551B1 (en) * 2001-06-14 2011-05-17 Oracle International Corporation Redirection of misses in queryable caches
CN104933190A (zh) * 2015-07-10 2015-09-23 上海新炬网络信息技术有限公司 一种sql语句执行频次动态调整方法
CN109033209A (zh) * 2018-06-29 2018-12-18 新华三大数据技术有限公司 Spark存储过程处理方法及装置
CN110543495A (zh) * 2019-08-29 2019-12-06 北京东方国信科技股份有限公司 游标遍历存储方法及装置
CN110795101A (zh) * 2019-09-18 2020-02-14 平安科技（深圳）有限公司 Sql代码信息显示方法、装置、计算机装置及存储介质

Family Cites Families (4)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US6598041B1 (en) * 2000-09-07 2003-07-22 International Business Machines Corporation Method, system, and program for processing modifications to data in tables in a database system
CN110069565B (zh) * 2017-11-16 2023-06-09 金篆信科有限责任公司 一种分布式数据库数据批量处理的方法及装置
CN110704479A (zh) * 2019-09-12 2020-01-17 新华三大数据技术有限公司 任务处理方法、装置、电子设备及存储介质
CN111259015B (zh) * 2020-02-10 2023-08-15 Oppo（重庆）智能科技有限公司 持续化数据存储方法与装置、电子设备

Patent Citations (5)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US7945551B1 (en) * 2001-06-14 2011-05-17 Oracle International Corporation Redirection of misses in queryable caches
CN104933190A (zh) * 2015-07-10 2015-09-23 上海新炬网络信息技术有限公司 一种sql语句执行频次动态调整方法
CN109033209A (zh) * 2018-06-29 2018-12-18 新华三大数据技术有限公司 Spark存储过程处理方法及装置
CN110543495A (zh) * 2019-08-29 2019-12-06 北京东方国信科技股份有限公司 游标遍历存储方法及装置
CN110795101A (zh) * 2019-09-18 2020-02-14 平安科技（深圳）有限公司 Sql代码信息显示方法、装置、计算机装置及存储介质

Also Published As

Publication number Publication date
CN113886415B (zh) 2023-02-07
CN113886415A (zh) 2022-01-04

Similar Documents

Publication Publication Date Title
US8209697B2 (en) Resource allocation method for a physical computer used by a back end server including calculating database resource cost based on SQL process type
US7502824B2 (en) Database shutdown with session migration
US5953719A (en) Heterogeneous database system with dynamic commit procedure control
US9348641B2 (en) System and method for performing a transaction in a massively parallel processing database
US20060106759A1 (en) Computer system, computer, data access method and database system
US6360228B1 (en) Transactional framework for executing statements involving non-native code
US7853570B2 (en) Method and system for data processing with parallel database systems
US7853584B2 (en) Multi-partition query governor in a computer database system
WO2017063520A1 (zh) 数据库的操作方法及装置
US20160034582A1 (en) Computing device and method for executing database operation command
US8041691B2 (en) Acquiring locks in wait mode in a deadlock free manner
US20150142845A1 (en) Smart database caching
US7571164B2 (en) System and method for deferred database connection configuration
US10318520B2 (en) System and method for reducing communications overhead in a distributed transactions environment by modifying implementation of the transaction end function
US20230098190A1 (en) Data processing method, apparatus, device and medium based on distributed storage
US11263542B2 (en) Technologies for auto discover and connect to a rest interface
CN111581234A (zh) Rac多节点数据库查询方法、装置及系统
WO2022127866A1 (zh) 数据处理方法、装置、电子设备、存储介质
CN110704130A (zh) 一种数据处理方法及装置
US7933948B2 (en) Computer-readable medium to multiplex multiple application server requests over a single database connection
CN113296897B (zh) 一种分布式事务的处理方法、装置、设备和机器可读介质
WO2022002275A1 (zh) 分布式存储过程的运行方法、电子设备及存储介质
US7689549B2 (en) Flashback support for domain index queries
US6725213B1 (en) Method and mechanism for providing external procedures to a database system
WO2022111707A1 (en) Method and apparatus for distributed database transactions using global timestamping

Legal Events

Date Code Title Description
121 Ep: the epo has been informed by wipo that ep was designated in this application

Ref document number: 21832567

Country of ref document: EP

Kind code of ref document: A1

NENP Non-entry into the national phase

Ref country code: DE

32PN Ep: public notification in the ep bulletin as address of the adressee cannot be established

Free format text: NOTING OF LOSS OF RIGHTS PURSUANT TO RULE 112(1) EPC (EPO FORM 1205A DATED 060623)

122 Ep: pct application non-entry in european phase

Ref document number: 21832567

Country of ref document: EP

Kind code of ref document: A1