动态SQL获取参数并在LIKE中将其用于存储过程中创建的新表
为此，请使用准备好的语句。让我们首先创建一个表-
create table DemoTable1973 ( StudentId int, StudentName varchar(20) );
使用插入命令在表中插入一些记录-
insert into DemoTable1973 values(101,'Chris'); insert into DemoTable1973 values(102,'John Doe'); insert into DemoTable1973 values(103,'David'); insert into DemoTable1973 values(104,'John Smith');
使用select语句显示表中的所有记录-
select * from DemoTable1973;
这将产生以下输出-
+-----------+-------------+ | StudentId | StudentName | +-----------+-------------+ | 101 | Chris | | 102 | John Doe | | 103 | David | | 104 | John Smith | +-----------+-------------+ 4 rows in set (0.00 sec)
以下是创建存储过程和具有LIKE子句的新表的查询，该表的值来自过程调用-
DELIMITER //
create PROCEDURE demo_create(IN newTableName varchar(20),IN tbl varchar(20))
BEGIN
DROP TABLE IF EXISTS newTableName;
SET @query= CONCAT('CREATE TABLE newTableName as SELECT * from DemoTable1973 WHERE StudentName like ''%',tbl,'%''');
PREPARE ps FROM @query;
EXECUTE ps;
END
//
DELIMITER ;使用调用命令调用存储过程-
call demo_create('newTableName','John');使用select语句显示表中的所有记录-
select * from newTableName;
这将产生以下输出-
+-----------+-------------+ | StudentId | StudentName | +-----------+-------------+ | 102 | John Doe | | 104 | John Smith | +-----------+-------------+ 2 rows in set (0.00 sec)