Question

mysql存储过程求大神(拼接字符串)

BEGIN
declare table_name VARCHAR (100);
declare stopFlag int;
declare sql1 VARCHAR (1000);
declare cursor_name cursor
for select TABLE_NAME from information_schema.KEY_COLUMN_USAGE where TABLE_NAME like 'test_2014%';
declare CONTINUE HANDLER FOR NOT FOUND set stopFlag=1;
set @sql1='create or replace view test as select id,name from ';
open cursor_name;
REPEAT
FETCH cursor_name INTO table_name;
begin
set @sql1=CONCAT(@sql1,table_name);
select @sql1;
PREPARE step FROM @sql1;
EXECUTE step;
end;
FETCH cursor_name INTO table_name;
UNTIL stopFlag = 1
END REPEAT;
close cursor_name;
END

如上存储过程，实现的是数据库中有多个表名为test_201409、test_201410....等表格，这些表格结构字段都一样，现在用游标把它们查询出来后，根据这些表创建视图，有一个拼接的过程不会 求大神帮忙看下

拼接后的Sql:
select id,name
from test_201409
union all select id,name from test_201410....

先给80分 好了再给80 求大神
已经解决！

Answer 1

 DROP PROCEDURE IF EXISTS `p_create_view`

CREATE  PROCEDURE p_create_view()
BEGIN
        DECLARE tableName VARCHAR (100);
        DECLARE stopFlag INT;
        DECLARE sql1 VARCHAR (1000);
        DECLARE cursor_name CURSOR  FOR SELECT TABLE_NAME  FROM information_schema.tables WHERE TABLE_NAME  LIKE 'test_2014%';
        DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'  SET stopFlag=1;
        SET @sql1='create  view test as select id,name from ';
        OPEN cursor_name;
        REPEAT
        FETCH cursor_name INTO tableName;
        SELECT   CONCAT(@sql1,tableName,' UNION ALL SELECT  id,name  FROM ') INTO @sql1 FROM DUAL;  
        UNTIL stopFlag  END REPEAT;
        CLOSE cursor_name;   
        SELECT SUBSTR(@sql1,1,LENGTH(@sql1)-31) INTO @sql1 FROM DUAL;
        PREPARE step FROM @sql1;
        EXECUTE step;
END;