执行SQL语句

嵌入式SQL-C程序支持直接执行静态SQL语句（如SELECT、INSERT），也支持执行动态SQL语句。动态SQL语句详情请参考执行动态SQL语句。本节主要介绍执行静态SQL语句。

静态SQL语句执行步骤如下：

1. 创建表结构：

   EXEC SQL CREATE TABLE foo (a int, b varchar);

2. 插入单行记录：

   EXEC SQL INSERT INTO foo VALUES (5, 'abc');

3. 删除指定记录：

   EXEC SQL DELETE FROM foo WHERE a = 5;

4. 更新表数据：

   EXEC SQL UPDATE foo SET b = 'gdp' WHERE a = 7;

5. 单行查询数据：

   EXEC SQL SELECT a INTO :var_a FROM foo WHERE b = 'def';

   通过INTO关键字将查询结果直接映射到绑定的宿主变量中。

完整使用示例如下：

前提条件：需要提前创建testdb数据库。

#include <stdlib.h>
#include <string.h>
#include <stdio.h>

int main ()
{
    ECPGdebug (1, stderr);

    EXEC SQL BEGIN DECLARE SECTION;
    int var_a;
    EXEC SQL END DECLARE SECTION;

    /* 提前创建testdb */
    EXEC SQL CONNECT TO testdb;

    /* 创建一个表 */
    EXEC SQL CREATE TABLE foo (a int, b varchar);

    /* 插入数据 */
    EXEC SQL INSERT INTO foo VALUES (5, 'abc');
    EXEC SQL INSERT INTO foo VALUES (6, 'def');
    EXEC SQL INSERT INTO foo VALUES (7, 'ghi');

    /* 删除一行 */
    EXEC SQL DELETE FROM foo WHERE a = 5;

    /* 更新表数据 */
    EXEC SQL UPDATE foo SET b = 'gdp' WHERE a = 7;

    /* 单行查询表数据 */
    EXEC SQL SELECT a INTO :var_a FROM foo WHERE b = 'def';

    /* 打印查询结果 */
    printf("select res is %d\n", var_a);
    EXEC SQL DISCONNECT;
    return 0;
}

运行结果如下（[39841]是线程ID，随机生成）：

[39841]: ECPGdebug: set to 1
[39841]: ECPGconnect: opening database testdb on <DEFAULT> port <DEFAULT>
[39841]: ecpg_execute on line 17: query: create table foo ( a int , b varchar ); with 0 parameter(s) on connection testdb
[39841]: ecpg_execute on line 17: using PQexec
[39841]: ecpg_execute on line 17: OK: CREATE TABLE
[39841]: ecpg_execute on line 20: query: insert into foo values ( 5 , 'abc' ); with 0 parameter(s) on connection testdb
[39841]: ecpg_execute on line 20: using PQexec
[39841]: ecpg_execute on line 20: OK: INSERT 0 1
[39841]: ecpg_execute on line 21: query: insert into foo values ( 6 , 'def' ); with 0 parameter(s) on connection testdb
[39841]: ecpg_execute on line 21: using PQexec
[39841]: ecpg_execute on line 21: OK: INSERT 0 1
[39841]: ecpg_execute on line 22: query: insert into foo values ( 7 , 'ghi' ); with 0 parameter(s) on connection testdb
[39841]: ecpg_execute on line 22: using PQexec
[39841]: ecpg_execute on line 22: OK: INSERT 0 1
[39841]: ecpg_execute on line 25: query: delete from foo where a = 5; with 0 parameter(s) on connection testdb
[39841]: ecpg_execute on line 25: using PQexec
[39841]: ecpg_execute on line 25: OK: DELETE 1
[39841]: ecpg_execute on line 28: query: update foo set b = 'gdp' where a = 7; with 0 parameter(s) on connection testdb
[39841]: ecpg_execute on line 28: using PQexec
[39841]: ecpg_execute on line 28: OK: UPDATE 1
[39841]: ecpg_execute on line 31: query: select a from foo where b = 'def'; with 0 parameter(s) on connection testdb
[39841]: ecpg_execute on line 31: using PQexec
[39841]: ecpg_execute on line 31: correctly got 1 tuples with 1 fields
[39841]: ecpg_get_data on line 31: RESULT: 6 offset: 4; array: no
select res is 6
[39841]: ecpg_finish: connection testdb closed