存储过程代码如下
DELIMITER $$
USE `database`$$
DROP PROCEDURE IF EXISTS `test`$$
#选用database数据库,建立一个叫test的存储过程,2个输入,分别叫a1,a2
CREATE DEFINER=`root`@`localhost` PROCEDURE `test`(IN a1 VARCHAR(20),IN a2 VARCHAR(20))
BEGIN
#此处定义几个变量,a1设为表名,a2设为值
SET @tbl_name=a1;
SET @v2=a2;
#参照着网上搜的让变量做表名的方法,定义俩想要prepare的变量
#如果a1表不存在就创建一个叫a1的表,然后往里赋值
SET @command1 = CONCAT("create table if not exists ",@tbl_name,"(id varchar(20) not null) default charset=utf8;");
SET @command2 = CONCAT("insert into ",@tbl_name,"(commodity) VALUES(",@v2,");");
PREPARE stmt1 FROM @command1;
PREPARE stmt2 FROM @command2;
EXECUTE stmt1;
EXECUTE stmt2;
END$$
DELIMITER ;
DELIMITER $$
USE `database`$$
DROP PROCEDURE IF EXISTS `test`$$
#选用database数据库,建立一个叫test的存储过程,2个输入,分别叫a1,a2
CREATE DEFINER=`root`@`localhost` PROCEDURE `test`(IN a1 VARCHAR(20),IN a2 VARCHAR(20))
BEGIN
#此处定义几个变量,a1设为表名,a2设为值
SET @tbl_name=a1;
SET @v2=a2;
#参照着网上搜的让变量做表名的方法,定义俩想要prepare的变量
#如果a1表不存在就创建一个叫a1的表,然后往里赋值
SET @command1 = CONCAT("create table if not exists ",@tbl_name,"(id varchar(20) not null) default charset=utf8;");
SET @command2 = CONCAT("insert into ",@tbl_name,"(commodity) VALUES(",@v2,");");
PREPARE stmt1 FROM @command1;
PREPARE stmt2 FROM @command2;
EXECUTE stmt1;
EXECUTE stmt2;
END$$
DELIMITER ;