MySQL – Dynamic SQL with EXECUTE and PREPARE Statements_MySQL
程序员文章站
2022-05-13 11:22:13
...
MySQL supports Dynamic SQL with the help of EXECUTE and PREPARE statements. Suppose you have a scenario where you need to pass table name as parameter value and returns all column values, you can use Dynamic SQL.
Let us create this table and data.
CREATE TABLE TestTable (ID INT, Col VARCHAR(4));
INSERT INTO TestTable (ID, Col)
SELECT 1, 'A'
UNION ALL
SELECT 1, 'B'
UNION ALL
SELECT 1, 'C'
UNION ALL
SELECT 2, 'A'
UNION ALL
SELECT 2, 'B'
UNION ALL
SELECT 2, 'C'
UNION ALL
SELECT 2, 'D'
UNION ALL
SELECT 2, 'E';
Now pass the table name as a parameter and returns all rows from it.
SET @table_name:='TestTable';
SET @sql:=CONCAT('SELECT * FROM ',@table_name);
PREPARE dynamic_statement FROM @sql;
EXECUTE dynamic_statement;
DEALLOCATE PREPARE dynamic_statement;
The variable @table_name is assigned name of the table. The variable @sql forms SELECT statement concatenating the Table name so the actual SELECT statement becomes SELECT * FROM TestTable. The PREPARE statement prepares the statement for execution and assigns a name (in this case it is dynamic_statement). EXECUTE command executes the statement prepared by the prepared name dynamic_statement and produces the result. The DEALLOCATE command releases the prepared statement.
So the result is
ID Col 1 A 1 B 1 C 2 A 2 B 2 C 2 D 2 E
Reference: Pinal Dave ( http://blog.sqlauthority.com )
推荐阅读
-
MySQL prepare语句的SQL语法
-
MySQL中预处理语句prepare、execute与deallocate的使用教程
-
MySQL – Dynamic SQL with EXECUTE and PREPARE Statements_MySQL
-
MySQL – Dynamic SQL with EXECUTE and PREPARE Statements_MySQL
-
MySQL prepare语句的SQL语法_MySQL
-
MySQL案例01:Last_SQL_Errno: 1755 Cannot execute the current event group in the par
-
MySQL prepare语句的SQL语法_MySQL
-
MySQL中prepare与execute以及deallocate预处理语句的使用教程
-
MySQL中prepare、execute与deallocate的用法详解
-
MySQL中prepare与execute以及deallocate预处理语句的使用教程