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 ;
語句的名字不區分大小寫。準備好的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 ;
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26506993/viewspace-2098186/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- MySQL 預處理語句prepare、execute、deallocate的使用MySql
- MySQL 5.7 LIMIT語句介紹MySqlMIT
- MySQL 5.7 的事務控制語句的介紹MySql
- MySQL 遊標(PREPARE預處理語句)MySql
- MySQL 5.7 NOT EXISTS用法介紹MySql
- MySQL 5.7 Performance Schema 介紹MySqlORM
- MySQL 5.7 REPLACE語句的用法MySql
- MySQL 5.7 mysqldumpslow工具介紹MySql
- MySQL 5.5 SHOW PROFILE、SHOW PROFILES語句介紹MySql
- MySQL 5.7 EXPLAIN EXTENDED語句說明MySqlAI
- MySQL 5.5 LOCK TABLES 和 UNLOCK TABLES 語句介紹MySql
- MySQL 5.5 FLUSH TABLES WITH READ LOCK語句介紹MySql
- MySQL 5.7許可權的介紹MySql
- mongodb常用語句介紹MongoDB
- 精妙SQL語句介紹SQL
- MySQL 5.7 mysql_install_db工具介紹MySql
- MySQL 5.7 MyISAM併發插入特性介紹MySql
- MySQL 5.7 online DDL特性介紹MySql
- MySQL5.7 JSON型別使用介紹MySqlJSON型別
- MySQL 5.7預設ONLY_FULL_GROUP_BY語義介紹以及故障解決MySql
- JavaScript return語句簡單介紹JavaScript
- javascript with()語句用法簡單介紹JavaScript
- mysql 常用sql語句 簡介MySql
- MySQL 5.7 SELECT ... LOCK IN SHARE MODE|FOR UPDATE語句說明MySql
- SQL高手篇:精妙SQL語句介紹SQL
- Oracle動態執行語句(Execute Immediate)Oracle
- MySQL 5.7 使用 SELECT ... INTO 語句匯出資料檔案MySql
- 超詳細的介紹Python語句Python
- go 流程控制之switch 語句介紹Go
- javascript流程控制語句簡單介紹JavaScript
- MySQL SQL語句查詢執行過程的四個階段介紹MySql
- js如何退出迴圈語句簡單介紹JS
- MySQL 5.7 InnoDB引擎簡介MySql
- mysql語句MySql
- Oracle EXECUTE IMMEDIATE語句裡面的引號處理Oracle
- awk 流程控制語句(if,for,while,do)詳細介紹While
- MySQL 5.7的主要特性簡介MySql
- MySQL5.7新版本的運維,效能和新特性介紹MySql運維