MYSQL中使用存储过程中变量的使用

[huaihuajio]

比如
delimiter //
create procedure p(in a char(15))
begin
insert into a/*这里用变量a来获得表名称*/ (name) values ('zhangsan');
end;
//
我想用变量a还动态获得表名称，能做到吗？应该怎么做，如果直接写的话会提示错误:找不到表a。能不能做到啊？

[ACMAIN_CHM]

可以用prepare 来试一下。

参考一下下面这个MySQL帮助中的例子。

mysql> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
mysql> SET @a = 3;
mysql> SET @b = 4;
mysql> EXECUTE stmt1 USING @a, @b;

[WWWWA]

要用PREPARE动态执行SQL语句
set @ee=concat('insert into ',a,'(name) values (''zhangsan'')')
PREPARE stmt1 FROM @ee
EXECUTE stmt1

[WWWWA]

set @ee=concat('insert into ',a,'(name) values (\'zhangsan\')')
PREPARE stmt1 FROM @ee
EXECUTE stmt1

[ACMAIN_CHM]

mysql> select * from t5;
+------+----------------------+
| id   | c1                   |
+------+----------------------+
|    1 | 2008-12-31按排期交货 |
|    2 | 2008-12-31?????      |
|    3 | 2008-12-31?????      |
|    4 | 2008-12-31?????      |
|   11 | 111                  |
+------+----------------------+
5 rows in set (0.00 sec)

mysql> delimiter //
mysql> create procedure p(in a char(15))
    -> begin
    ->  set @st = concat('insert into ',a,'(id) values (9)');
    ->  PREPARE stmt1 FROM @st;
    ->  EXECUTE stmt1 ;
    -> end;
    -> //
Query OK, 0 rows affected (0.16 sec)

mysql> delimiter ;
mysql> call p('t5');
Query OK, 1 row affected (0.05 sec)

mysql> select * from t5;
+------+----------------------+
| id   | c1                   |
+------+----------------------+
|    1 | 2008-12-31按排期交货 |
|    2 | 2008-12-31?????      |
|    3 | 2008-12-31?????      |
|    4 | 2008-12-31?????      |
|   11 | 111                  |
|    9 | NULL                 |
+------+----------------------+
6 rows in set (0.00 sec)

mysql>

[huaihuajio]

太感谢大家了。知道该怎么做了~~