2 tan078 Tan078 于 2017.09.14 14:52 提问

mysql中使用带输入参数的存储过程建表

DECLARE table_name VARCHAR(50);
DECLARE table_pre VARCHAR(20);
DECLARE table_mid VARCHAR(10);
DECLARE table_end VARCHAR(30);
DECLARE sql_text VARCHAR(2000);
DECLARE i INT;
DECLARE j INT;
DECLARE k INT;
SET table_name='';
SET table_pre='tbl_';
SET table_mid='_';
SET table_end='_measurementdata';
SET sql_text='';
SET i=1;
SET j=1;
SET k=1;

SET table_name=CONCAT(table_pre,i,table_mid,j,table_mid,k,table_end);
SET sql_text=CONCAT('CREATE TABLE ', table_name, '(
DataID int(11) NOT NULL AUTO_INCREMENT,
DataValue decimal(10,3) NULL,
MeaFileID int(11) NULL,
NominalID int(11) NULL,
PRIMARY KEY (DataID)

) ' );

SELECT sql_text;
SET @sql_text=sql_text;
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END

各位大神,想把i,j,k换成输入参数,该怎么改呢?今天刚开始学习存储过程,先感谢各位。

1个回答

qq_21976267
qq_21976267   2017.09.14 16:17
已采纳

create procedure pro_exemple
(
in table_pre varchar(20),
in i int,
in table_mid varchar(2),
in j int,
in k int,
in table_end varchar(20)
)
begin
set @table_name=CONCAT(table_pre,i,table_mid,j,table_mid,k,table_end);
set @sql_text=CONCAT('create table ',@table_name,' (
DataID int(11) NOT NULL AUTO_INCREMENT,
DataValue decimal(10,3) NULL,
MeaFileID int(11) NULL,
NominalID int(11) NULL,
PRIMARY KEY (DataID))');
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
end
go
call pro_exemple ('sad',1,'_',2,3,'asdf');

Tan078
Tan078 该方法经过测试,是最正确的,感谢,不过table_name拼接j,k那里多写了一个参数,删掉或者再添加一个输入参数就可以了
2 个月之前 回复
Csdn user default icon
上传中...
上传图片
插入图片