Mysql Programming CS 155P Note (vii) Dynamic SQL

Language method

PREPARE statement_name from Sql_text/* Definitions */

EXECUTE statement_name [USING variable [, variable ...]]/* Perform the expected syntax */

DEALLOCATE PREPARE statement_name/* Delete definition */

Cases

Mysql> PREPARE prod from "INSERT into Examlple VALUES (?,?)";

mysql> SET @p= ' 1 ';

mysql> SET @q= ' 2 ';

mysql> EXECUTE prod USING @p,@q;

mysql> SET @name = ' 3 ';

mysql> EXECUTE prod USING @p, @name;

Mysql> deallocate PREPARE prod; 1. Use the variable to make the table name: simply use set or declare to define the variable, and then directly as the SQL table name is not possible, MySQL will change the variable name as the table name. In other SQL repositories as well, the solution to MSSQL is to change the entire SQL sentence, in which the variable is used as the table name, and then the sp_executesql is used to adjust the sentence. This is not possible before the mysql5.0, 5.0 after the introduction of a new language, can reach the function of similar sp_executesql (only valid for procedure, function does not support dynamic Inquiry): PREPARE Stmt_name from preparable_stmt;

EXECUTE Stmt_name [USING @var_name [, @var_name] ...];

{deallocate | DROP} PREPARE Stmt_name; In order to have a perceptual knowledge,

Here are just a few small examples: 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;

++

| hypotenuse |

++

| 5 |

++

mysql> deallocate PREPARE stmt1; mysql> SET @s = ' SELECT SQRT (Pow (?, 2) + POW (?, 2)) as Hypotenuse ';

Mysql> PREPARE stmt2 from @s;

mysql> SET @a = 6;

mysql> SET @b = 8;

mysql> EXECUTE stmt2 USING @a, @b;

++

| hypotenuse |

++

| 10 |

++

mysql> deallocate PREPARE stmt2; If your MySQL version is 5.0.7 or higher, you can also use it in the LIMIT clause, as in the following example:

mysql> SET @a=1;mysql> PREPARE STMT from "select * from TBL LIMIT?";

mysql> EXECUTE STMT USING @a;

mysql> SET @skip = 1; SET @numrows = 5;

Mysql> PREPARE STMT from the "select * from TBL LIMIT?,?";

mysql> EXECUTE STMT USING @skip, @numrows; A few notes to use PREPARE:

A:prepare Stmt_name from Preparable_stmt, and defines a language, and gives it to Stmt_name, Tmt_name is the size of the word.

B: Even in the preparable_stmt sentence? Represents a string, and you don't need to. Included in the primer.

C: If the new PREPARE language uses an existing stmt_name, then the original will be released immediately! Even if this new PREPARE language is wrong, it cannot be done correctly.

The scope of the D:prepare Stmt_name is when the pre-client connection is available.

E: To release the resources for a predetermined sentence, you can use the deallocate PREPARE syntax.

F:execute stmt_name syntax, if Stmt_name does not exist, it will lead to a bug.

G: If the deallocate PREPARE syntax is not explicitly used when the terminal is connected, the server will release it on its own motion.

H: In the preset sentence, CREATE TABLE, DELETE, do, INSERT, REPLACE, SELECT, SET, UPDATE, and most of the SHOW syntax are supported.

I:prepare phrases can not be used for storage processes, self-defining functions! But starting with MySQL 5.0.13, it can be used for storage and still not supported in the function!

Mysql Programming CS 155P Note (vii) Dynamic SQL