36.3. Запуск команд SQL #
В приложении со встраиваемым SQL можно запустить любую команду SQL. Ниже приведены несколько примеров, показывающих как это делать.
36.3.1. Выполнение SQL-операторов #
Создание таблицы:
EXEC SQL CREATE TABLE foo (number integer, ascii char(16)); EXEC SQL CREATE UNIQUE INDEX num1 ON foo(number); EXEC SQL COMMIT;
Добавление строк:
EXEC SQL INSERT INTO foo (number, ascii) VALUES (9999, 'doodad'); EXEC SQL COMMIT;
Удаление строк:
EXEC SQL DELETE FROM foo WHERE number = 9999; EXEC SQL COMMIT;
Изменение:
EXEC SQL UPDATE foo
SET ascii = 'foobar'
WHERE number = 9999;
EXEC SQL COMMIT;Операторы SELECT, возвращающие одну строку результата, также могут выполняться непосредственно командой EXEC SQL. Чтобы обработать наборы результатов с несколькими строками, приложение должно использовать курсоры; см. Подраздел 36.3.2 ниже. (В отдельных случаях приложение может выбрать сразу несколько строк в переменную массива; см. Подраздел 36.4.4.3.1.)
Выборка одной строки:
EXEC SQL SELECT foo INTO :FooBar FROM table1 WHERE ascii = 'doodad';
Так же можно получить параметр конфигурации командой SHOW:
EXEC SQL SHOW search_path INTO :var;
Идентификаторы вида : воспринимаются как переменные среды, то есть они ссылаются на переменные программы C. Они рассматриваются в Разделе 36.4.имя
36.3.2. Использование курсоров #
Чтобы получить набор результатов, содержащий несколько строк, приложение должно объявить курсор и выбирать каждую строку через него. Использование курсора подразумевает следующие шаги: объявление курсора, открытие его, выборку строки через курсор, повторение предыдущего шага, и наконец, закрытие курсора.
Выборка с использованием курсоров:
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.
Примечание
Команда DECLARE в ECPG на самом деле не передаёт этот оператор серверу PostgreSQL. Курсор открывается на сервере (командой сервера DECLARE) в момент, когда выполняется команда OPEN.
36.3.3. Управление транзакциями #
В режиме по умолчанию операторы фиксируются только когда выполняется EXEC SQL COMMIT. Интерфейс встраиваемого SQL также поддерживает автофиксацию транзакций (так работает libpq по умолчанию); она включается аргументом командной строки -t программы ecpg (см. ecpg) либо оператором EXEC SQL SET AUTOCOMMIT TO ON. В режиме автофиксации каждая команда фиксируется автоматически, если только она не помещена в явный блок транзакции. Этот режим можно выключить явным образом, выполнив EXEC SQL SET AUTOCOMMIT TO OFF.
Поддерживаются следующие команды управления транзакциями:
EXEC SQL COMMIT#Зафиксировать текущую транзакцию.
EXEC SQL ROLLBACK#Откатить текущую транзакцию.
EXEC SQL PREPARE TRANSACTIONид_транзакции#Подготовить текущую транзакцию для двухфазной фиксации.
EXEC SQL COMMIT PREPAREDид_транзакции#Зафиксировать транзакцию в подготовленном состоянии.
EXEC SQL ROLLBACK PREPAREDид_транзакции#Откатить транзакцию в подготовленном состоянии.
EXEC SQL SET AUTOCOMMIT TO ON#Включить режим автофиксации.
EXEC SQL SET AUTOCOMMIT TO OFF#Отключить режим автофиксации. По умолчанию он отключён.
36.3.4. Подготовленные операторы #
Когда значения, передаваемые SQL-оператору, неизвестны во время компиляции, или один и тот же оператор будет использоваться многократно, могут быть полезны подготовленные операторы.
Оператор подготавливается командой PREPARE. Вместо значений, которые ещё неизвестны, вставляются местозаполнители «?»:
EXEC SQL PREPARE stmt1 FROM "SELECT oid, datname FROM pg_database WHERE oid = ?";
Если оператор возвращает одну строку, приложение может вызвать EXECUTE после PREPARE для выполнения этого оператора, указав фактические значения для местозаполнителей в предложении USING:
EXEC SQL EXECUTE stmt1 INTO :dboid, :dbname USING 1;
Если оператор возвращает несколько строк, приложение может использовать курсор, объявленный на базе подготовленного оператора. Чтобы привязать входные параметры, курсор нужно открыть с предложением USING:
EXEC SQL PREPARE stmt1 FROM "SELECT oid,datname FROM pg_database WHERE oid > ?";
EXEC SQL DECLARE foo_bar CURSOR FOR stmt1;
/* по достижении конца набора результатов прервать цикл while */
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;Когда подготовленный оператор больше не нужен, его следует освободить:
EXEC SQL DEALLOCATE PREPARE имя;Подробнее оператор PREPARE описан в PREPARE. Также обратитесь к Разделу 36.5 за дополнительными сведениями о местозаполнителях и входных параметрах.
36.3. Running SQL Commands #
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 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';
Also, a configuration parameter can be retrieved with the SHOW command:
EXEC SQL SHOW search_path INTO :var;
The tokens of the form : are host variables, that is, they refer to variables in the C program. They are explained in Section 36.4. something
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;
For more details about declaring a cursor, see DECLARE; for more details about fetching rows from a cursor, see FETCH.
Note
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 #
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 TRANSACTIONtransaction_id#Prepare the current transaction for two-phase commit.
EXEC SQL COMMIT PREPAREDtransaction_id#Commit a transaction that is in prepared state.
EXEC SQL ROLLBACK PREPAREDtransaction_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 #
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.
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 = ?";
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;
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;
For more details about PREPARE, see PREPARE. Also see Section 36.5 for more details about using placeholders and input parameters.