MySQL Stored Procedure instance

MySQL Stored Procedure instance

The Mysql stored procedure is actually a function, which can execute multiple SQL statements and create temporary variables.
For example, the main functions of the following stored procedure are:
(1) create two temporary variables to count whether there is data from yesterday in the two tables of the database. If there is no temporary variable, insert the statistical data. If there is no temporary variable, do nothing.
(2) This stored procedure is called once every hour. Why is it not called once a day? Because many of the tables queried in this stored procedure are FEDERATED tables, similar to the dblink of Oracle, if the query is performed once a day, the link will not be available (nor can the database variable be modified, at this time, an error 1160-Got an error writing communication packets will be reported as soon as the time is reached, leading to the failure of scheduled tasks) therefore, the flush table statement at the beginning of the stored procedure is added.
(3) Pay attention to the usage and value assignment of variables, comparison and calculation of time.

Drop procedure if exists partition; delimiter // create procedure partition () beginDECLARE partition int default 0; DECLARE partition int default 0; flush table partition; flush table bureau_and_area; flush table connected_low; flush table head; flush table head_and_meter; flush table meter_and_low; flush table uhead_without_bureau; flush table e _ Bureau_link39; flush table e_elemeterhead_link39; flush table e_elemeter_link39; flush table partition; flush table e_managementarea_link39; flush table partition; select count (*) into count_yestaday_data_low from e_unconnect_lowcomputer_statistics where date (e_unconnect_lowcomputer_stat Istics. date) = date (DATE_SUB (NOW (), INTERVAL 1 DAY); -- if no matching option is available, the value of cnt1 is 0 select count (*) INTO count_yestaday_data_head FROM e_unconnect_elemeterhead_statistics where date (e_unconnect_elemeterhead_statistics.date) = date (DATE_SUB (NOW (), INTERVAL 1 DAY); -- if no option is available, the value of cnt1 is 0 IF count_yestaday_data_low = 0 THEN -- INSERT the statistical result INTO the database insert into e_unconnect_lowcomputer_statistics ('date', area_name, lo W_name, low_address) SELECT 'uhead _ date', area_name, low_name, low_address FROM unconnected_low_statistics; else select 'cnt1! = 0'; end if; IF count_yestaday_data_head = 0 THEN -- INSERT the statistical result INTO the database insert into values ('date', area_name, low_name, low_address, meter_name, meter_address, head_name, head_address, bureau_name, low_id, meter_id, uhead_id, bureau_id) SELECT 'date', area_name, low_name, low_address, meter_name, meter_address, head_name, head_address, bureau_name, low_id, meter_id, uhead_id, Bureau_id FROM unconnected_elemeterhead_statistics; else select 'cnt1! = 0'; end if; end // delimiter;

Next, you can create a scheduled task in Mysql. The SQL statement executed in the scheduled task is one sentence, that is, executing the stored procedure:

 call u_head_and_low_pro();