Mysql multi-log table result set splicing stored procedure, mysql Stored Procedure

Mysql multi-log table result set splicing stored procedure, mysql Stored Procedure

Generally, a single-day log only records the log information of the current day. If you need to view the log information within January, You Need To splice the result set of the daily log table, and union is usually used.

Storage Process:

drop PROCEDURE if  EXISTS unionSp;DELIMITER //create procedure unionSp(sTime varchar(32), eTime varchar(32),tchema varchar(32))begindeclare sqlVar varchar(1024000);declare rest int;declare tableName varchar(1024);set rest = 100;set sqlVar='';while rest > 0 do   set sTime = (select DATE_FORMAT((select ADDDATE(sTime,1)),'%Y%m%d')); set tableName=CONCAT('tbl_req_',sTime); select count(1) from information_schema.tables where table_name = tableName  and TABLE_SCHEMA=tchema into @cnt; if @cnt != 0 thenif rest=1 then set sqlVar=CONCAT(sqlVar,' SELECT DISTINCT channel_id,app_id from tbl_req_',sTime);ELSE set sqlVar=CONCAT(sqlVar,' SELECT DISTINCT channel_id,app_id from tbl_req_',sTime,' UNION');END IF;END if;set rest = DATEDIFF(eTime,sTime);END while;set @v_s=sqlVar;prepare stmt from @v_s;EXECUTE stmt;DEALLOCATE PREPARE stmt;end;// DELIMITER;call unionSp('20140730','20140930','biz_date')

Union: union means to combine the results of two or more queries.
Required: the number of columns in the Two Queries must be consistent.
Recommendation: The types of columns can be different, but we recommend that you query each column, and you want the corresponding types to be the same
Data from multiple tables: The names of the columns retrieved from multiple SQL statements may be inconsistent. The column names of the first SQL statement shall prevail.
If the rows extracted from different statements are identical (the values of each column are the same), union merges the same rows and retains only one row. We can also understand that union removes duplicate rows.
If you do not want to remove duplicate rows, you can use union all

How does MYSQL Insert the result set returned by the stored procedure into the table?

There are two types of table values returned from the stored procedure: 1. the stored procedure uses the float parameter to specify the cursor varying output item at the same time. the caller can use the while and fetch loops to traverse the buoy. 2. directly insert the result set returned by the stored procedure into the table, that is, use the insert into table name exec stored procedure. in this way, note that the result set columns returned by the stored procedure must exactly correspond to the insert columns. You can specify the column names in the insert statements to ensure the corresponding relationship. benchmark Test: create table # tmp (colx int, coly int) insert into # tmp values (1, 2) insert into # tmp values (2, 3) insert into # tmp values (3, 4) select * from # tmpGO ---- create proc sp_c @ cur cursor varying OUTPUTASbeginset @ cur = CURSOR for select colx from # tmpopen @ cur/* This process returns the CURSOR, the cursor is the query result of the colx column */endGO ---- create proc sp_dasselect coly from # tmp/* This process returns the query result of the coly column */go ---- create create proc sp_easbegindeclare @ x intdeclare @ cur cursor ---- receives the cursor, and traverse the cursor EXEC sp_c @ cur OUTPUTfetch next from @ cur into @ xwhile (@ FETCH_STATUS = 0) beginprint @ xfetch next from @ cur into @ xENDclose @ curdeallocate @ cur ---- re-insert the column value returned by the stored procedure into the source table insert into # tmp (coly) EXEC sp_dselect * from # tmpendGOEXEC sp_edrop proc sp_cdrop proc sp_d

How does mysql put the result set returned by two stored procedures into the same table?

Cannot be merged into the same stored procedure? If not, you can create only one table, and then operate the table in both processes.