SP 程式碼：
DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`sp_row_column_wrap`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_row_column_wrap`(IN $schema_name varchar(64),
IN $table_name varchar(64))
BEGIN
declare cnt int(11);
declare $table_rows int(11);
declare i int(11);
declare j int(11);
declare s int(11);
declare str varchar(255);
-- Get the column number of the table
select count(1) from information_schema.columns where table_schema=$schema_name and table_name=$table_name into cnt;
-- Get the row number of the table
select table_rows from information_schema.tables where table_schema = $schema_name and table_name=$table_name into $table_rows;
-- Check whether the table exists or not
drop table if exists test.temp;
create table if not exists test.temp (`1` varchar(255) not null);
-- loop1 start
set i = 0;
loop1:loop
if i = $table_rows-1 then
leave loop1;
end if;
set @stmt1 = concat(`alter table test.temp add ``,i+2,`` varchar(255) not null`);
prepare s1 from @stmt1;
execute s1;
deallocate prepare s1;
set @stmt1 = ``;
set i = i + 1;
end loop loop1;
-- loop1 end;
set s = 0;
-- loop2 start
loop2:loop
-- leave loop2
if s=cnt then
leave loop2;
end if;
set @stmt2 = concat(`select column_name from information_schema.columns where table_schema="`,$schema_name,
`" and table_name="`,$table_name,`" limit `,s,`,1 into @temp;`);
prepare s2 from @stmt2;
execute s2;
deallocate prepare s2; set @stmt2 = ``; set j=0;
set str = ` select `;
-- Loop3 start
loop3:loop
if j = $table_rows then
leave loop3;
end if;
set @stmt3 = concat(`select `,@temp,` from `,$schema_name,`.`,$table_name,` limit `,j,`,1 into @temp2;`);
prepare s3 from @stmt3;
execute s3;
set str = concat(str,`"`,@temp2,`"`,`,`);
deallocate prepare s3;
set @stmt3 = ``;
set j = j+1;
end loop loop3;
set str = left(str,length(str)-1);
-- insert new data into table
set @stmt4 = concat(`insert into test.temp`,str,`;`);
prepare s4 from @stmt4;
execute s4;
deallocate prepare s4;
set @stmt4 = ``;
set s=s+1;
end loop loop2;
END$$
DELIMITER ;
以下是測試結果：
======
select * from a;
select * from b;
select * from salary;
call sp_row_column_wrap(`test`,`a`);
select * from test.temp;
call sp_row_column_wrap(`test`,`b`);
select * from test.temp;
call sp_row_column_wrap(`test`,`salary`);
select * from test.temp;
query result(2 records)
| aid | title |
| 1 | 111 |
| 2 | 222 |
query result(3 records)
| bid | aid | p_w_picpath | time |
| 1 | 2 | 1.gif | 2007-08-08 |
| 2 | 2 | 2.gif | 2007-08-09 |
| 3 | 2 | 3.gif | 2007-08-08 |
query result(7 records)
| id | cost | des | Autoid |
| 1 | 10 | aaaa | 1 |
| 1 | 15 | bbbb | 2 |
| 1 | 20 | cccc | 3 |
| 2 | 80 | aaaa | 4 |
| 2 | 100 | bbbb | 5 |
| 2 | 60 | dddd | 6 |
| 3 | 500 | dddd | 7 |
query result(2 records)
| 1 | 2 |
| 1 | 2 |
| 111 | 222 |
query result(4 records)
| 1 | 2 | 3 |
| 1 | 2 | 3 |
| 2 | 2 | 2 |
| 1.gif | 2.gif | 3.gif |
| 2007-08-08 | 2007-08-09 | 2007-08-08 |
query result(4 records)
| 1 | 2 | 3 | 4 | 5 | 6 | 7 |
| 1 | 1 | 1 | 2 | 2 | 2 | 3 |
| 10 | 15 | 20 | 80 | 100 | 60 | 500 |
| aaaa | bbbb | cccc | aaaa | bbbb | dddd | dddd |
| 1 | 2 | 3 | 4 | 5 | 6 | 7 |