Frequency scheduling method and system based on row cloud database storage process
By using variables and parameters to pass the frequency and detailed information of the scheduling task in stored procedures and creating a general stored procedure, the problem that traditional database scheduling methods cannot handle multiple frequency scheduling tasks is solved, and efficient and flexible scheduling management and rapid problem positioning are achieved.
Patent Information
- Application Number
- CN202510333411.0
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2025-03-20
- Publication Date
- 2025-06-20
AI Technical Summary
Traditional database scheduling methods cannot effectively handle multiple frequency scheduling tasks, resulting in cumbersome configuration of scheduling tasks, increasing the workload of development and operation and maintenance personnel, and it is difficult to quickly locate and solve data problems.
By passing the frequency and details of the scheduling task using variables and parameters in stored procedures, create a general stored procedure that can handle multiple frequency scheduling tasks, and use scheduling tools to achieve automatic triggering and operation monitoring.
It realizes unified management and efficient execution of multiple frequency scheduling tasks, significantly improves the flexibility and efficiency of database scheduling, reduces management costs, and can quickly locate and solve problems when problems arise.
Smart Images
Figure CN120179673A_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the field of cloud database storage, and in particular to a frequency scheduling method and system based on a cloud database storage process. Background Art
[0002] Traditional database scheduling methods are often based on a single frequency, such as supporting only daily or monthly scheduling, which cannot meet complex and changing business needs. Especially when it is necessary to handle multiple frequency scheduling tasks such as daily, weekly, monthly, and seasonal, traditional methods usually require writing multiple independent stored procedures to be called by the scheduling program, which is particularly cumbersome and inefficient.
[0003] A stored procedure is a set of SQL statements to perform specific functions. It is stored in the database and can be called by the user by specifying the name and giving parameters. A stored procedure can accept input parameters, return output parameters, return result sets, and return values. Using stored procedures can simplify complex operations and improve code reusability, security, and performance.
[0004] When calling stored procedures with different frequencies in various databases, it is often necessary to write the logic of different frequencies into separate stored procedures. If the logic of different frequencies inserts data into a data table at the same time, it is also necessary to plan the calling sequence of each stored procedure, otherwise there may be a risk of locking the table. This will not only make the scheduling task configuration particularly cumbersome, increase the workload of development and operation and maintenance personnel, but also make it difficult to quickly locate and solve data problems.
[0005] Disadvantages of existing technologies:
[0006] 1) When logics with different frequencies insert data into a data table at the same time, it is easy to cause table lock;
[0007] 2) Calling stored procedures one by one makes the scheduling task configuration very cumbersome, increasing the workload of developers and operation and maintenance personnel;
[0008] 3) When data problems occur, it is difficult to quickly locate and resolve problems in the logic. Summary of the invention
[0009] In view of the above problems, the present invention is proposed to provide a frequency scheduling method and system based on a stored procedure of a cloud database, which overcomes the above problems or at least partially solves the above problems.
[0010] According to one aspect of the present invention, a frequency scheduling method based on a stored procedure of a cloud database is provided, and the frequency scheduling method comprises:
[0011] In the stored procedure, use variables and parameters to pass the frequency and details of the scheduled task;
[0012] Process multiple frequency scheduling tasks;
[0013] Create a general stored procedure capable of processing multiple frequency scheduling tasks;
[0014] Automatic triggering of scheduling tasks and monitoring of their running status.
[0015] Optionally, in the stored procedure, using variables and parameters to pass the frequency and details of the scheduling tasks specifically includes:
[0016] Through dynamic SQL statements, based on the input date parameter judgment, by default, execute the weekly, monthly, and quarterly frequency logics respectively on Monday, the beginning of the month, and the beginning of the quarter;
[0017] Set the weekly frequency logic to be executed on Tuesday according to different business requirements.
[0018] Optionally, the creation of a general stored procedure capable of processing multiple frequency scheduling tasks specifically includes:
[0019] Use variables sql_info_d, sql_info_w, sql_info_m, and sql_info_q to represent the SQL information that needs to be executed for the daily, weekly, monthly, and quarterly frequency logics respectively;
[0020] At the same time, there needs to be a frequency identification freq_flag field in the target table, which is prepared when assigning values to the above variables to distinguish data with different frequencies in the target table.
[0021] Optionally, the automatic triggering of scheduling tasks and monitoring of their running status specifically includes:
[0022] Configure the shell command for executing the stored procedure into the scheduling tool, select to execute daily, configure the dependency relationship with other tasks, and it can only be executed after certain tasks are completed. At the same time, select a fixed time of day for the task to execute;
[0023] Real-time monitor the running status of tasks in the scheduling interface, and view the running records and logs of historical tasks.
[0024] Optionally, the creation of a general stored procedure capable of processing multiple frequency scheduling tasks specifically includes:
[0025] Use the scheduling tool to define the stored procedure that needs to be executed;
[0026] Set the task to execute daily, and judge that the logics of different frequencies have been implemented in the stored procedure;
[0027] Determine the Shell commands required to execute the stored procedure, and configure them in the scheduling command to ensure the connectivity between the scheduling tool and the server and the connectivity of the database connected in JDBC mode in the server.
[0028] Optionally, defining the stored procedure to be executed using the scheduling tool includes: task name, execution logic, and required resources.
[0029] Optionally, the frequency scheduling method further includes:
[0030] Use the scheduling tool to start the configured task, input different date parameters, and after waiting for the execution to complete, connect to the Xingyun database to query whether the execution result is consistent with the expectation;
[0031] If there is logic that should be executed but is not executed or should not be executed but is actually executed, it is necessary to check whether the writing season judgment logic of the second-step stored procedure is correct;
[0032] After the task is executed, view the execution status on the scheduling page. If the execution time of the task is too long, adjust the task resources as needed or optimize the SQL.
[0033] The present invention also provides a frequency scheduling system based on a stored procedure of the Xingyun database, applying the above-mentioned frequency scheduling method based on a stored procedure of the Xingyun database. The frequency scheduling system further includes:
[0034] A scheduling configuration module, used to use variables and parameters in the stored procedure to pass the frequency and detailed information of the scheduling task;
[0035] A server background execution module, used to process various frequency scheduling tasks;
[0036] A stored procedure execution module, used to create a general stored procedure capable of processing various frequency scheduling tasks;
[0037] A task execution status and log module, used for automatic triggering of scheduling tasks and monitoring of running status.
[0038] The frequency scheduling method and system based on a stored procedure of the Xingyun database provided by the present invention. The frequency scheduling method includes: using variables and parameters in the stored procedure to pass the frequency and detailed information of the scheduling task; processing various frequency scheduling tasks; creating a general stored procedure capable of processing various frequency scheduling tasks; automatic triggering of scheduling tasks and monitoring of running status. By writing a general stored procedure, combined with parameterized design and a scheduling tool, unified management and efficient execution of various frequency scheduling tasks are achieved.
[0039] The above description is only an overview of the technical solution of the present invention. In order to more clearly understand the technical means of the present invention, it can be implemented according to the contents of the specification. In order to make the above and other purposes, features and advantages of the present invention more obvious and easy to understand, the specific implementation methods of the present invention are listed below. BRIEF DESCRIPTION OF THE DRAWINGS
[0040] In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the accompanying drawings required for use in the description of the embodiments will be briefly introduced below. Obviously, the accompanying drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other accompanying drawings can be obtained based on these accompanying drawings without paying creative work.
[0041] Figure 1 A flowchart of a frequency scheduling method based on a stored procedure of a Xingyun database provided in an embodiment of the present invention;
[0042] Figure 2 A block diagram of a frequency scheduling system based on a stored procedure in a cloud database provided in an embodiment of the present invention. DETAILED DESCRIPTION
[0043] The exemplary embodiments of the present disclosure will be described in more detail below with reference to the accompanying drawings. Although the exemplary embodiments of the present disclosure are shown in the accompanying drawings, it should be understood that the present disclosure can be implemented in various forms and should not be limited by the embodiments set forth herein. On the contrary, these embodiments are provided to enable a more thorough understanding of the present disclosure and to fully convey the scope of the present disclosure to those skilled in the art.
[0044] The terms "comprises" and "having" and any variations thereof in the description embodiments, claims and drawings of the present invention are intended to cover non-exclusive inclusions, for example, including a series of steps or units.
[0045] The technical solution of the present invention is further described in detail below in conjunction with the accompanying drawings and embodiments.
[0046] like Figure 1 As shown, a method for scheduling stored procedures based on Xingyun database includes:
[0047] Determine requirements and environment configuration, including:
[0048] First, you need to clarify the frequency of business needs, including daily, weekly, monthly, quarterly, semi-annual, and annual frequencies, as well as the trigger conditions (i.e. dates) corresponding to each frequency. In addition, you must ensure that the Xingyun database environment has been built and has the necessary permissions and resources.
[0049] Design and write stored procedures, including:
[0050] Determine the name of the stored procedure, define the parameters input to the stored procedure (e.g., v_date, an 8-digit date), and make judgments based on the input parameters and requirements. Taking the need to perform batch processing every Monday, the first day of each month, and the beginning of each quarter as an example, for weekly frequency, use to_char((to_date(v_date,'YYYY-MM-DD')),'D') to determine whether the passed parameter is Monday by checking if it equals '2'. For monthly frequency, use
[0051] to_char(trunc(to_date(v_date,'YYYY-MM-DD'),'MM') + 1,'YYYYMMDD') to check if it is consistent with the passed v_date to determine whether the passed parameter is the first day of the month. For quarterly frequency, directly check if the passed date is the beginning of the quarter (0101, 0401, 0701, 1001).
[0052] Convert the business logic into the technical logic of SQL to write the main body of the stored procedure, and execute the corresponding logic based on the input parameters and the above judgment methods.
[0053] Scheduling configuration and monitoring, including:
[0054] Use the scheduling tool to define the stored procedure to be executed, including the task name, execution logic, required resources, etc. Here, set the task to execute daily because the logic for judging different frequencies has been implemented in the stored procedure. Determine the Shell command required to execute the stored procedure and configure it in the scheduling command to ensure the connectivity between the scheduling tool and the server and the connectivity of the database connected in JDBC mode in the server.
[0055] Testing and optimization, including:
[0056] Use the scheduling tool to start the configured task, input different date parameters, and after waiting for the execution to complete, connect to the Cloud Database to query whether the execution result is consistent with the expectation. If there is logic that should be executed but is not executed or should not be executed but is actually executed, check whether the writing and judgment logic of the stored procedure in the second step is correct. After the task is executed, the execution status can be viewed on the scheduling page. If the task execution takes too long, adjust the task resources as needed or perform SQL optimization.
[0057] Documentation and training, including:
[0058] For stored procedures with complex logic, be sure to keep the mapping document and requirement document for future modification or research. At the same time, relevant personnel can also be trained to ensure that they can also master this method proficiently.
[0059] Ensure that the method of integrating various scheduling based on the storage procedure of Xingyun Database can achieve good scheduling effects in actual applications and improve work efficiency.
[0060] A scheduling system based on the storage procedure of Xingyun Database, comprising:
[0061] Parametric design, including:
[0062] Improve the flexibility and reusability of the storage procedure. In the storage procedure, variables and parameters are used to pass the frequency and detailed information of the scheduling task. Through dynamic SQL statements, according to the input date parameter judgment, by default, the weekly, monthly, and quarterly frequency logics are executed respectively on Monday, the beginning of the month, and the beginning of the quarter. It is also possible to set the weekly frequency logic to be executed on Tuesday according to different business requirements.
[0063] General storage procedure design, including:
[0064] Create a general storage procedure that can handle scheduling tasks of multiple frequencies. Use variables sql_info_d, sql_info_w, sql_info_m, and sql_info_q to represent the SQL information that needs to be executed for the daily, weekly, monthly, and quarterly frequency corresponding logics respectively. At the same time, there needs to be a frequency identifier (freq_flag) field in the target table, which is prepared when assigning values to the above variables to distinguish data of different frequencies in the target table.
[0065] Scheduling task configuration and monitoring management, including:
[0066] Realize the automatic triggering of scheduling tasks and the monitoring of the running situation. Configure the shell command for executing the storage procedure into the scheduling tool. Select to execute daily, and the dependency relationship with other tasks can be configured, and it can be executed only after certain tasks are completed. At the same time, it is also possible to select to execute the task at a fixed time every day. Monitor the running situation of the task in real time on the scheduling interface, and view the running records and logs of historical tasks.
[0067] Such as Figure 2 As shown, the scheduling configuration module passes the date parameter and the shell command to the server background execution module, and sends them to the storage procedure execution module according to the date parameter, and obtains the log from the storage procedure execution module and sends it to the task execution situation and log module.
[0068] Beneficial effects: The present invention proposes a method for integrating various scheduling based on the storage procedure of Xingyun Database. By writing a general storage procedure, combined with parametric design and scheduling tools, unified management and efficient execution of scheduling tasks of multiple frequencies are realized. This method supports scheduling tasks of various frequencies such as daily, weekly, monthly, and quarterly, meets complex and changeable business requirements, significantly improves the flexibility and efficiency of database scheduling, and reduces management costs.
[0069] In the above specific embodiments, the object, technical solution and beneficial effects of the present invention have been further described in detail. It should be understood that the above are only specific embodiments of the present invention and are not used to limit the protection scope of the present invention. Any modifications, equivalent replacements, improvements, 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 frequency scheduling method based on the stored procedure of Xingyun database, characterized in that: The frequency scheduling method comprises: In the stored procedure, use variables and parameters to pass the frequency and details of the scheduled task; Handle various frequency scheduling tasks; Create a general stored procedure that can handle multiple frequency scheduling tasks; Automatically trigger scheduling tasks and monitor their operation status.
2. According to claim 1, a frequency scheduling method based on a stored procedure of a cloud database is characterized in that: In the stored procedure, variables and parameters are used to pass the frequency and detailed information of the scheduled task, including: Through dynamic SQL statements, according to the input date parameters, the default execution of weekly, monthly, and quarterly frequency logic is based on Monday, the beginning of the month, and the beginning of the quarter respectively; Set up weekly logic execution on Tuesdays based on different business needs.
3. According to claim 1, a frequency scheduling method based on a stored procedure of a cloud database is characterized in that: The creation of a general stored procedure capable of handling multiple frequency scheduling tasks specifically includes: Use variables sql_info_d, sql_info_w, sql_info_m, and sql_info_q to represent the SQL information that needs to be executed for the logic corresponding to the daily, weekly, monthly, and quarterly frequencies respectively; At the same time, there needs to be a frequency identification freq_flag field in the target table, which is prepared when assigning values to the above variables to distinguish data of different frequencies in the target table.
4. According to claim 1, a frequency scheduling method based on a stored procedure of a cloud database is characterized in that: The automatic triggering of the scheduling task and the monitoring of the operation status specifically include: Configure the shell command for executing the stored procedure into the scheduling tool, select daily execution, configure the dependency with other tasks, and select the task to be executed only after certain tasks are completed. At the same time, select the task to be executed at a fixed time every day. Monitor the task running status in real time on the scheduling interface, and view the running records and logs of historical tasks.
5. According to claim 1, a frequency scheduling method based on a stored procedure of a cloud database is characterized in that: The creation of a general stored procedure capable of handling multiple frequency scheduling tasks specifically includes: Use the scheduling tool to define the stored procedures that need to be executed; The task is set to be executed every day, and the logic for determining different frequencies has been implemented in the stored procedure; Determine the Shell command that needs to be executed to execute the stored procedure and configure it in the scheduling command to ensure the connectivity between the scheduling tool and the server and the connectivity between the server and the database using JDBC.
6. A frequency scheduling method based on a stored procedure of a cloud database according to claim 5, characterized in that: The storage process to be executed is defined by using the scheduling tool, including: task name, execution logic, and required resources.
7. The frequency scheduling method based on the stored procedure of the Xingyun database according to claim 1 is characterized in that: The frequency scheduling method further includes: Use the scheduling tool to call up the configured task, enter different date parameters, wait for the execution to complete, and then connect to the Xingyun database to query whether the execution result is consistent with the expectation; If there is logic that should be executed but is not executed, or logic that should not be executed but is actually executed, you need to check whether the writing and judgment logic of the second step stored procedure is correct; After the task is executed, check the execution status on the scheduling page. If the task takes too long to execute, adjust the task resources or perform SQL optimization as needed.
8. A frequency scheduling system based on a stored procedure of a Xingyun database, applying a frequency scheduling method based on a stored procedure of a Xingyun database as described in any one of claims 1 to 7, characterized in that: The frequency scheduling system also includes: The scheduling configuration module is used to pass the frequency and details of the scheduled tasks using variables and parameters in the stored procedure; The server background execution module is used to handle various frequency scheduling tasks; The stored procedure execution module is used to create a general stored procedure that can handle multiple frequency scheduling tasks; The task execution status and log module is used to automatically trigger the scheduling tasks and monitor the operation status.