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....
认证0级讲师