MySQL stores statistics and inserts data, and mysql stores data.

MySQL stores statistics and inserts data, and mysql stores data.

The statistical stored procedure is to insert the statistical results into a table, and the background can have scheduled tasks to call this stored procedure. The following services are used to collect statistics on the circulation of goods in the warehouse, including: date, total number of goods, number of warehouse receiving, and number of warehouse picking.

Begin declare ES_COR_CODE VARCHAR (20); -- the variable DECLARE table _ VARCHAR (50) that needs to be defined to receive cursor data; -- DECLARE done int default false, which indicates the end of Data traversal; -- cursor declare cur cursor for select TABLE_NAME FROM (SELECT TABLE_NAME from information_schema.tables WHERE TABLE_NAME LIKE '% wm_item_transfer _ %' AND TABLE_SCHEMA = 'wc _ test') allTables; -- bind the end flag to the cursor declare continue handler for not found set done = TRUE; -- OPEN the cursor OPEN cur; -- start LOOP read_loop: LOOP -- extract the data in the cursor, there is only one, and the same is true for multiple; FETCH cur INTO table _; -- IF done then leave read_loop; end if; SET ES_COR_CODE = substring (table _, 18); SET @ INSERT_ITEM_TRANSFER = CONCAT ('insert INTO wm_item_transfer _ ', ES_COR_CODE,' (WMIT_DATE, WMIT_STOCK_NUM, limit, distinct) SELECT (SELECT date_sub (curdate (), interval 1 day) WMIT_DATE, (select ifnull (SUM (WMST_NOW_NUMBER), 0) FROM wm_stock _ ', ES_COR_CODE,') WMIT_STOCK_NUM, (select ifnull (SUM (limit), 0) FROM wm_stock _ ', ES_COR_CODE, 'where BETWEEN date_sub (curdate (), interval 1 day) and now () wmit_1__item_num, (select ifnull (SUM (WMOO_OUT_NUMBER), 0) FROM wm_out_order _ ', ES_COR_CODE, 'where SURE_DATE BETWEEN date_sub (curdate (), interval 1 day) and now () then'); PREPARE INSERT_ITEM_TRANSFER FROM @ response; EXECUTE response; end loop; -- CLOSE the cursor CLOSE cur; END