mysql5.7语句建库_MySQL 5.7 PREPARE、EXECUTE、DEALLOCATE语句介绍

PREPARE语句准备好一条SQL语句，并分配给这条SQL语句一个名字供之后调用。准备好的SQL语句通过EXECUTE命令执行，通过DEALLOCATE PREPARE命令释放掉。

语句的名字不区分大小写。准备好的SQL语句名字可以是字符串，也可以是用户指定的包含SQL文本的变量。PREPARE中的SQL文本必须代表一条单独的SQL语句而不能是多条SQL语句。在SQL语句中，? 字符用来作为后面执行查询使用的一个参数。? 不能加上引号，及时打算将它们绑定到字符变量中也不可以。

如果准备好的SQL语句名字已经存在，它会在新语句被准备好前释放掉。这意味着，如果一条新的语句包含错误且不能被准备好，这时会返回错误并且准备好的SQL语句将不再存在。

准备好的语句范围是创建它的会话，具有下列特点：

准备好的语句在其他会话无效；

当会话结束时，不管会话时正常结束还是异常结束，这个会话中准备好的SQL语句将不再存在。如果自动连接功能开启，客户端不会被通知连接丢失。

在存储过程或函数里面的准备好的语句，在存储过程或函数执行结束后，会继续存在，可以在存储过程或包外面继续被执行。

示例：

mysql> SET @a=10;

Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE STMT FROM 'SELECT * FROM dept2 LIMIT ?';

Query OK, 0 rows affected (0.08 sec)

Statement prepared

mysql> EXECUTE STMT USING @a;

+--------+-------+

| deptno | dname |

+--------+-------+

|     10 | A     |

|     20 | B     |

|     30 | C     |

|     40 | D     |

|     50 | E     |

|     60 | F     |

|     70 | G     |

|     80 | H     |

|     90 | I     |

|    100 | J     |

+--------+-------+

10 rows in set (0.02 sec)

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

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE STMT FROM 'SELECT * FROM dept2 LIMIT ?, ?';

Query OK, 0 rows affected (0.00 sec)

Statement prepared

mysql> EXECUTE STMT USING @skip, @numrows;

+--------+-------+

| deptno | dname |

+--------+-------+

|     20 | B     |

|     30 | C     |

|     40 | D     |

|     50 | E     |

|     60 | F     |

+--------+-------+

5 rows in set (0.00 sec)

mysql> DEALLOCATE PREPARE STMT;

Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE STMT USING @skip, @numrows;

ERROR 1243 (HY000): Unknown prepared statement handler (STMT) given to EXECUTE

mysql> PREPARE STMT FROM 'SELECT * FROM dept2 LIMIT ?, ?';

Query OK, 0 rows affected (0.00 sec)

Statement prepared

mysql> EXECUTE STMT USING @skip, @numrows;

+--------+-------+

| deptno | dname |

+--------+-------+

|     20 | B     |

|     30 | C     |

|     40 | D     |

|     50 | E     |

|     60 | F     |

+--------+-------+

5 rows in set (0.00 sec)

示例存储过程

delimiter $$

create procedure registerSQLReference(IN the_table_name VARCHAR(80), IN the_sql_script_version VARCHAR(80), IN the_sql_script_name VARCHAR(80),

IN the_install_version  VARCHAR(80), IN the_execution_duration VARCHAR(80), IN the_sql_script_description VARCHAR(200))

BEGIN

declare column_exist INT DEFAULT 0;

declare the_query VARCHAR(500);

select '[INFO]    Check if the column C_CHANGE_DESCRIPTION exists in the table @VERSION_LEVEL_TABLE_NAME';

set column_exist = is_ChangeDescColumnExist ('@VERSION_LEVEL_TABLE_NAME');

IF column_exist = 0

THEN

select '[INFO]    VERSION LEVEL TABLE does not contain C_CHANGE_DESCRIPTION column.';

set @v_the_table_name=the_table_name;

select concat('INSERT INTO ', @v_the_table_name, ' VALUES (?, ?, ?, ?, ?) ') into the_query;

SET @stmt=the_query;

PREPARE STMT FROM @stmt;

select concat('[INFO]    the_query=', the_query );

set @v_the_sql_script_version=the_sql_script_version;

set @v_the_sql_script_name=the_sql_script_name;

set @v_date=now();

set @v_the_install_version=the_install_version;

set @v_the_execution_duration=the_execution_duration;

EXECUTE STMT using @v_the_sql_script_version , @v_the_sql_script_name, @v_date, @v_the_install_version, @v_the_execution_duration;

ELSE

select '[INFO]    VERSION LEVEL TABLE contains C_CHANGE_DESCRIPTION column.';

select concat('INSERT INTO ', the_table_name, ' VALUES (?, ?, ?, ?, ?, ?) ') into the_query;

SET @stmt=the_query;

PREPARE STMT FROM @stmt;

select concat('[INFO]    the_query=', the_query );

set @v_the_sql_script_version=the_sql_script_version;

set @v_the_sql_script_name=the_sql_script_name;

set @v_date=now();

set @v_the_install_version=the_install_version;

set @v_the_execution_duration=the_execution_duration;

set @v_the_sql_script_description=the_sql_script_description;

select concat('[INFO]    the_query=', the_query);

EXECUTE the_query using @v_the_sql_script_version , @v_the_sql_script_name, @v_date, @v_the_install_version, @v_the_execution_duration, @v_the_sql_script_description;

END IF;

DEALLOCATE PREPARE STMT;

END$$

delimiter ;