MySQL 5.7 PREPARE、EXECUTE、DEALLOCATE语句介绍

布满荆棘的人生 2024-04-17 23:56 49阅读 0赞

**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 ;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2098186/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2098186/