Baoding-FenShuaJiang
/
Green_GoodERP18_FSJ_Standard
關注 1
收藏 0
Wiki 捐赠
gooderp18绿色标准版
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
分支: master
分支列表 標籤
${ item.name }
建立分支 ${ searchTerm }
從 'master'
${ noResults }
Green_GoodERP18_FSJ_Standard/PostgreSQL/doc/postgresql/html/ecpg-commands.html

165 line
11KB
原始文件 永久連結 Blame 歷史記錄 

  1. <?xml version="1.0" encoding="UTF-8" standalone="no"?>

  2. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>35.3. Running SQL Commands</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets V1.79.1" /><link rel="prev" href="ecpg-connect.html" title="35.2. Managing Database Connections" /><link rel="next" href="ecpg-variables.html" title="35.4. Using Host Variables" /></head><body><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">35.3. Running SQL Commands</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="ecpg-connect.html" title="35.2. Managing Database Connections">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="ecpg.html" title="Chapter 35. ECPG - Embedded SQL in C">Up</a></td><th width="60%" align="center">Chapter 35. <span xmlns="http://www.w3.org/1999/xhtml" class="application">ECPG</span> - Embedded <acronym xmlns="http://www.w3.org/1999/xhtml" class="acronym">SQL</acronym> in C</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 12.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="ecpg-variables.html" title="35.4. Using Host Variables">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="ECPG-COMMANDS"><div class="titlepage"><div><div><h2 class="title" style="clear: both">35.3. Running SQL Commands</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="ecpg-commands.html#ECPG-EXECUTING">35.3.1. Executing SQL Statements</a></span></dt><dt><span class="sect2"><a href="ecpg-commands.html#ECPG-CURSORS">35.3.2. Using Cursors</a></span></dt><dt><span class="sect2"><a href="ecpg-commands.html#ECPG-TRANSACTIONS">35.3.3. Managing Transactions</a></span></dt><dt><span class="sect2"><a href="ecpg-commands.html#ECPG-PREPARED">35.3.4. Prepared Statements</a></span></dt></dl></div><p>

  3.    Any SQL command can be run from within an embedded SQL application.

  4.    Below are some examples of how to do that.

  5.   </p><div class="sect2" id="ECPG-EXECUTING"><div class="titlepage"><div><div><h3 class="title">35.3.1. Executing SQL Statements</h3></div></div></div><p>

  6.    Creating a table:

  7. </p><pre class="programlisting">

  8. EXEC SQL CREATE TABLE foo (number integer, ascii char(16));

  9. EXEC SQL CREATE UNIQUE INDEX num1 ON foo(number);

  10. EXEC SQL COMMIT;

  11. </pre><p>

  12.   </p><p>

  13.    Inserting rows:

  14. </p><pre class="programlisting">

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

  16. EXEC SQL COMMIT;

  17. </pre><p>

  18.   </p><p>

  19.    Deleting rows:

  20. </p><pre class="programlisting">

  21. EXEC SQL DELETE FROM foo WHERE number = 9999;

  22. EXEC SQL COMMIT;

  23. </pre><p>

  24.   </p><p>

  25.    Updates:

  26. </p><pre class="programlisting">

  27. EXEC SQL UPDATE foo

  28.     SET ascii = 'foobar'

  29.     WHERE number = 9999;

  30. EXEC SQL COMMIT;

  31. </pre><p>

  32.   </p><p>

  33.    <code class="literal">SELECT</code> statements that return a single result

  34.    row can also be executed using

  35.    <code class="literal">EXEC SQL</code> directly.  To handle result sets with

  36.    multiple rows, an application has to use a cursor;

  37.    see <a class="xref" href="ecpg-commands.html#ECPG-CURSORS" title="35.3.2. Using Cursors">Section 35.3.2</a> below.  (As a special case, an

  38.    application can fetch multiple rows at once into an array host

  39.    variable; see <a class="xref" href="ecpg-variables.html#ECPG-VARIABLES-ARRAYS" title="35.4.4.3.1. Arrays">Section 35.4.4.3.1</a>.)

  40.   </p><p>

  41.    Single-row select:

  42. </p><pre class="programlisting">

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

  44. </pre><p>

  45.   </p><p>

  46.    Also, a configuration parameter can be retrieved with the

  47.    <code class="literal">SHOW</code> command:

  48. </p><pre class="programlisting">

  49. EXEC SQL SHOW search_path INTO :var;

  50. </pre><p>

  51.   </p><p>

  52.    The tokens of the form

  53.    <code class="literal">:<em class="replaceable"><code>something</code></em></code> are

  54.    <em class="firstterm">host variables</em>, that is, they refer to

  55.    variables in the C program.  They are explained in <a class="xref" href="ecpg-variables.html" title="35.4. Using Host Variables">Section 35.4</a>.

  56.   </p></div><div class="sect2" id="ECPG-CURSORS"><div class="titlepage"><div><div><h3 class="title">35.3.2. Using Cursors</h3></div></div></div><p>

  57.    To retrieve a result set holding multiple rows, an application has

  58.    to declare a cursor and fetch each row from the cursor.  The steps

  59.    to use a cursor are the following: declare a cursor, open it, fetch

  60.    a row from the cursor, repeat, and finally close it.

  61.   </p><p>

  62.    Select using cursors:

  63. </p><pre class="programlisting">

  64. EXEC SQL DECLARE foo_bar CURSOR FOR

  65.     SELECT number, ascii FROM foo

  66.     ORDER BY ascii;

  67. EXEC SQL OPEN foo_bar;

  68. EXEC SQL FETCH foo_bar INTO :FooBar, DooDad;

  69. ...

  70. EXEC SQL CLOSE foo_bar;

  71. EXEC SQL COMMIT;

  72. </pre><p>

  73.   </p><p>

  74.    For more details about declaration of the cursor,

  75.    see <a class="xref" href="ecpg-sql-declare.html" title="DECLARE">DECLARE</a>, and

  76.    see <a class="xref" href="sql-fetch.html" title="FETCH"><span class="refentrytitle">FETCH</span></a> for <code class="literal">FETCH</code> command

  77.    details.

  78.   </p><div class="note"><h3 class="title">Note</h3><p>

  79.      The ECPG <code class="command">DECLARE</code> command does not actually

  80.      cause a statement to be sent to the PostgreSQL backend.  The

  81.      cursor is opened in the backend (using the

  82.      backend's <code class="command">DECLARE</code> command) at the point when

  83.      the <code class="command">OPEN</code> command is executed.

  84.     </p></div></div><div class="sect2" id="ECPG-TRANSACTIONS"><div class="titlepage"><div><div><h3 class="title">35.3.3. Managing Transactions</h3></div></div></div><p>

  85.    In the default mode, statements are committed only when

  86.    <code class="command">EXEC SQL COMMIT</code> is issued. The embedded SQL

  87.    interface also supports autocommit of transactions (similar to

  88.    <span class="application">psql</span>'s default behavior) via the <code class="option">-t</code>

  89.    command-line option to <code class="command">ecpg</code> (see <a class="xref" href="app-ecpg.html" title="ecpg"><span class="refentrytitle"><span class="application">ecpg</span></span></a>) or via the <code class="literal">EXEC SQL SET AUTOCOMMIT TO

  90.    ON</code> statement. In autocommit mode, each command is

  91.    automatically committed unless it is inside an explicit transaction

  92.    block. This mode can be explicitly turned off using <code class="literal">EXEC

  93.    SQL SET AUTOCOMMIT TO OFF</code>.

  94.   </p><p>

  95.     The following transaction management commands are available:

  96. 

  97.     </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">EXEC SQL COMMIT</code></span></dt><dd><p>

  98.         Commit an in-progress transaction.

  99.        </p></dd><dt><span class="term"><code class="literal">EXEC SQL ROLLBACK</code></span></dt><dd><p>

  100.         Roll back an in-progress transaction.

  101.        </p></dd><dt><span class="term"><code class="literal">EXEC SQL PREPARE TRANSACTION </code><em class="replaceable"><code>transaction_id</code></em></span></dt><dd><p>

  102.         Prepare the current transaction for two-phase commit.

  103.        </p></dd><dt><span class="term"><code class="literal">EXEC SQL COMMIT PREPARED </code><em class="replaceable"><code>transaction_id</code></em></span></dt><dd><p>

  104.         Commit a transaction that is in prepared state.

  105.        </p></dd><dt><span class="term"><code class="literal">EXEC SQL ROLLBACK PREPARED </code><em class="replaceable"><code>transaction_id</code></em></span></dt><dd><p>

  106.         Roll back a transaction that is in prepared state.

  107.        </p></dd><dt><span class="term"><code class="literal">EXEC SQL SET AUTOCOMMIT TO ON</code></span></dt><dd><p>

  108.         Enable autocommit mode.

  109.        </p></dd><dt><span class="term"><code class="literal">EXEC SQL SET AUTOCOMMIT TO OFF</code></span></dt><dd><p>

  110.         Disable autocommit mode.  This is the default.

  111.        </p></dd></dl></div><p>

  112.    </p></div><div class="sect2" id="ECPG-PREPARED"><div class="titlepage"><div><div><h3 class="title">35.3.4. Prepared Statements</h3></div></div></div><p>

  113.     When the values to be passed to an SQL statement are not known at

  114.     compile time, or the same statement is going to be used many

  115.     times, then prepared statements can be useful.

  116.    </p><p>

  117.     The statement is prepared using the

  118.     command <code class="literal">PREPARE</code>.  For the values that are not

  119.     known yet, use the

  120.     placeholder <span class="quote">“<span class="quote"><code class="literal">?</code></span>”</span>:

  121. </p><pre class="programlisting">

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

  123. </pre><p>

  124.    </p><p>

  125.     If a statement returns a single row, the application can

  126.     call <code class="literal">EXECUTE</code> after

  127.     <code class="literal">PREPARE</code> to execute the statement, supplying the

  128.     actual values for the placeholders with a <code class="literal">USING</code>

  129.     clause:

  130. </p><pre class="programlisting">

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

  132. </pre><p>

  133.    </p><p>

  134.     If a statement returns multiple rows, the application can use a

  135.     cursor declared based on the prepared statement.  To bind input

  136.     parameters, the cursor must be opened with

  137.     a <code class="literal">USING</code> clause:

  138. </p><pre class="programlisting">

  139. EXEC SQL PREPARE stmt1 FROM "SELECT oid,datname FROM pg_database WHERE oid &gt; ?";

  140. EXEC SQL DECLARE foo_bar CURSOR FOR stmt1;

  141. 

  142. /* when end of result set reached, break out of while loop */

  143. EXEC SQL WHENEVER NOT FOUND DO BREAK;

  144. 

  145. EXEC SQL OPEN foo_bar USING 100;

  146. ...

  147. while (1)

  148. {

  149.     EXEC SQL FETCH NEXT FROM foo_bar INTO :dboid, :dbname;

  150.     ...

  151. }

  152. EXEC SQL CLOSE foo_bar;

  153. </pre><p>

  154.    </p><p>

  155.     When you don't need the prepared statement anymore, you should

  156.     deallocate it:

  157. </p><pre class="programlisting">

  158. EXEC SQL DEALLOCATE PREPARE <em class="replaceable"><code>name</code></em>;

  159. </pre><p>

  160.    </p><p>

  161.     For more details about <code class="literal">PREPARE</code>,

  162.     see <a class="xref" href="ecpg-sql-prepare.html" title="PREPARE">PREPARE</a>. Also

  163.     see <a class="xref" href="ecpg-dynamic.html" title="35.5. Dynamic SQL">Section 35.5</a> for more details about using

  164.     placeholders and input parameters.

  165.    </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="ecpg-connect.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="ecpg.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="ecpg-variables.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">35.2. Managing Database Connections </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> 35.4. Using Host Variables</td></tr></table></div></body></html>
上海开阖软件有限公司 沪ICP备12045867号-1