MySQL stored procedures

A MySQL stored procedure is actually a function in which you can execute multiple SQL statements and create temporary variables.
For example, the following main functions of the stored procedure are
(1) Create two temporary variables to count the two tables in the database there is no data from yesterday, if not on the insertion of statistics, if there is nothing to do.
(2) This stored procedure is called once every one hours, why not call it once a day? Because this stored Procedure query table has a lot of federated table, similar to Oracle's Dblink, if one day query, the link is not (modify the database variable also not, this time will appear to a run will error 1160-got an error writing Communication packets, which causes the scheduled task to fail to execute properly), the Flush table statement that started the stored procedure is also added.
(3) Here to pay attention to the use and assignment of variables, time comparison and operation

 Drop procedure IF EXISTS U_head_and_low_pro;Delimiter// Create procedure u_head_and_low_pro ()beginDECLARE count_yestaday_data_low INT DEFAULT 0;DECLARE Count_yestaday_data_head INT DEFAULT 0; FLUSH TABLE Unconnected_low_statistics; 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 e_lwrcomputer_link39; FLUSH TABLE e_managementarea_link39; FLUSH TABLE r_elemeter_elemeterhead_link39; FLUSH TABLE r_lwrcomputer_elemeter_link39; FLUSH TABLE r_managementarea_bureau_link39; Select Count(*)  into Count_yestaday_data_low  from E_unconnect_lowcomputer_ Statistics WHERE DATE(e_unconnect_lowcomputer_statistics.  Date) = Date(Date_sub (now (),INTERVAL 1 Day)); --If there is no qualifying option, 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 there is no qualifying option, the value of Cnt1 is 0IF count_yestaday_data_low = 0 Then--statistical results inserted into the database         INSERT  into e_unconnect_lowcomputer_statistics ( ' Date ', Area_name,            Low_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--statistical results inserted into the database         INSERT  into e_unconnect_elemeterhead_statistics ( ' 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, lo w_id, meter_id, uhead_id, bureau_id  from unconnected_elemeterhead_statistics;             ELSE SELECT ' cnt1!=0 ';        END IF;  End //delimiter;

The next step is to create a timed task in MySQL, and the SQL statement executed in the timed task is executed in a sentence, that is, to execute the stored procedure:

call u_head_and_low_pro();

MySQL stored procedures