Postgresql 中文操作指南

36.3. Running SQL Commands #

可以在嵌入式 SQL 应用程序内运行任何 SQL 命令。以下是一些如何执行此操作的示例。

Any SQL command can be run from within an embedded SQL application. Below are some examples of how to do that.

36.3.1. Executing SQL Statements #

创建表：

Creating a table:

EXEC SQL CREATE TABLE foo (number integer, ascii char(16));
EXEC SQL CREATE UNIQUE INDEX num1 ON foo(number);
EXEC SQL COMMIT;

插入行：

Inserting rows:

EXEC SQL INSERT INTO foo (number, ascii) VALUES (9999, 'doodad');
EXEC SQL COMMIT;

删除行：

Deleting rows:

EXEC SQL DELETE FROM foo WHERE number = 9999;
EXEC SQL COMMIT;

更新：

Updates:

EXEC SQL UPDATE foo
    SET ascii = 'foobar'
    WHERE number = 9999;
EXEC SQL COMMIT;

返回单结果行的 _SELECT_语句也可以使用 _EXEC SQL_直接执行。若要处理具有多行的结果集，应用程序必须使用游标；请参见下文的 Section 36.3.2。（作为特殊情况，应用程序可以一次将多行获取到数组宿主变量中；请参见 Section 36.4.4.3.1。）

SELECT statements that return a single result row can also be executed using EXEC SQL directly. To handle result sets with multiple rows, an application has to use a cursor; see Section 36.3.2 below. (As a special case, an application can fetch multiple rows at once into an array host variable; see Section 36.4.4.3.1.)

单行选择：

Single-row select:

EXEC SQL SELECT foo INTO :FooBar FROM table1 WHERE ascii = 'doodad';

此外，可以使用 SHOW 命令检索配置参数：

Also, a configuration parameter can be retrieved with the SHOW command:

EXEC SQL SHOW search_path INTO :var;

:_something_ 形式的令牌是 host variables ，即它们引用 C 程序中的变量。它们在 Section 36.4 中进行了解释。

The tokens of the form :_something_ are host variables, that is, they refer to variables in the C program. They are explained in Section 36.4.

36.3.2. Using Cursors #

若要检索包含多行的结果集，应用程序必须声明一个游标并从游标中获取每一行。使用游标的步骤如下：声明一个游标，打开它，从游标中获取一行，重复，最后关闭它。

To retrieve a result set holding multiple rows, an application has to declare a cursor and fetch each row from the cursor. The steps to use a cursor are the following: declare a cursor, open it, fetch a row from the cursor, repeat, and finally close it.

使用游标进行选择：

Select using cursors:

EXEC SQL DECLARE foo_bar CURSOR FOR
    SELECT number, ascii FROM foo
    ORDER BY ascii;
EXEC SQL OPEN foo_bar;
EXEC SQL FETCH foo_bar INTO :FooBar, DooDad;
...
EXEC SQL CLOSE foo_bar;
EXEC SQL COMMIT;

有关声明游标的更多详细信息，请参阅 DECLARE ；有关从游标中获取行的更多详细信息，请参阅 FETCH 。

For more details about declaring a cursor, see DECLARE; for more details about fetching rows from a cursor, see FETCH.

Note

ECPG DECLARE 命令实际上并不导致向 PostgreSQL 后端发送语句。游标在后端（使用后端的 DECLARE 命令）在执行 OPEN 命令时打开。

The ECPG DECLARE command does not actually cause a statement to be sent to the PostgreSQL backend. The cursor is opened in the backend (using the backend’s DECLARE command) at the point when the OPEN command is executed.

36.3.3. Managing Transactions #

在默认模式下，只有当发出 EXEC SQL COMMIT 时才提交语句。嵌入式 SQL 界面也通过 -t 命令行选项 ecpg （请参阅 ecpg ）或 EXEC SQL SET AUTOCOMMIT TO ON 语句来支持自动提交事务（类似于 psql 的默认行为）。在自动提交模式下，每个命令自动提交，除非它在显式事务块内。可以通过 EXEC SQL SET AUTOCOMMIT TO OFF 明确关闭此模式。

In the default mode, statements are committed only when EXEC SQL COMMIT is issued. The embedded SQL interface also supports autocommit of transactions (similar to psql’s default behavior) via the -t command-line option to ecpg (see ecpg) or via the EXEC SQL SET AUTOCOMMIT TO ON statement. In autocommit mode, each command is automatically committed unless it is inside an explicit transaction block. This mode can be explicitly turned off using EXEC SQL SET AUTOCOMMIT TO OFF.

可以使用以下事务管理命令：

The following transaction management commands are available:

  • EXEC SQL COMMIT #

    • Commit an in-progress transaction.

  • EXEC SQL ROLLBACK #

    • Roll back an in-progress transaction.

  • EXEC SQL PREPARE TRANSACTION transaction_id #

    • Prepare the current transaction for two-phase commit.

  • EXEC SQL COMMIT PREPARED transaction_id #

    • Commit a transaction that is in prepared state.

  • EXEC SQL ROLLBACK PREPARED transaction_id #

    • Roll back a transaction that is in prepared state.

  • EXEC SQL SET AUTOCOMMIT TO ON #

    • Enable autocommit mode.

  • EXEC SQL SET AUTOCOMMIT TO OFF #

    • Disable autocommit mode. This is the default.

36.3.4. Prepared Statements #

当要传递给 SQL 语句的值在编译时未知，或者同一个语句将被多次使用时，准备好的语句很有用。

When the values to be passed to an SQL statement are not known at compile time, or the same statement is going to be used many times, then prepared statements can be useful.

使用命令 EXEC SQL SET AUTOCOMMIT TO OFF 准备语句。对于尚未知道的值，请使用占位符 “PREPARE”：

The statement is prepared using the command PREPARE. For the values that are not known yet, use the placeholder “?”:

EXEC SQL PREPARE stmt1 FROM "SELECT oid, datname FROM pg_database WHERE oid = ?";

如果一个语句返回单行，应用程序可以在 ? 之后调用 EXECUTE 以执行语句，通过 PREPARE 子句为占位符提供实际值：

If a statement returns a single row, the application can call EXECUTE after PREPARE to execute the statement, supplying the actual values for the placeholders with a USING clause:

EXEC SQL EXECUTE stmt1 INTO :dboid, :dbname USING 1;

如果一个语句返回多行，应用程序可以使用根据准备好的语句声明的游标。若要绑定输入参数，必须使用 USING 子句打开游标：

If a statement returns multiple rows, the application can use a cursor declared based on the prepared statement. To bind input parameters, the cursor must be opened with a USING clause:

EXEC SQL PREPARE stmt1 FROM "SELECT oid,datname FROM pg_database WHERE oid > ?";
EXEC SQL DECLARE foo_bar CURSOR FOR stmt1;

/* when end of result set reached, break out of while loop */
EXEC SQL WHENEVER NOT FOUND DO BREAK;

EXEC SQL OPEN foo_bar USING 100;
...
while (1)
{
    EXEC SQL FETCH NEXT FROM foo_bar INTO :dboid, :dbname;
    ...
}
EXEC SQL CLOSE foo_bar;

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

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

EXEC SQL DEALLOCATE PREPARE name;

有关 _PREPARE_的更多详细信息，请参见 PREPARE。有关使用占位符和输入参数的更多详细信息，还请参见 Section 36.5。

For more details about PREPARE, see PREPARE. Also see Section 36.5 for more details about using placeholders and input parameters.