PREPARE

功能描述

创建一个预备语句。

预备语句是服务端的对象，可以用于优化性能。在执行PREPARE语句的时候，指定的查询被解析、分析、重写。当发出EXECUTE语句的时候，预备语句被规划和执行。这种设计避免了重复解析、分析工作。

PREPARE语句创建后在整个数据库会话期间一直存在，一旦创建成功，即便是在事务块中创建，事务回滚，PREPARE也不会删除。只能通过显式调用DEALLOCATE进行删除，会话结束时，PREPARE也会自动删除。

本文档只介绍PREPARE语句兼容MySQL的特性，原Vastbase的PREPARE语法未做删除和修改，详见PREPARE。

Vastbase在MySQL兼容模式下，支持PREPARE如下特性用法：

支持PREPARE FROM语法。
在存储过程中调用PREPARE语句时，支持通过设置b_format_behavior_compat_options参数控制EXECUTE statement在PL/pgSQL中的行为。参数取值包含b_format_plpgsql_execute时，表示EXECUTE statement中的statement为PREPARE处理过的语句，而不是将statement视作一个sqlstring模板执行动态sql。
可以重复使用预备语句statement覆盖原语句，输出结果与MySQL一致。
statement支持加单引号，且单引号内的statement必须是单个query。加单引号的场景，statement除了SELECT、INSERT、UPDATE、DELETE、MERGE INTO或VALUES语句外，还支持其他最终会转换成SelectStmt的语句，如部分SHOW系列语句等。
statement 中的绑定参数支持使用?，需要先将b_compatibility_mode设置为on，且不能同时在一个语句中同时使用$和?作为参数占位符。将b_compatibility_mode设置为on后，?将不能作为操作符使用。

注意事项

该功能仅在数据库兼容模式为MySQL时支持（即数据库实例初始化时指定DBCOMPATIBILITY='B'）。
该功能仅在数据库版本为V2.2 Build 15(Patch No.4)及以上版本支持。
如果创建预备语句的PREPARE语句声明了一些参数，那么传递给EXECUTE语句的必须是一个兼容的参数集，否则就会生成一个错误。
当会话已经执行过带EXECUTE的存储过程，已经产生了缓存，若此时添加或去除b_format_behavior_compat_options参数的b_format_plpgsql_execute取值，会出现报错信息，提示rebuild procedure or restart a new session。

语法格式

PREPARE name [ ( data_type [, ...] ) ] { AS | FROM } statement;
PREPARE name [ ( data_type [, ...] ) ] { AS | FROM } 'statement';

参数说明

name

指定预备语句的名称。它必须在该会话中是唯一的。
data_type

参数的数据类型。
statement

SELECT INSERT、UPDATE、DELETE、MERGE INTO或VALUES语句之一。

示例

示例1： 用prepare from语法为SQL语句命名，并用EXECUTE执行。

1、创建测试表并插入数据。

CREATE TABLE example(id  INTEGER NOT NULL,c2  character(16));
INSERT INTO example VALUES(generate_series(1,10),'test1');

2、用prepare from语法为SQL语句命名，并用EXECUTE执行。

（1）命名SELECT语句并执行。

PREPARE stmt1 FROM 'SELECT * FROM example LIMIT 3';
EXECUTE stmt1;

返回结果为：

id |        c2
----+------------------
1 | test1
2 | test1
3 | test1
(3 rows)

（2）命名INSERT语句，执行该语句并查看插入结果。

PREPARE stmt2 FROM 'INSERT INTO example(id) VALUES(102)';
EXECUTE stmt2;
select * from example where id=102;

返回结果为：

id  | c2
-----+----
102 |
(1 row)

（3）命名UPDATE语句，执行该语句并查看更新结果。

PREPARE stmt3 FROM  update example set c2='test01' where id=$1;
EXECUTE stmt3(102);
select * from example where id=102;

返回结果为：

id  |        c2
-----+------------------
102 | test01
(1 row)

（4）命名DELETE语句，执行该语句并查看结果。

PREPARE stmt4 FROM DELETE FROM example WHERE id=1;
EXECUTE stmt4;
SELECT * FROM example ORDER BY id;

返回结果为：

id  |        c2
-----+------------------
2 | test1
3 | test1
4 | test1
5 | test1
6 | test1
7 | test1
8 | test1
9 | test1
10 | test1
102 | test01
(10 rows)

3、在视图PG_PREPARED_STATEMENTS中查看名称为stmt3的语句。

SELECT * FROM pg_prepared_statements WHERE NAME='stmt3';

返回结果为：

 name  |                            statement                            |         prepare_time          | parameter_types | from_sql
-------+-----------------------------------------------------------------+-------------------------------+-----------------+----------
 stmt3 | PREPARE stmt3 FROM  update example set c2='test01' where id=$1; | 2023-08-22 10:15:14.913309+08 | {integer}       | t
(1 row)

4、清理环境。

DROP TABLE example;
DEALLOCATE stmt1;
DEALLOCATE stmt2;
DEALLOCATE stmt3;

示例2： 在prepare语句中使用单引号和?。

1、创建测试表并插入数据。

CREATE TABLE test(name text, age int);
INSERT INTO test values('a',18);

2、调用prepare语句执行查询，并用EXECUTE执行。

PREPARE stmt FROM SELECT * FROM test;
EXECUTE stmt;

返回结果为：

 name | age 
------+-----
 a    |  18
(1 row)

3、设置GUC参数b_compatibility_mode为on。

SET b_compatibility_mode to on;

4、在prepare语句中使用?。

PREPARE stmt1 FROM 'SELECT sqrt(pow(?,2) + pow(?,2)) as test';;
EXECUTE stmt1 USING 6,8;

返回结果为：

 test
------
   10
(1 row)

5、清理环境。

DROP TABLE test;
DEALLOCATE stmt;
DEALLOCATE stmt1;
SET b_compatibility_mode to off;

示例3： 在存储过程中使用PREPARE语句，且使用@变量带反引号。

1、参数准备。

SET enable_set_variable_b_format=on;
SET behavior_compat_options = 'block_return_multi_results';
SET b_format_behavior_compat_options='b_format_plpgsql_execute';

2、创建测试表，并插入数据。

CREATE TABLE `support` (
`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`type` VARCHAR(10),
`details` VARCHAR(30)
);

INSERT INTO `support` (`type`, `details`)
VALUES
('Email', 'admin@sqlfiddle.com'),
('Twitter', '@sqlfiddle');

3、创建存储过程。

CREATE OR REPLACE PROCEDURE `Function`(IN `table` VARCHAR(10), OUT `result` VARCHAR(10))
AS
BEGIN
SET @`q` := CONCAT('SELECT `der`.`type`
FROM (SELECT `type` FROM `', `table`, '` LIMIT 1) `der`,
(SELECT @`result` := NULL) `init`
INTO @`result`;');
PREPARE `stmt` FROM @`q`;
EXECUTE `stmt`;
SET `result` := @`result`;
DEALLOCATE PREPARE `stmt`;
END;
/

4、调用存储过程。

CALL `Function`('support', @`new_result`);

5、查询测试表数据。

SELECT @`new_result`;

返回结果为：

 @new_result
-------------
 Email
(1 row)

6、环境状态恢复。

DROP PROCEDURE `Function`;
DROP TABLE `support`;
SET enable_set_variable_b_format=off;
SET behavior_compat_options = 'reduce_tailing_zero';
SET b_format_behavior_compat_options='';

VastbaseG100

PREPARE

功能描述

注意事项

语法格式

参数说明

示例

弹框头部