Pro*C介绍-内嵌SQL

概要

内嵌SQL是结合高级语言如C/C++的计算能力和SQL数据库处理能力的一种方法。它允许你在程序中执行任意的SQL语句。Oracle的嵌入SQL环境称为Pro*C。

Pro*C语法

SQL

所有SQL语句都要以 EXEC SQL 开始，并用分号" ; "结束。SQL语句可以放置在C/C++块中的任何地方，但可执行的(SQL)语句应该在声明语句后面。例：

      {

    int a;

    /* ... */

    EXEC SQL SELECT salary INTO :a

             FROM Employee

             WHERE SSN=876543210;

    /* ... */

    printf("The salary is %d\n", a);

    /* ... */

}
    

预处理指令

能够在Pro*C中正常工作的C/C++预处理指令是 #include 和 #if 。Pro*C不能识别 #define 。下面的代码是错误的：

      #define THE_SSN 876543210

/* ... */

EXEC SQL SELECT salary INTO :a

         FROM Employee

         WHERE SSN = THE_SSN;/* INVALID */
    

语句标号

可以在SQL中跳转到C/C++标记

          EXEC SQL WHENEVER SQLERROR GOTO error_in_SQL;

    /* ... */

error_in_SQL:

    /* do error handling */
    

宿主变量

基础

指针

可以在SQL表达式中使用普通C语法声明的指针。通常使用一个冒号做前缀：

      int*x;

/*...*/

EXEC SQL SELECT xyz INTO :x FROM ...;
    

这个 SELECT 语句的结果会写入 *x ，而不是 x 。

结构

结构同样可以作为宿主变量使用，如下面例子：

      typedef struct {

    char name[21];/* one greater than column length; for '\0' */

    int SSN;

} Emp;

/* ... */

Emp bigshot;

/* ... */

EXEC SQL INSERT INTO emp (ename, eSSN)

    VALUES (:bigshot);
    

数组

像下面这样使用宿主数组：

      int emp_number[50];

char name[50][11];

/* ... */

EXEC SQL INSERT INTO emp(emp_number, name)

    VALUES (:emp_number, :emp_name);
    

这样会一次插入所有的50个元素(tuples)。

数组只能是一维数组。例子中的 char name[50][11] 可能看起来与这个规则矛盾，然而，Pro*C实际上把 name 视为一维字符串数组而不是二维字符数组。也可以使用结构数组。

当使用数组存放查询结果时，如果宿主数组的大小( n )小于实际查询后返回的结果数量时，那么只有（查询出来的）前 n 个结果被填入宿主数组。

指示器变量

指示器实际上是附在宿主变量后的" NULL 标记"。每一个宿主变量都可以选择性的关联到一个指示器变量上。指示器变量的类型必须为2字节整形值( short 类型)，而且必须紧随在宿主变量后且用冒号做为前缀。你也可以使用关键字 INDICATOR 放在宿主变量和指示器变量之间。例如：

      short indicator_var;

EXEC SQL SELECT xyz INTO :host_var:indicator_var

    FROM ...;

/* ... */

EXEC SQL INSERT INTO R

    VALUES(:host_var INDICATOR :indicator_var, ...);
    

在 SELECT 的 INTO 子句中使用的指示器变量可以用来检查返回给宿主变量的值为空(NULL)或被截断的的情况。Oracle能够赋给指示器变量的值具有下面各项意义：

你也可以在 INSERT 或 UPDATE 的 VALUES 和 SET 子句中使用指示器变量指明用于输入的宿主变量为 NULL 值。你的程序可以赋给指示器变量的值具有如下各项意义：

数据类型同等化

Oracle认识两种数据类型：内部类型和外部类型。内部数据类型指示Oracle在数据库表中如何存储字段。外部数据类型指示如何格式化存储在宿主变量中用于输入或输出的值。在预编译期，每一个宿主变量会被赋予一个默认的Oracle外部数据类型。数据类型同等化允许你重载默认的同等化，并允许你控制Oracle输入数据的解释和输出数据的格式化。

同等化通过使用 VAR 表达式实现基于从变量到变量的转换。语法是：

      EXEC SQL VAR <host_var> IS <type_name> [ (<length>) ];
    

举例来说, 假设你想从 emp 表中查询雇员名子，然后传给一个需要C类型（以' \0 '结尾的）字符串的程序，你不需要显式的用' \0 '来结束这个名子。如下面所示，简单地将宿主变量同等化为外部数据类型 STRING ：

      char emp_name[21];

EXEC SQL VAR emp_name IS STRING(21);
    

emp 表中 ename 字段的长是20字符(character)，因此，需要分配21字符(character)以适应' \0 '结束符。Oracle的外部数据类型 STRING 是为C类型字符串特别设计的接口。当把 ename 字段值传给 emp_name 时，Oracle会自动用' \n '结尾。

也可以使用 TYPE 语句将用户自定义数据类型同等化为Oracle外部数据类型。语法是：

      EXEC SQL TYPE <user_type> IS <type_name> [ (<length>) ] [REFERENCE];
    

可以声明一个用户自定义类型的指针，显式的如指向标量或结构的指针，或隐式的如数组，然后在 TYPE 表达式中使用这个类型。这种情况下，你需要在表达式后使用 REFERENCE 子句，如下所示：

      typedef unsigned char *my_raw;

EXEC SQL TYPE my_raw IS VARRAW(4000) REFERENCE;

my_raw buffer;

/* ... */

buffer = malloc(4004);
    

这里，我们分配了比源类型长(4000)更多的内存，这是因为预编译器要返回长度，而且可能需要填充适当的长度以满足系统的对齐要求。

动态SQL

嵌入SQL能够满足一个固定的应用，但有时动态产生完整的SQL语句也是很重要的。对于动态SQL，语句存储在字符串变量中， PREPARE 把字符串转换为SQL语句，然后用 EXECUTE 执行这个语句。考虑下面的例子：

      char *s = "INSERT INTO emp VALUES(1234, 'jon', 3)";

EXEC SQL PREPARE q FROM :s;

EXEC SQL EXECUTE q;
    

PREPARE 和 EXECUTE 可放到一个语句中，像这样：

      char *s = "INSERT INTO emp VALUES(1234, 'jon', 3)";

EXEC SQL EXECUTE IMMEDIATE :s;
    

事务

Oracle Pro*C支持标准SQL定义的事务。一个事务是一组SQL语句集合，Oracle把它当作单独的单元运行。一个事务从第一个SQL语句开始，遇到" EXEC SQL COMMIT "（执行当前事务对数据库的永久修改）或" EXEC SQL ROLLBACK "（取消从事务开始到当前位置对数据库的任何修改）时结束事务。当前事务由 COMMIT 或 ROLLBACK 语句结束后，下一条可执行SQL语句将自动开始一个新事务。

如果程序结束时没有执行 EXEC SQL COMMIT ，则对数据库的所作的修改都将被忽略。

错误处理

在每个可执行的SQL表达式之后，可以在程序中检查 SQLCA 显式地或用 WHENEVER 语句隐式地得到执行状态信息。下面将详细地介绍这两种方法。

SQLCA

SQLCA(SQL Communications Area，SQL通讯区)用于在程序中检查错误和状态变化。每个可执行SQL语句执行后，Oracle 运行时会在这个结构中填入信息。

如果要使用SQLCA，你要用 #include 包含进 sqlca.h 头文件。如果在很多地方包含了头文件，你要使用 #undef SQLCA 来取消 SQLCA 的宏定义。 sqlca.h 中的相关程序块如下：

      #ifndef SQLCA

#define SQLCA 1



struct sqlca {

    /* ub1 */ char sqlcaid[8];

    /* b4 */ long sqlabc;

    /* b4 */ long sqlcode;

    struct {

        /* ub2 */ unsigned short sqlerrml;

        /* ub1 */ char sqlerrmc[70];

    } sqlerrm;

    /* ub1 */ char sqlerrp[8];

    /* b4 */ long sqlerrd[6];

    /* ub1 */ char sqlwarn[8];

    /* ub1 */ char sqlext[8];

};

/* ... */
    

sqlca域有下列的意义:

SQLCA只能在 sqlerrm 域中存储最大70字符长的错误信息。要得到更长的（或嵌套的）全部错误信息字符，可以使用 sqlglm() 函数：

      void sqlglm(char *msg_buf, size_t *buf_size, size_t *msg_length);
    

msg_buf 是Oracle存储错误信息的字符缓冲； buf_size 指定 msg_buf 的长(byte)； Oracle在 *msg_length 中存放实际的错误信息长。Oracle错误信息的最大长度是512字节(byte)。

WHENEVER语句

这个表达式进行自动错误检查和处理。语法是：

      EXEC SQL WHENEVER <condition> <action>;
    

Oracle自动检查 <condition> 的SQLCA，当条件被检测到时，程序会自动执行 <action>

<condition> 可以是下列各项：

• SQLWARNING - 由于Oracle返回一个警告而设置 sqlwarn[0]
• SQLERROR - 由于Oracle返回一个错误, sqlcode 的值为负
• NOT FOUND - 由于Oracle按 WHERE 的条件没有找到任何一条记录，或 SELECT INTO 或 FETCH 返回 0 条记录，而使 sqlcode 为正

<action> 可以为下列各项：

• CONTINUE - 只要可能，程序会尝试继续运行之后的语句
• DO - 程序将控制权交给一个错误处理模块
• GOTO <label> - 程序跳转到被标示的语句
• STOP - 调用 exit() 结束程序，回滚未提交的操作

下面是 WHENEVER 语句的例子：

      EXEC SQL WHENEVER SQLWARNING DO print_warning_msg();

EXEC SQL WHENEVER NOT FOUND GOTO handle_empty;
    

下面是一个更详细的例子:

      /* code to find student name given id */

/* ... */

for (;;)

{

    printf("Give student id number : ");

    scanf("%d", &id);

    EXEC SQL WHENEVER NOT FOUND GOTO notfound;

    EXEC SQL SELECT studentname INTO :st_name

             FROM   student

             WHERE  studentid = :id;

    printf("Name of student is %s.\n", st_name);

    continue;

notfound:

    printf("No record exists for id %d!\n", id);

}

/* ... */
    

注意 WHENEVER 表达式不遵从标准C的作用域规则，整个程序都是它的作用域。举例来说，如果下面的语句在你程序中的什么地方（如在一个循环之前）：

      EXEC SQL WHENEVER NOT FOUND DO break; 
    

这个文件在这行之后出现的所有SQL语句都会受其影响。当不再需要 WHENEVER 的来影响程序时（比如在你的循环之后），确保使用下面的语句取消它的使用。

      EXEC SQL WHENEVER NOT FOUND CONTINUE; 
    

Demo程序

注意: 例程会创建、使用四个表： DEPT 、 EMP 、 PAY1 和 PAY2 。注意你的数据库中可能会存在相同名子的表！

在leland系统的 /afs/ir/class/cs145/code/proc 下有很多例程。它们被命名为 sample*.pc （C用户）和 cppdemo*.pc （C++用户）。 " .pc "是Pro*C代码的扩展名。由于有几个固定的步骤复制这些文件，所以不要手工复制它们。下面介绍如何下载和设置例程：

1. 确认你已经运行了 source /afs/ir/class/cs145/all.env
2. 在你的目录下，运行 load_samples <db_username> <db_passwd> <sample_dir> ， <sample_idr> 是你想放置例程的地方（例： load_samples sally etaoinshrdlu cs145_samples ）
3. cd <sample_dir>
4. 运行 make samples (C++用户运行 make cppsamples )编译所有的例程。

第2步将会建立样品数据库，创建在 <sample_dir> 指定的新目录，然后把文件复制到目录下。它会在例程中修改你的用户名和密码，所有你不必每次运行例程时都要输入用户名和密码。 sample1 和 cppdemo1 也为用户提供了输入用户名和密码的接口，当你想要学习如何使用的时候。如果在第2步输入用户名和密码时产生了任何错误，只要在你的目录下运行 clean_sample <db_username> <db_passwd> & lt;sample_dir> ，然后重做第2步到第4步。

对于第4步，你可以单独编译每一个例程。比如单独编译 sample1.pc 。编译过程实际上有两句：

1. proc iname=sample1.pc
   把内嵌SQL代码转换为相应的库调用，输出 sample1.c
2. cc <a_number_of_flags_here> sample1.c
   主成可执行的 sample1

编译你自己的代码，如 foo.pc ，只要修改 Makefile 的几个变量：在 SAMPLES 变量中加入 foo 程序名，在 SAMPLE_SRC 变量中加入 foo.pc 源文件名。然后，写好 foo.pc 后 make foo 。 foo.pc 被预编译为 foo.c ，再编译为可执行的 foo 。C++用户要把程序名和源文件名加入到 CPPSAMPLE 和 CPPSAMPLE_SRC 而不是 SAMPLES 和 SAMPLE_SRC 。

例程运行于下面的数据库表上：

      CREATE TABLE DEPT

    (DEPTNO    NUMBER(2) NOT NULL,

     DNAME     VARCHAR2(14),

     LOC       VARCHAR2(13));



CREATE TABLE EMP

    (EMPNO     NUMBER(4) NOT NULL,

     ENAME     VARCHAR2(10),

     JOB       VARCHAR2(9),

     MGR       NUMBER(4),

     HIREDATE  DATE,

     SAL       NUMBER(7, 2),

     COMM      NUMBER(7, 2),

     DEPTNO    NUMBER(2));



CREATE TABLE PAY1

    (ENAME     VARCHAR2(10),

     SAL       NUMBER(7, 2));



CREATE TABLE PAY2

    (ENAME     VARCHAR2(10),

     SAL       NUMBER(7, 2));
    

当在第2步运行 load_samples 的时候这些表就自动创建好了。一些tuples（译注：没找到合适的词，把原词放这了）也插入了。你可以在程序运行前查看这些表也可以任意操作这些表（如：插入、删除或是更新tuples）。当你运行 clean_sample 时，这些表自动被删除。注意： clean_sample 也会清理整个 <sample_dir>； ；

确保在运行这个命令之前将你自己的文件转移到其它的地方！

你应该在运行它之前看一看源代码，头部的注释描述了程序都做些什么。例如， sample1 从一个雇员的 EMPNO 得到他的名子和工资，从 EMP 表中得到的那个雇员的佣金。

通过学习源程序你应该可以学到下面的东西：

• 如何从主机连接到Oracle
• 如何在C/C++中嵌入SQL
• 如何使用游标
• 如何使用宿主变量与数据库通讯
• 如何使用 WHENEVER 进行不同的错误处理动作
• 如何使用指示器变量检测输出中的空值

现在，你可以使用这些技术编写你自己的数据库应用程序了。And have fun!

C++用户

要使用预编译器生成合适的C++代码，你需要注意下面的事项：

• 代码被预编译器展开。要得到C++代码，你需要在执行 proc 时设置 CODE=CPP 选项。

• 解析能力。 proc 的 PARSE 选项可以是下面的值：

• PARSE=NONE . C预处理指令只能在声明节中被解析，所以所有的宿主变量需要在声明节中声明。
• PARSE=PARTIAL . C预处理指令能被解析；然而，所有的宿主变量需要在声明节中声明。
• PARSE=FULL . C预处理指令能被解析，而且宿主变量可以声明在任何地方。当 CODE 不为 CPP 时，这是默认设置；但当 CODE=CPP 时，指定 PARSE=FULL 却是个错误。

所以，C++用户必须指定 PARSE=NONE 或 PARSE=PARTIAL ，因此这也失去了在任意地方声明宿主变量的自由。更有，宿主变量必须被包在一个声明节中，如下：

        EXEC SQL BEGIN DECLARE SECTION;

    // declarations...

EXEC SQL END DECLARE SECTION;
      

你需要使用这种方法去声明所有的宿主和指示器变量。

• 文件扩展名。你要指定设置 CPP_SUFFIX=cc 或 CPP_SUFFIX=C 。

• 头文件定位。默认情况下， proc 像标准定位 stdio.h 文件一样查找头文件。然而C++有自己的头文件，如 iostream.h ，被放置在别处。所以，你需要使用 SYS_INCLUDE 选项指定 proc 查找头文件的路径。

Pro*C支持的嵌入SQL语句列表