[原]Pro*C介绍内嵌SQL

[ Translate by Z.Jingwei. Document address：http://www-db.stanford.edu/~ullman/fcdb/oracle/or-proc.html ]

Pro*C介绍
内嵌SQL

---

• 概要
• Pro*C语法
  • SQL
  • 预处理指令
  • 语句标号
• 宿主变量
  • 基础
  • 指针
  • 结构
  • 数组
  • 指示器变量
  • 数据类型同等化
• 动态SQL
• 事务
• 错误处理
  • SQLCA
  • WHENEVER语句
• Demo程序
• C++用户
• List of Embedded SQL Statements Supported by Pro*C

---

概要

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

Pro*C程序分两步编译。首先，Pro*C的预编译器识别出嵌入在程序中的SQL语句，并将这些语句转换为对SQL运行时库(SQL runtime library)中功能(functions)的适当调用。输出是纯C/C++代码和未被处理的纯C/C++代码。然后，用常规C/C++编译器编译代码并生成可执行程序。更详细的内容请参考Demo程序。

Demo程序。

---

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 */

我们会在后面的错误处理一节中讲到有关WHENEVER的含意。

错误处理一节中讲到有关WHENEVER的含意。

---

宿主变量

---

基础

宿主变量是连接宿主程序与数据库的关键。宿主变量表达式必须视为(resolve to)左值（能被赋值）。你可以像声明普通C变量一样，按着C的语法规则声明宿主变量。宿主变量的声明可以放置在任何C变量声明可以放置的地方。（C++用户需要使用"DECLARE SECTION"；参考C++ Users） Oracle可以使用的C数据类型包括：

C++ Users） Oracle可以使用的C数据类型包括：

• char
• char[n]
• int
• short
• long
• float
• double
• VARCHAR[n] - 它是能被Pro*C的预编译器识别的预处理类型（psuedo-type）。它用来 表示由空白填充(blank-padded，译注：'/0')的变长字符串。Pro*C预编译器会把它转换为有一个2字节(byte)长的域和一个n字(byte)长的字符数组的结构体。

你不能指定寄存器存储类型(译注：指针)为宿主变量。

可以在SQL表达式中使用冒号":"做前缀来引用一个宿主变量，但不能在C表达式中用分号做前缀。当使用字符串作为宿主变量时，必须省略引用；Pro*C明白你正指定一个基于宿主变量声明类型的字符串（译注：这句的意思是，当定义一个字符串做为宿主变量时char *str="string"，在嵌入SQL中使用时，要省略"*"而不是*str）。不能将C函数调用和多数的指针计算表达式作为宿主变量使用，即使它们确实被解释为左值。下面的代码同时说明了合法和不合法的宿主变量的引用：

int deptnos[3] = { 000, 111, 222 };
int get_deptno() { return deptnos[2]; }
int *get_deptnoptr() { return &(deptnos[2]); }
int main() 
{
    int x; char *y; int z;
    /* ... */
    EXEC SQL INSERT INTO emp(empno, ename, deptno)
        VALUES(:x, :y, :z); /* LEGAL */
    EXEC SQL INSERT INTO emp(empno, ename, deptno)
        VALUES(:x + 1,  /* LEGAL: the reference is to x */
               'Big Shot',  /* LEGAL: but not really a host var */
               :deptnos[2]);/* LEGAL: array element is fine */
    EXEC SQL INSERT INTO emp(empno, ename, deptno)
        VALUES(:x, :y,
               :(*(deptnos+2)));/* ILLEGAL: although it has anlvalue */
    EXEC SQL INSERT INTO emp(empno, ename, deptno)
        VALUES(:x, :y,
               :get_deptno());  /* ILLEGAL: no function calls */
    EXEC SQL INSERT INTO emp(empno, ename, deptno)
        VALUES(:x, :y,
               :(*get_depnoptr())); /* ILLEGAL: although it has an lvalue */
    /* ... */
}

---

指针

可以在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能够赋给指示器变量的值具有下面各项意义：

-1	字段（译注：原文为column，意为该字段的列）值为NULL，宿主变量的值不确定。
0	Oracle把完整的字值赋给了宿主变量。
>0	Oracle把被截断的字段值赋给了宿主变量。指示器变量返回的整形值是字段的原始长度。
-2	Oracle把被截断的字段值赋给了宿主变量，但原字段值是不确定的。（译注：这种情况可能是数值型的值被切断后不能确定原始值）

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

-1	Oracle会忽略宿主变量的值并给字段赋一个NULL值。
>=0	Oracle会将宿主变量的值赋给字段。

---

数据类型同等化

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域有下列的意义:

sqlcaid

该字符串域初始化为"SQLCA"，以指明这是个SQL通讯区。

sqlcabc

该整数域用byte标识SQLCA结构的长度。

sqlcode

这个整数域标识最近执行的SQL语句的状态码：
 

0	没有错误。
>0	语句执行但捕获异常。当Oracle根据WHERE条件查找不到任何记录，或SELECT INTO或FETCH影响记录数为0时会发生这种情况。
<0	由于一个错误Oracle不能执行SQL语句。当这个错误发生时，多数情况下当前的事务应该回滚(rollback)。

 

sqlerrm

这个内嵌结构包含两个域： 

• sqlerrml - 在sqlerrmc中保存的信息文本的长。
• sqlerrmc - 最大到70个字符(character)的信息文本，与sqlcode中存储的错误码相似。
   

sqlerrp

保留

sqlerrd

这个二进制整形数组包含6个元素： 

• sqlerrd[0] - 保留
• sqlerrd[1] - 保留
• sqlerrd[2] - 最近被执行的SQL语句影响的记录行数。
• sqlerrd[3] - 保留
• sqlerrd[4] - 分析最近执行语句出现错误，错误的开始字符的偏移。
• sqlerrd[5] - 保留
   

sqlwarn

这个单字符数组包含8个元素，用于警告标志。Oracle使用字符'W'设置一个标记。 
 

sqlwarn[0]	当其它标记被设置时设置。
sqlwarn[1]	当输出宿主变量中保存的是被截断的字段值时设置。
sqlwarn[2]	当计算SQL统计如AVG或SUM时，如果一个NULL字段不能被使用时被设置。
sqlwarn[3]	当SELECT的字段数与INTO句中指定的宿主变量数不等时设置。
sqlwarn[4]	当没有使用WHERE子句的UPDATE或DELETE语句处理了数据表中的每一行时设置。
sqlwarn[5]	当由于PL/SQL编译错误而导致procedure/function/package/package body创建命令失败时设置。
sqlwarn[6]	不再使用
sqlwarn[7]	不再使用

 

sqlext

保留

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语句列表

声明表达式
EXEC SQL ARRAYLEN	在PL/SQL中使用宿主变量
EXEC SQL BEGIN DECLARE SECTION EXEC SQL END DECLARE SECTION	声明宿主变量
EXEC SQL DECLARE	给Oracle对像命名
EXEC SQL INCLUDE	从文件中复制
EXEC SQL TYPE	同等化数据类型
EXEC SQL VAR	同等化变量
EXEC SQL WHENEVER	处理运行时错误
执行表达式
EXEC SQL ALLOCATE	声明、控制Oracle数据
EXEC SQL ALTER
EXEC SQL ANALYZE
EXEC SQL AUDIT
EXEC SQL COMMENT
EXEC SQL CONNECT
EXEC SQL CREATE
EXEC SQL DROP
EXEC SQL GRANT
EXEC SQL NOAUDIT
EXEC SQL RENAME
EXEC SQL REVOKE
EXEC SQL TRUNCATE
EXEC SQL CLOSE
EXEC SQL DELETE	排序、修改Oracle数据
EXEC SQL EXPLAIN PLAN
EXEC SQL FETCH
EXEC SQL INSERT
EXEC SQL LOCK TABLE
EXEC SQL OPEN
EXEC SQL SELECT
EXEC SQL UPDATE
EXEC SQL COMMIT	处理事务
EXEC SQL ROLLBACK
EXEC SQL SAVEPOINT
EXEC SQL SET TRANSACTION
EXEC SQL DESCRIBE	使用动态SQL
EXEC SQL EXECUTE
EXEC SQL PREPARE
EXEC SQL ALTER SESSION	控制会话
EXEC SQL SET ROLE
EXEC SQL EXECUTE END-EXEC	内嵌PL/SQL块

---

This document was written originally by Ankur Jain and Jeff Ullman for CS145, Autumn 1997; revised by Jun Yang for Prof. Jennifer Widom's CS145 class in Spring, 1998; further revisions by Roy Goldman for Prof. Jeff Ullman's CS145 class in Autumn, 1999; further revisions by Calvin Yang for Prof. Jennifer Widom's CS145 class in Spring, 2002.

作者：zjingwei 发表于2005-3-14 9:31:00 原文链接

阅读：1320 评论：0 查看评论