Postgresql 中文操作指南

36.5. Dynamic SQL #

在很多情况下，应用程序在编写时必须执行的特定 SQL 语句是已知的。但是，在某些情况下，SQL 语句是在运行时编写的或由外部来源提供的。在这些情况下，你无法将 SQL 语句直接嵌入 C 源代码中，但有一种机制允许你调用你用字符串变量提供的任意 SQL 语句。

In many cases, the particular SQL statements that an application has to execute are known at the time the application is written. In some cases, however, the SQL statements are composed at run time or provided by an external source. In these cases you cannot embed the SQL statements directly into the C source code, but there is a facility that allows you to call arbitrary SQL statements that you provide in a string variable.

36.5.1. Executing Statements without a Result Set #

执行任意 SQL 语句的最简单方法是使用命令 EXECUTE IMMEDIATE。例如：

The simplest way to execute an arbitrary SQL statement is to use the command EXECUTE IMMEDIATE. For example:

EXEC SQL BEGIN DECLARE SECTION;
const char *stmt = "CREATE TABLE test1 (...);";
EXEC SQL END DECLARE SECTION;

EXEC SQL EXECUTE IMMEDIATE :stmt;

EXECUTE IMMEDIATE 可用于不会返回结果集（例如 DDL、INSERT、UPDATE、DELETE）的 SQL 语句。你不能这样执行检索数据（例如 SELECT）的语句。下一节将说明如何执行此操作。

EXECUTE IMMEDIATE can be used for SQL statements that do not return a result set (e.g., DDL, INSERT, UPDATE, DELETE). You cannot execute statements that retrieve data (e.g., SELECT) this way. The next section describes how to do that.

36.5.2. Executing a Statement with Input Parameters #

执行任意 SQL 语句的一种更强大的方法是准备它们一次，然后根据需要任意多次执行准备好的语句。还可以准备语句的泛化版本，然后通过替换参数来执行此语句的特定版本。在准备语句时，在稍后要替换参数的位置写上问号。例如：

A more powerful way to execute arbitrary SQL statements is to prepare them once and execute the prepared statement as often as you like. It is also possible to prepare a generalized version of a statement and then execute specific versions of it by substituting parameters. When preparing the statement, write question marks where you want to substitute parameters later. For example:

EXEC SQL BEGIN DECLARE SECTION;
const char *stmt = "INSERT INTO test1 VALUES(?, ?);";
EXEC SQL END DECLARE SECTION;

EXEC SQL PREPARE mystmt FROM :stmt;
 ...
EXEC SQL EXECUTE mystmt USING 42, 'foobar';

当您不再需要准备好的语句时，应该释放它：

When you don’t need the prepared statement anymore, you should deallocate it:

EXEC SQL DEALLOCATE PREPARE name;

36.5.3. Executing a Statement with a Result Set #

要执行具有单个结果行的 SQL 语句，可以使用 EXECUTE。要保存结果，请添加 INTO 子句。

To execute an SQL statement with a single result row, EXECUTE can be used. To save the result, add an INTO clause.

EXEC SQL BEGIN DECLARE SECTION;
const char *stmt = "SELECT a, b, c FROM test1 WHERE a > ?";
int v1, v2;
VARCHAR v3[50];
EXEC SQL END DECLARE SECTION;

EXEC SQL PREPARE mystmt FROM :stmt;
 ...
EXEC SQL EXECUTE mystmt INTO :v1, :v2, :v3 USING 37;

EXECUTE 命令可以有 INTO 子句、USING 子句、两者兼有，或者两者都没有。

An EXECUTE command can have an INTO clause, a USING clause, both, or neither.

如果预期某个查询返回多个结果行，则应使用游标，如下例所示。（有关游标的更多详细信息，请参阅 Section 36.3.2）。

If a query is expected to return more than one result row, a cursor should be used, as in the following example. (See Section 36.3.2 for more details about the cursor.)

EXEC SQL BEGIN DECLARE SECTION;
char dbaname[128];
char datname[128];
char *stmt = "SELECT u.usename as dbaname, d.datname "
             "  FROM pg_database d, pg_user u "
             "  WHERE d.datdba = u.usesysid";
EXEC SQL END DECLARE SECTION;

EXEC SQL CONNECT TO testdb AS con1 USER testuser;
EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT;

EXEC SQL PREPARE stmt1 FROM :stmt;

EXEC SQL DECLARE cursor1 CURSOR FOR stmt1;
EXEC SQL OPEN cursor1;

EXEC SQL WHENEVER NOT FOUND DO BREAK;

while (1)
{
    EXEC SQL FETCH cursor1 INTO :dbaname,:datname;
    printf("dbaname=%s, datname=%s\n", dbaname, datname);
}

EXEC SQL CLOSE cursor1;

EXEC SQL COMMIT;
EXEC SQL DISCONNECT ALL;