MySQL Stored Procedures and Timed Tasks - Introduction and Practice

What is a stored procedure

Storage routines are a set of sql statements stored on a database server that are executed by calling a specified name in a query.

  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 ${proceName}; 

DELIMITER //
CREATE PROCEDURE ${proceName}() 
(   
[${Parameter type}] ${Parameter Name} ${Parameter type}
)   
BEGIN
  ${proceBody}
END//
DELIMITER ;

#Execute Proc() stored procedure
CALL ${proceName}(); 

How to use

#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: ${Author}
-- @date  : ${Time}
-- @param : int - ${Parameter Name} - 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!!!!!!!!!!

Management of Stored Procedures

# View all stored procedure information
show procedure status;


# Delete stored procedures
drop procedure ${procedureName};

# View content information for stored procedures
show create procedure ${procedureName};

Management of Timed Tasks

# 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 ${eventName} 
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 ${procedureName}(); 

# Start a timed task
ALTER EVENT ${evenName} ENABLE;    

# Close a timed task
ALTER EVENT ${evenName} DISABLE;  

# Delete Timed Tasks
DROP EVENT IF EXISTS ${evenName};

Simple example

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();

Tags: Stored Procedure SQL Database MySQL

Posted on Fri, 17 Jul 2020 11:04:34 -0400 by amites