MySQL5.7中PREPARE、EXECUTE、DEALLOCATE语句怎么用
小编给大家分享一下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 ;
以上是“MySQL 5.7中PREPARE、EXECUTE、DEALLOCATE语句怎么用”这篇文章的所有内容,感谢各位的阅读!相信大家都有了一定的了解,希望分享的内容对大家有所帮助,如果还想学习更多知识,欢迎关注创新互联行业资讯频道!
新闻名称:MySQL5.7中PREPARE、EXECUTE、DEALLOCATE语句怎么用
链接地址:http://pwwzsj.com/article/jdsheg.html