MySQL Stored Procedures and Timed Tasks - Introduction and Practice

1. A stored procedure is a set of SQL statements stored in a database
2. Stored procedures are a method defined in mysql
3. Changed methods can be invoked by calling the method name
4. Stored procedure associations are a database as well as a Schema

Structure of stored procedures

DROP PROCEDURE IF EXISTS $; DELIMITER // CREATE PROCEDURE $() ( [$] $ $ ) BEGIN $ END// DELIMITER ; #Execute Proc() stored procedure CALL $();

#Define a simple stored procedure # First determine if there is a Proc() stored procedure, thendropfall DROP PROCEDURE IF EXISTS Proc; # Create Proc() Stored Procedure /* Method Description -- @author: $ -- @date : $ -- @param : int - $ - Parameter Description -- @return: Specific information */ DELIMITER // CREATE PROCEDURE Proc() BEGIN SELECT * FROM user; END// DELIMITER ; #Execute Proc() stored procedure CALL Proc();

Note that the purpose of the "DELIMITER //" statement is to set the terminator of MYSQL to //, because the default statement terminator of MYSQL is a semicolon; to avoid contact with stored procedures
Conflicting SQL statement terminators in require DELIMITER to change the terminator of the stored procedure and end the stored procedure with END//.
DelIMITER is used after the stored procedure has been defined; restore the default terminator.DELIMITER can also specify other symbols as terminators!!!!!!!!!!

# View all stored procedure information show procedure status; # Delete stored procedures drop procedure $; # View content information for stored procedures show create procedure $;

# Whether the timed task is on SHOW VARIABLES LIKE '%event_sche%'; # Open Timer Task (Start Effective) # Modify Profile/etc/my.conf event_scheduler=ON # Open Timer Task (Restart Failed) SET GLOBAL event_scheduler = 1; # Close the timer task (restart fails) SET GLOBAL event_scheduler = 0; # View all timed task information SHOW EVENTS ; # Timed Task Structure # Create a timed task (example) CERATE EVENT IF NOT EXISTS $ ON SCHEDULE every 30 second #Execute every 30 seconds ON COMPLETION PRESERVE #Eve is set to disable after the timer task is completed, if PRESERVE is added before not is deleted DO CALL $(); # Start a timed task ALTER EVENT $ ENABLE; # Close a timed task ALTER EVENT $ DISABLE; # Delete Timed Tasks DROP EVENT IF EXISTS $;

Execute stored procedures every Monday

# Stored Procedures (determine if today is Monday) DELIMITER // CREATE PROCEDURE p_clear_author_rank_integral() BEGIN DECLARE weed_index INT DEFAULT 0; SET weed_index = dayofweek(now()); IF weed_index=2 THEN update user set rank_integral=0 where author.id!=0; END IF; END// DELIMITER ; #Timed tasks (daily) CERATE EVENT IF NOT EXISTS e_clear_author_rank_integral ON SCHEDULE every 1 DAY ON COMPLETION PRESERVE DO CALL p_clear_author_rank_integral();

17 July 2020, 11:05 | Views: 7824