第四节 ORACLE数据库的嵌入SQL语言
4.1 基本的SQL语句
4.1.1宿主变量和指示符
1）、声明方法
同其他数据库管理器一样，ORACLE使用宿主变量传递数据库中的数据和状态信息到应用程序，应用程序也通过宿主变量传递数据到ORACLE数据库。根据上面两种功能，宿主变量分为输出宿主变量和输入宿主变量。在SELECT INTO和FETCH语句之后的宿主变量称作“输出宿主变量”，这是因为从数据库传递列数据到应用程序。除了SELECT INTO和FETCH语句外的其他SQL语句中的宿主变量，称为“输入宿主变量”。这是因为从应用程序向数据库输入值。如：INSERT、UPDATE等语句。请看下面这个例子：
int emp_number;
char temp[20];
VARCHAR emp_name[20];
/* get values for input host variables */
printf("Employee number? ");
gets(temp);
emp_number = atoi(temp);
printf("Employee name? ");
gets(emp_name.arr);
emp_name.len = strlen(emp_name.arr);
EXEC SQL INSERT INTO EMP (EMPNO, ENAME)
valueS (:emp_number, :emp_name);
在上面这个例子中，其中的emp_number和emp_name就是宿主变量。值得注意的是，它同其他数据库的区别是，定义宿主变量可以不需要BEGIN DECLARE SECTION和END DECLARE SECTION。
2）、指示符变量
大多数程序设计语言（如C）都不支持NULL。所以对NULL的处理，一定要在SQL中完成。我们可以使用主机指示符变量来解决这个问题。在嵌入式SQL语句中，主变量和指示符变量共同规定一个单独的SQL类型值。指示符变量是一个2字节的整数。
针对输入宿主变量和输出宿主变量，指示变量共有下面几种情况：
同输入宿主变量一起使用时：
-1 Oracle将null赋值给列，即宿主变量应该假设为NULL。
>=0 Oracle将宿主变量的实际值赋值给列。
同输出宿主变量一起使用时：
-1 表示该列的输出值为NULL。
0 Oracle已经将列的值赋给了宿主变量。列值未做截断。
>0 Oracle将列的值截断，并赋给了宿主变量。指示变量中存放了这个列的实际长度。
-2 Oracle将列的值截断，并赋给了宿主变量。但是这个列的实际长度不能确定。
从数据库中查询数据时，可以使用指示符变量来测试NULL：
EXEC SQL SELECT ename, sal
INTO :emp_name, :salary
FROM emp
WHERE :commission INDICATOR :ind_comm IS NULL ...
注意，不能使用关系操作符来比较NULL，这是因为NULL和任何操作都为false。如：
EXEC SQL SELECT ename, sal
INTO :emp_name, :salary
FROM emp
WHERE comm = :commission
如果comm列的某些行存在NULL，则该SELECT语句不能返回正确的结果。应该使用下面这个语句完成：
EXEC SQL SELECT ename, sal
INTO :emp_name, :salary
FROM emp
WHERE (comm = :commission) OR ((comm IS NULL) AND
(:commission INDICATOR :ind_comm IS NULL));
4.1.2 查询
如果是单行查询，则应该使用SELECT INTO语句。如果是多行查询，应该使用游标或宿主变量数组。如：单行查询的一个例子：
EXEC SQL SELECT ename, job, sal + 2000
INTO :emp_name, :job_title, :salary
FROM emp
WHERE empno = :emp_number;
在嵌入SQL语句中，也可以使用子查询。如：
EXEC SQL INSERT INTO emp2 (empno, ename, sal, deptno)
SELECT empno, ename, sal, deptno FROM emp
WHERE job = :job_title;
4.1.3 修改数据
1）、插入数据
使用INSERT语句插入数据。其语法同ANSI SQL语法类似。如：
EXEC SQL INSERT INTO emp (empno, ename, sal, deptno)
valueS (:emp_number, :emp_name, :salary, :dept_number);
2）、更新数据
使用UPDATE语句更新数据。其语法同ANSI SQL语法类似。如：
EXEC SQL UPDATE emp
SET sal = :salary, comm = :commission
WHERE empno = :emp_number;
3）、删除数据
使用DELETE语句删除数据。其语法同ANSI SQL语法类似。如：
EXEC SQL DELETE FROM emp
WHERE deptno = :dept_number;
4.1.4 游标
用嵌入式SQL语句查询数据分成两类情况。一类是单行结果，一类是多行结果。对于单行结果，可以使用SELECT INTO语句；对于多行结果，你必须使用游标来完成。游标是一个与SELECT语句相关联的符号名，它使用户可逐行访问由ORACLE返回的结果集。使用游标，应该包含以下四个步骤。
1）、定义游标
使用DECLARE语句完成。如：
EXEC SQL DECLARE emp_cursor CURSOR FOR
SELECT ename FROM emp WHERE deptno = :dept_number;
值得注意的是，不能在同一个文件中定义两个相同名字的游标。游标的作用范围是全局的。
2）、打开游标
使用OPEN语句完成。如：
EXEC SQL OPEN emp_cursor;
3）、取一行值
使用FETCH语句完成。如：
EXEC SQL FETCH emp_cursor INTO :emp_name;
4）、关闭游标
使用CLOSE语句完成。它完成的功能是：释放资源，如占用内存，锁等。如：EXEC SQL CLOSE emp_cursor;
5）、使用游标修改数据
我们可以使用CURRENT OF子句来完成修改数据。如：
EXEC SQL DECLARE emp_cursor CURSOR FOR
SELECT ename, sal FROM emp WHERE job = 'CLERK'
FOR UPDATE OF sal;
...
EXEC SQL OPEN emp_cursor;
EXEC SQL WHENEVER NOT FOUND GOTO ...
for (;;) {
EXEC SQL FETCH emp_cursor INTO :emp_name, :salary;
...
EXEC SQL UPDATE emp SET sal = :new_salary
WHERE CURRENT OF emp_cursor;
}
值得注意的是，在使用CURRENT OF子句来完成修改数据时，在OPEN时会对数据加上排它锁。这个锁直到有COMMIT或ROLLBACK语句时才释放。
以下是使用游标修改数据的一个完整例子：
...
/* 定义游标 */
EXEC SQL DECLARE emp_cursor CURSOR FOR
SELECT ename, job
FROM emp
WHERE empno = :emp_number
FOR UPDATE OF job;
/* 打开游标 */
EXEC SQL OPEN emp_cursor;
/* break if the last row was already fetched */
EXEC SQL WHENEVER NOT FOUND DO break;
/* 循环取值*/
for (;;)
{
EXEC SQL FETCH emp_cursor INTO :emp_name, :job_title;
/* 更新当前游标所在的行的数据*/
EXEC SQL UPDATE emp
SET job = :new_job_title
WHERE CURRENT OF emp_cursor;
}
...
/* 关闭游标 */
EXEC SQL CLOSE emp_cursor;
EXEC SQL COMMIT WORK RELEASE;
...
下面这个例子完整演示了静态游标的使用方法。这个例子的作用是，获得部门编号，通过游标来显示这个部门中的所有雇员信息。
#include
/* 声明宿主变量 */
char userid[12] = "SCOTT/TIGER";
char emp_name[10];
int emp_number;
int dept_number;
char temp[32];
void sql_error();
/*包含SQLCA */
#include
main()
{ emp_number = 7499;
/* 处理错误*/
EXEC SQL WHENEVER SQLERROR do sql_error("Oracle error");
/* 连接到Oracle数据库*/
EXEC SQL CONNECT :userid;
printf("Connected.\n");
/* 声明游标 */
EXEC SQL DECLARE emp_cursor CURSOR FOR
SELECT ename FROM emp WHERE deptno = :dept_number;
printf("Department number? ");
gets(temp);
dept_number = atoi(temp);
/* 打开游标*/
EXEC SQL OPEN emp_cursor;
printf("Employee Name\n");
printf("-------------\n");
/* 循环处理每一行数据，如果无数据，则退出*/
EXEC SQL WHENEVER NOT FOUND DO break;
while (1)
{
EXEC SQL FETCH emp_cursor INTO :emp_name;
printf("%s\n", emp_name);
}
EXEC SQL CLOSE emp_cursor;
EXEC SQL COMMIT WORK RELEASE;
exit(0);
}
/错误处理程序*/
void sql_error(msg)
char *msg;
{
char buf[500];
int buflen, msglen;
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ROLLBACK WORK RELEASE;
buflen = sizeof (buf);
sqlglm(buf, &buflen, &msglen);
printf("%s\n", msg);
printf("%*.s\n", msglen, buf);
exit(1);
}
4.2 嵌入PL/SQL
嵌入PL/SQL和嵌入SQL不同。嵌入PL/SQL提供了很多嵌入SQL不具有的优点，如：更好的性能、更灵活的表达方式。能够自己定义过程和函数。如：
PROCEDURE create_dept
(new_dname IN CHAR(14),
new_loc IN CHAR(13),
new_deptno OUT NUMBER(2)) IS
BEGIN
SELECT deptno_seq.NEXTVAL INTO new_deptno FROM dual;
INSERT INTO dept valueS (new_deptno, new_dname, new_loc);
END create_dept;
其中的IN/OUT，表示参数模式。IN是传递参数值到过程，而OUT是从过程传递参数值到调用者。
但是，如果使用这些扩展的功能，也会造成同其他数据库厂商的嵌入SQL的不兼容。
4.3 动态SQL语句
4.3.1 ORACLE动态SQL语句的一些特点
ORACLE DBMS进入市场的时间早于DB2，其动态SQL支持是以IBM的system/R原型为基础的。因此，ORACLE支持的动态SQL与IBM的DB2标准有不同。虽然ORACLE和DB2在很大程度上是兼容的，但是在使用参数标志、SQLDA格式及支持数据类型转换等方面都有差异。
DB2中不允许在PREPARE的动态语句中引用宿主变量，而是用问号来标志语句中的参数，然后用EXECUTE或OPEN语句来规定参数值。ORACLE允许用户用宿主变量规定动态语句中的参数。
而且，ORACLE支持的DESCRIBE语句同DB2有一些区别。如：
从已经PREPARE后的动态查询语句中获得对查询结果列的信息的语句为：
EXEC SQL DESCRIBE SELECT LIST FOR qrystmt INTO qry_sqlda;
等价于DB2的：
EXEC SQL DESCRIBE qrystmt INTO qry_sqlda;
从已经PREPARE后的动态查询语句中获得对查询参数的说明的语句为：
EXEC SQL DESCRIBE BIND LIST FOR qrystmt INTO qry_sqlda;
该ORACLE语句没有对应的DB2语句。用户只能按照当前需要的参数和SQLDA的结构对SQLDA赋值。然后再在OPEN语句或EXECUTE语句中使用SQLDA结构。
4.3.2 使用动态SQL的四种方法
使用动态SQL，共分成四种方法：
方法 支持的SQL语句
1 该语句不包含宿主变量，该语句不是查询语句
2 该语句包含输入宿主变量 ，该语句不是查询语句
3 包含已知数目的输入宿主变量或列的查询
4 包含未知数目的输入宿主变量或列的查询
l方法1：使用EXECUTE IMMEDIATE命令实现，具体语法为：
EXEC SQL EXECUTE IMMEDIATE { :host_string | string_literal };
其中，host_variable和string是存放完整T-SQL语句。
请看下面这个例子。这个例子的作用是执行用户随意输入的合法的SQL语句。
char dyn_stmt[132];
...
for (;;)
{
printf("Enter SQL statement: ");
gets(dyn_stmt);
if (*dyn_stmt == '\0')
break;
/* dyn_stmt now contains the text of a SQL statement */
EXEC SQL EXECUTE IMMEDIATE :dyn_stmt;
}
...
EXECUTE IMMEDIATE命令的作用是：分析该语句的语法，然后执行该语句。方法1适合于仅仅执行一次的语句。
l方法2：方法支持的语句可以包含输入宿主变量。这个语句首先做PREPARE操作，然后通过EXECUTE执行。PREPARE语句的语法为：
EXEC SQL PREPARE statement_name FROM { :host_string | string_literal };
该语句接收含有SQL语句串的宿主变量，并把该语句送到ORACLE。ORACLE编译语句并生成执行计划。在语句串中包含一个“？”表明参数，当执行语句时，ORACLE需要参数来替代这些“？”。PREPRARE执行的结果是，DBMS用语句名标志准备后的语句。在执行SQL语句时，EXECUTE语句后面是这个语句名。EXECUTE语句的语法为：
EXECUTE 语句名 USING 宿主变量 | DEscriptOR 描述符名
它的作用是，请求ORACLE执行PREPARE语句准备好的语句。当要执行的动态语句中包含一个或多个参数标志时，在EXECUTE语句必须为每一个参数提供值。这样的话，EXECUTE语句用宿主变量值逐一代替准备语句中的参数标志（“？”或其他占位符），从而，为动态执行语句提供了输入值。
使用主变量提供值，USING子句中的主变量数必须同动态语句中的参数标志数一致，而且每一个主变量的数据类型必须同相应参数所需的数据类型相一致。各主变量也可以有一个伴随主变量的指示符变量。当处理EXECUTE语句时，如果指示符变量包含一个负值，就把NULL值赋予相应的参数标志。除了使用主变量为参数提供值，也可以通过SQLDA提供值。
请看下面这个例子。这个例子的作用是删除用户指定的雇员信息。
...
int emp_number INTEGER;
char delete_stmt[120], search_cond[40];;
...
strcpy(delete_stmt, "DELETE FROM EMP WHERE EMPNO = :n AND ");
printf("Complete the following statement's search condition--\n");
printf("%s\n", delete_stmt);
gets(search_cond);
strcat(delete_stmt, search_cond);
EXEC SQL PREPARE sql_stmt FROM :delete_stmt;
for (;;)
{
printf("Enter employee number: ");
gets(temp);
emp_number = atoi(temp);
if (emp_number == 0)
break;
EXEC SQL EXECUTE sql_stmt USING :emp_number;
}
l方法三：是指查询的列数或输入宿主变量数在预编译时已经确定，但是数据库中的对象，如表、列名等信息未确定。这些对象名不能是宿主变量。这时，必须通过以下语句来完成：
PREPARE statement_name FROM { :host_string | string_literal };
DECLARE cursor_name CURSOR FOR statement_name;
OPEN cursor_name [USING host_variable_list];
FETCH cursor_name INTO host_variable_list;
CLOSE cursor_name;
如：下面这个例子演示用方法3完成动态查询：
char select_stmt[132] =
"SELECT MGR, JOB FROM EMP WHERE SAL < :salary";
EXEC SQL PREPARE sql_stmt FROM :select_stmt;
EXEC SQL DECLARE emp_cursor CURSOR FOR sql_stmt;
EXEC SQL OPEN emp_cursor USING :salary;
EXEC SQL FETCH emp_cursor INTO :mgr_number, :job_title;
EXEC SQL CLOSE emp_cursor;
l方法四：在预编译时，查询的列数或者宿主变量的个数不能确定，因为不知道具体的返回个数，所以不能使用输出宿主变量。这是因为你不知道应该定义多少个宿主变量。这时，就需要SQLDA结构和DESCRIBE命令。SQLDA包含了动态查询的列描述信息。对于输入宿主变量，也可以使用SQLDA来完成不确定的参数说明。要完成方法四，必须通过以下语句来完成：
EXEC SQL PREPARE statement_name FROM { :host_string | string_literal };
EXEC SQL DECLARE cursor_name CURSOR FOR statement_name;
EXEC SQL DESCRIBE BIND VARIABLES FOR statement_name
INTO bind_descriptor_name;
EXEC SQL OPEN cursor_name
[USING DEscriptOR bind_descriptor_name];
EXEC SQL DESCRIBE [SELECT LIST FOR] statement_name
INTO select_descriptor_name;
EXEC SQL FETCH cursor_name USING DEscriptOR select_descriptor_name;
EXEC SQL CLOSE cursor_name;
在上述语句中，DESCRIBE SELECT LIST的作用是将PREPARE后的动态查询语句的列名、数据类型、长度等信息保存在SQLDA中。DESCRIBE BIND VARIABLES的作用是，检查PREPARE后的动态查询语句的每个占位符的名字、数据类型、长度等信息。并将它存放在SQLDA中，然后，使用SQLDA提示用户数据参数值。
值得注意的是，方法之间可以混合使用。如：在一个查询中，列的个数确定，但是查询中的占位符不确定，这时，你可以结合方法3和方法4，即使用方法3的FETCH语句和方法4的OPEN语句，如：EXEC SQL FETCH emp_cursor INTO host_variable_list; 反之，如果查询中占位符的个数确定，而列数不确定，则你可以使用方法3的OPEN语句，如：EXEC SQL OPEN cursor_name [USING host_variable_list];
这里，我们讲解的是嵌入SQL，对于嵌入PL/SQL，有一些区别。简单来说，主要有两点：
l预编译器将PL/SQL块中的所有宿主变量都作为输入宿主变量。
l不能对PL/SQL块使用FETCH命令。
l占位符不用声明，可以是任何名字。如：
INSERT INTO emp (empno, deptno) valueS (:e, :d)
DELETE FROM dept WHERE deptno = :num OR loc = :loc
其中的e、d、num和loc就是占位符。
4.3.3 SQLDA
SQLDA存放了输出数据的信息，或存放了输入数据的信息。可以使用SQLSQLDAAlloc(runtime_context, size, name_length, ind_name_length)来分配空间。
SQLDA结构的定义存放在sqlda.h文件中。它的内容为：
struct SQLDA
{
long N; /* Descriptor size in number of entries */
char **V; /*Ptr to Arr of addresses of main variables */
long *L; /* Ptr to Arr of lengths of buffers */
short *T; /* Ptr to Arr of types of buffers */
short **I; /* Ptr to Arr of addresses of indicator vars */
long F; /* Number of variables found by DESCRIBE */
char **S; /* Ptr to Arr of variable name pointers */
short *M; /* Ptr to Arr of max lengths of var. names */
short *C; /* Ptr to Arr of current lengths of var. names */
char **X; /* Ptr to Arr of ind. var. name pointers */
short *Y; /* Ptr to Arr of max lengths of ind. var. names */
short *Z; /* Ptr to Arr of cur lengths of ind. var. names */
};
其中，上述变量的含义为：
lN：可以容纳的列的最大数目或参数的最大数目。它对应于DB2的SQLDA的SQLN字段。
lF：当前SQLDA中的实际列数或参数个数。它对应于DB2的SQLDA的SQLD字段。
lT：指明数据类型。它对应于DB2的SQLVAR结构中的SQLTYPE字段。
lV：指向字符数组。该字符数组可能是列的数据，或传送参数的数据。它对应于DB2的SQLVAR结构中的SQLDATA字段。
lL：给出列或参数值的长度。它对应于DB2的SQLVAR结构中的SQLLEN字段。
lI：指向指示符变量，标志数据是否为NULL。它对应于DB2的SQLVAR结构中的SQLIND字段。
lS：指向存放列名或参数名的字符数组。它对应于DB2的SQLVAR结构中的SQLNAME结构的data[]。
lM：指向一个整数，该整数是S的申请长度。在DB2中，SQLVAR结构中的SQLNAME结构的data[30]的大小是固定的，即是30。而ORACLE中是可变的。其大小为M指向的整数。
lC：指向一个整数，该整数是S的实际长度。它对应于DB2的SQLVAR结构中的SQLNAME结构的 length。
lX：指向一个字符数组。该字符数组存放了指示符变量的名称，指示符变量表示传递的参数是否为NULL。DB2中无相应的对应字段。这个缓冲区仅仅供DESCRIBE BIND LIST语句使用。
lY：指向一个整数，该整数是X的申请的最大长度。DB2中无相应的对应字段。
lZ：指向一个整数，该整数是X的实际长度。DB2中无相应的对应字段。
ORACLE的数据类型分成两种情况：内部数据类型和外部数据类型。ORACLE的内部数据类型是ORACLE在数据库中存放数据的类型，在使用DESCRIBE SELECT LIST命令，就返回内部数据类型代码。下表是所有的内部数据类型：
Oracle 内部数据类型 代码
VARCHAR2 1
NUMBER 2
LONG 8
ROWID 11
DATE 12
RAW 23
LONG RAW 24
CHARACTER (or CHAR) 96
MLSLABEL 106
外部数据类型是输入宿主变量和输出宿主变量存放数据的类型。DESCRIBE BIND VARIABLES命令将SQLDA中的数据类型代码置为0。所以，必须在OPEN语句前设置外部数据类型代码，以告诉ORACLE是什么外部数据类型。下表是具体的外部数据类型：
外部数据类型 代码 C数据类型
VARCHAR2 1 char[n]
NUMBER 2 char[n] (n 22)
INTEGER 3 int
FLOAT 4 float
STRING 5 char[n+1]
VARNUM 6 char[n] (n 22)
DECIMAL 7 float
LONG 8 char[n]
VARCHAR 9 char[n+2]
ROWID 11 char[n]
DATE 12 char[n]
VARRAW 15 char[n]
RAW 23 unsigned char[n]
LONG RAW 24 unsigned char[n]
UNSIGNED 68 unsigned int
DISPLAY 91 char[n]
LONG VARCHAR 94 char[n+4]
LONG VARRAW 95 unsigned char[n+4]
CHAR 96 char[n]
CHARF 96 char[n]
CHARZ 97 char[n+1]
MLSLABEL 106 char[n]
当ORACLE从用户程序中接收参数值并向用户程序传送查询结果时，就在自己的内部数据格式与它所运行的计算机系统的数据格式之间自动进行数据转换。DESCRIBE SELECT L IST命令可以返回ORACLE的内部数据类型。对于字符数据，内部数据类型同外部数据类型是相一致的；而有些内部数据类型对应到外部数据类型后，导致处理复杂化，如：你想将NUMBER数据类型的值处理为C中的FLOAT，那么你可以设置相应的T值为FLOAT（4）和L值为FLOAT的长度。在FETCH时，ORACLE自动在内部数据类型和外部数据类型之间转换。
在DB2的SQLVAR结构中，列的说明信息、数据等存放在一个单独的sqlvar结构中。而在ORACLE数据库中，不存在一个单独的结构来说明每列的信息。而是通过数组的方式实现。如下图所示，描述了1个输入参数，参数名为bonus。假设的最大参数个数为3。
SQLDA结构
N=3
V
L
T
I
F=1 describe设置
S
N
C
X
Y
Z
图6-5 SQLDA结构示例
下面这个例子是一个adhoc程序。用户输入任何合法的SQL语句（可以带参数），该程序能够处理这个语句，并打印出结果。这个例子非常经典，说明使用SQLDA的两个功能。
#include
#include
#include
/* 列的最大数目或宿主变量的最大个数*/
#define MAX_ITEMS 40
/* 列名的最大长度或指示符的最大长度*/
#define MAX_VNAME_LEN 30
#define MAX_INAME_LEN 30
#ifndef NULL
#define NULL 0
#endif
char *dml_commands[] = {"SELECT", "select", "INSERT", "insert",
"UPDATE", "update", "DELETE", "delete"};
EXEC SQL BEGIN DECLARE SECTION;
char dyn_statement[1024];
EXEC SQL VAR dyn_statement IS STRING(1024);
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE sqlca;
EXEC SQL INCLUDE sqlda;
SQLDA *bind_dp;
SQLDA *select_dp;
extern SQLDA *SQLSQLDAAlloc();
extern void sqlnul();
/* Define a buffer to hold longjmp state info. */
jmp_buf jmp_continue;
/* A global flag for the error routine. */
int parse_flag = 0;
main()
{
int oracle_connect();
int alloc_descriptors();
int get_dyn_statement();
int set_bind_variables();
int process_select_list();
int i;
/*连接到数据库 */
if (oracle_connect() != 0)
exit(1);
/* 为SQLDA分配空间*/
if (alloc_descriptors(MAX_ITEMS, MAX_VNAME_LEN, MAX_INAME_LEN) != 0)
exit(1);
/* 处理SQL 语句*/
for (;;)
{
i = setjmp(jmp_continue);
/* 获取SQL语句。输入"exit"表示退出 */
if (get_dyn_statement() != 0)
break;
/* 对该SQL语句做PREPARE操作 */
EXEC SQL WHENEVER SQLERROR DO sql_error();
parse_flag = 1; /* Set a flag for sql_error(). */
EXEC SQL PREPARE S FROM :dyn_statement;
parse_flag = 0; /* Unset the flag. */
/*声明游标*/
EXEC SQL DECLARE C CURSOR FOR S;
/* 提示用户输入参数值*/
set_bind_variables();
/* 打开游标 */
EXEC SQL OPEN C USING DEscriptOR bind_dp;
/* 处理语句，并输出结果*/
process_select_list();
/*输出处理的行数. */
for (i = 0; i < 8; i++)
{
if (strncmp(dyn_statement, dml_commands, 6) == 0)
{
printf("\n\n%d row%c processed.\n",
sqlca.sqlerrd[2],
sqlca.sqlerrd[2] == 1 ? '\0' : 's');
break;
}
}
} /* end of for(;;) statement-processing loop */
/* 释放申请的空间*/
for (i = 0; i < MAX_ITEMS; i++)
{
if (bind_dp->V != (char *) 0)
free(bind_dp->V);
free(bind_dp->I); /* MAX_ITEMS were allocated. */
if (select_dp->V != (char *) 0)
free(select_dp->V);
free(select_dp->I); /* MAX_ITEMS were allocated. */
}
SQLSQLDAFree(SQL_SINGLE_RCTX, bind_dp);
SQLSQLDAFree(SQL_SINGLE_RCTX, select_dp);
EXEC SQL WHENEVER SQLERROR CONTINUE;
/* 关闭游标*/
EXEC SQL CLOSE C;
EXEC SQL COMMIT WORK RELEASE;
puts("\nHave a good day!\n");
EXEC SQL WHENEVER SQLERROR DO sql_error();
return;
}
/*连接数据库函数*/
oracle_connect()
{
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR username[128];
VARCHAR password[32];
EXEC SQL END DECLARE SECTION;
/提示用户输入用户名*/
printf("\nusername: ");
fgets((char *) username.arr, sizeof username.arr, stdin);
fflush(stdin);
username.arr[strlen((char *) username.arr)-1] = '\0';
username.len = strlen((char *) username.arr);
/*提示用户输入口令*/
printf("password: ");
fgets((char *) password.arr, sizeof password.arr, stdin);
fflush(stdin);
password.arr[strlen((char *) password.arr) - 1] = '\0';
password.len = strlen((char *) password.arr);
EXEC SQL WHENEVER SQLERROR GOTO connect_error;
/*连接数据库*/
EXEC SQL CONNECT :username IDENTIFIED BY :password;
printf("\nConnected to ORACLE as user %s.\n", username.arr);
return 0;
/*连接错误处理*/
connect_error:
fprintf(stderr, "Cannot connect to ORACLE as user %s\n",
username.arr);
return -1;
}
/*为SQLDA分配空间*/
alloc_descriptors(size, max_vname_len, max_iname_len)
int size;
int max_vname_len;
int max_iname_len;
{
int i;
/*SQLSQLDAAlloc的第一个参数是SQL语句的最大列数或输入宿主变量的最大个数。
*第二个参数，是指列名的最大长度，或参数名的最大长度。
*第三个参数，是指指示符变量名的最大长度。*/
/*给SQLDA分配空间，下面这个SQLDA用于输入参数*/
if ((bind_dp =
SQLSQLDAAlloc(SQL_SINGLE_RCTX, size,
max_vname_len, max_iname_len)) == (SQLDA *) 0)
{
fprintf(stderr, "Cannot allocate memory for bind descriptor.");
return -1; /* Have to exit in this case. */
}
/*给SQLDA分配空间，下面这个SQLDA用于动态查询*/
if ((select_dp =
SQLSQLDAAlloc (SQL_SINGLE_RCTX, size,
max_vname_len, max_iname_len)) == (SQLDA *) 0)
{
fprintf(stderr,"Cannot allocate memory for select descriptor.");
return -1;
}
/*设置最大的列数，或最大的变量数*/
select_dp->N = MAX_ITEMS;
/* 给存放指示符变量值和存放数据的变量申请空间。*/
for (i = 0; i < MAX_ITEMS; i++) {
bind_dp->I = (short *) malloc(sizeof (short));
select_dp->I = (short *) malloc(sizeof(short));
bind_dp->V = (char *) malloc(1);
select_dp->V = (char *) malloc(1);
}
return 0;
}
/*获得SQL语句，可略看*/
get_dyn_statement()
{
char *cp, linebuf[256];
int iter, plsql;
int help();
for (plsql = 0, iter = 1; )
{
if (iter == 1)
{
printf("\nSQL> ");
dyn_statement[0] = '\0';
}
fgets(linebuf, sizeof linebuf, stdin);
fflush(stdin);
cp = strrchr(linebuf, '\n');
if (cp && cp != linebuf)
*cp = ' ';
else if (cp == linebuf)
continue;
if ((strncmp(linebuf, "EXIT", 4) == 0) ||
(strncmp(linebuf, "exit", 4) == 0))
{
return -1;
}
else if (linebuf[0] == '?' ||
(strncmp(linebuf, "HELP", 4) == 0) ||
(strncmp(linebuf, "help", 4) == 0))
{
help();
iter = 1;
continue;
}
if (strstr(linebuf, "BEGIN") ||
(strstr(linebuf, "begin")))
{
plsql = 1;
}
strcat(dyn_statement, linebuf);
if ((plsql && (cp = strrchr(dyn_statement, '/'))) ||
(!plsql && (cp = strrchr(dyn_statement, ';'))))
{
*cp = '\0';
break;
}
else
{
iter++;
printf("%3d ", iter);
}
}
return 0;
}
/*设置宿主变量的信息*/
set_bind_variables()
{
int i, n;
char bind_var[64];
/* 通过DESCRIBE语句，将处理语句的参数名、数据类型等信息存放在bind_dp中*/
EXEC SQL WHENEVER SQLERROR DO sql_error();
bind_dp->N = MAX_ITEMS; /* Init. count of array elements. */
EXEC SQL DESCRIBE BIND VARIABLES FOR S INTO bind_dp;
/* F小于0，表示SQLSQLDAAlloc()分配的空间不够，即实际参数的个数超出预算的最大值. */
if (bind_dp->F < 0)
{
printf("\nToo many bind variables (%d), maximum is %d.\n",
-bind_dp->F, MAX_ITEMS);
return;
}
/* 将N（最大值）设置为实际的参数个数*/
bind_dp->N = bind_dp->F;
/* 提示用户输入参数值，并设置SQLDA的其他相关值，如：长度等。*/
for (i = 0; i < bind_dp->F; i++)
{
printf ("\nEnter value for bind variable %.*s: ",
(int)bind_dp->C, bind_dp->S);
fgets(bind_var, sizeof bind_var, stdin);
/* 获得长度，去掉NULL结束符 */
n = strlen(bind_var) - 1;
/*设置参数长度 */
bind_dp->L = n;
/* 分配存放参数数据的内存空间 */
bind_dp->V = (char *) realloc(bind_dp->V, (bind_dp->L + 1));
/* 将数据放在这个内存空间中 */
strncpy(bind_dp->V, bind_var, n);
/* 设置指示符变量的值*/
if ((strncmp(bind_dp->V, "NULL", 4) == 0) ||
(strncmp(bind_dp->V, "null", 4) == 0))
*bind_dp->I = -1;
else
*bind_dp->I = 0;
/* 设置数据类型为CHAR，ORACLE会根据列的数据类型自动转换 */
bind_dp->T = 1;
}
}
/*处理语句*/
process_select_list()
{
int i, null_ok, precision, scale;
/*如果不是查询语句，则设置F（即返回的列数）为0*/
if ((strncmp(dyn_statement, "SELECT", 6) != 0) &&
(strncmp(dyn_statement, "select", 6) != 0))
{
select_dp->F = 0;
return;
}
/* 如果是SELECT语句，则通过DESCRIBE函数返回列名、数据类型、长度和是否为NULL标志*/
select_dp->N = MAX_ITEMS;
EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;
/* 如果F小于0。则表示比预定的列数要多。*/
if (select_dp->F < 0)
{
printf
("\nToo many select-list items (%d), maximum is %d\n",
-(select_dp->F), MAX_ITEMS);
return;
}
/* 设置最大列数为实际列数*/
select_dp->N = select_dp->F;
/* 为每列分配空间。
SQLNumberPrecV6() 函数的作用是从select_dp->L获得精度和长度。
SQLColumnNullCheck() 函数的作用是检查该列是否为NULL。*/
printf ("\n");
for (i = 0; i < select_dp->F; i++)
{
/* 关闭最高位*/
SQLColumnNullCheck (&(select_dp->T),
&(select_dp->T), &null_ok);
switch (select_dp->T)
{
case 1 : /* CHAR */
break;
case 2 : /* NUMBER ，获得精度和范围*/
SQLNumberPrecV6 (SQL_SINGLE_RCTX, &(select_dp->L), &precision,
&scale);
/* 如果精度为0，则设置为最大值40 */
if (precision == 0) precision = 40;
if (scale > 0)
select_dp->L = sizeof(float);
else
select_dp->L = sizeof(int);
break;
case 8 : /* LONG*/
select_dp->L = 240;
break;
case 11 : /* ROWID datatype */
select_dp->L = 18;
break;
case 12 : /* DATE datatype */
select_dp->L = 9;
break;
case 23 : /* RAW datatype */
break;
case 24 : /* LONG RAW datatype */
select_dp->L = 240;
break;
}
/* 申请空间给SQLDA来存放数据*/
if (select_dp->T != 2)
select_dp->V = (char *) realloc(select_dp->V,
select_dp->L + 1);
else
select_dp->V = (char *) realloc(select_dp->V,
select_dp->L);
/* 输出列名*/
if (select_dp->T == 2)
if (scale > 0)
printf ("%.*s ",select_dp->L+3, select_dp->S);
else
printf ("%.*s ", select_dp->L, select_dp->S);
else
printf ("%-.*s ", select_dp->L, select_dp->S);
/* 除了LONG RAW和NUMBER，其他数据类型转换为字符型数据类型*/
if (select_dp->T != 24 && select_dp->T != 2)
select_dp->T = 1;
/* 将 NUMBER数据类型转换为浮点型数据类型或int数据类型*/
if (select_dp->T == 2)
if (scale > 0)
select_dp->T = 4; /* float */
else
select_dp->T = 3; /* int */
}
printf ("\n\n");
/* 取出每一行数据*/
EXEC SQL WHENEVER NOT FOUND GOTO end_select_loop;
for (;;)
{
EXEC SQL FETCH C USING DEscriptOR select_dp;
/*输出列数据。除了float和int数据类型，其他数据类型都被转为字符型*/
for (i = 0; i < select_dp->F; i++)
{
if (*select_dp->I < 0)
if (select_dp->T == 4)
printf ("%-*c ",(int)select_dp->L+3, ' ');
else
printf ("%-*c ",(int)select_dp->L, ' ');
else
if (select_dp->T == 3) /* int datatype */
printf ("%*d ", (int)select_dp->L,
*(int *)select_dp->V);
else if (select_dp->T == 4)/* float datatype*/
printf ("%*.2f ", (int)select_dp->L,
*(float *)select_dp->V);
else /* character string */
printf ("%-*s ",
(int)select_dp->L, select_dp->V);
}
printf ("\n");
}
end_select_loop:
return;
}
help()
{
puts("\n\nEnter a SQL statement or a PL/SQL block");
puts("at the SQL> prompt.");
puts("Statements can be continued over several");
puts("lines, except within string literals.");
puts("Terminate a SQL statement with a semicolon.");
puts("Terminate a PL/SQL block");
puts("(which can contain embedded semicolons)");
puts("with a slash (/).");
puts("Typing \"exit\" (no semicolon needed)");
puts("exits the program.");
puts("You typed \"?\" or \"help\"");
puts(" to get this message.\n\n");
}
sql_error()
{
int i;
/* ORACLE error handler */
printf ("\n\n%.70s\n",sqlca.sqlerrm.sqlerrmc);
if (parse_flag)
printf("Parse error at character offset %d.\n",
sqlca.sqlerrd[4]);
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ROLLBACK WORK;
longjmp(jmp_continue, 1);
}
SQLDA中的sqldata存放着指向数据的地址。你可以认为，如果查询出的数据是整数（如：258），那么这个地址是指向整数的地址（也就是说，INTEL是高位在后，低位在前。所以第一个字节为00000001，第二个字节为00000010），如果查询出的数据是字符（‘2'），那么这个地址是指向字符的地址(也就是说，第一个字节为00110010—2的ASCII码)。又因为，sqldata声明的是指向字符的指针，所以，你必须按照不同的数据类型做转换，即：对于整数，应该是（*（int *）sqldata)，告诉系统，sqldata目前指向的数据应该按照整数来解释。如果按照字符来解释，那么第一个字符是ASCII值为1的字符，显然不正确。从数据库向sqldata赋值时，是直接赋值。如：*p=*q，*（P+1）=*（Q+1）。如果从数据库查询出数据为258，则存放在sqlda中也是258，存放格式为：第一个字节为00000001，第二个字节为00000010。你可以执行以下语句，来体会上述论述。
#include
main()
{
int li_i;
int * lp_int;
char * lp_char;
char lc_char;
li_i=258;
lp_char=malloc(10);
lp_int=lp_char;
*lp_int=258;
/*(lp_char+2)='\0';*/
printf("*lp_char=%d\n",*((int *)lp_char));
printf("*lp_char=%s\n",lp_char);
return;
}
第五节INFORMIX的嵌入SQL/C语言
5.1 一个简单的入门例子
例1、查询customer表中所有lname的第一个字符小于C的顾客信息。
#include
/*定义两个常量*/
EXEC SQL define FNAME_LEN 15;
EXEC SQL define LNAME_LEN 15;
main()
{
/*声明宿主变量*/
EXEC SQL BEGIN DECLARE SECTION;
char fname[ FNAME_LEN + 1 ];
char lname[ LNAME_LEN + 1 ];
EXEC SQL END DECLARE SECTION;
printf( "DEMO1 Sample ESQL Program running.\n\n");
/*出错处理，如果返回错误信息，则停止该程序*/
EXEC SQL WHENEVER ERROR STOP;
/*连接到stores7数据库*/
EXEC SQL connect to 'stores7';
/*声明一个游标*/
EXEC SQL DECLARE democursor cursor for
select fname, lname
into :fname, :lname
from customer
where lname < 'C';
/*打开游标*/
EXEC SQL open democursor;
/*如果SQLSTATE不等于“00”，那么表示到达了数据集的尾部（02），或者产生了错误（大于02）*/
for (;;)
{
EXEC SQL fetch democursor;
if (strncmp(SQLSTATE, "00", 2) != 0)
break;
printf("%s %s\n",fname, lname);
}
/*打印错误信息*/
if (strncmp(SQLSTATE, "02", 2) != 0)
printf("SQLSTATE after fetch is %s\n", SQLSTATE);
/*关闭游标*/
EXEC SQL close democursor;
/*释放游标占用的资源*/
EXEC SQL free democursor;
/*断开数据库服务器的连接*/
EXEC SQL disconnect current;
printf("\nDEMO1 Sample Program over.\n\n");
}
从上面这个例子，我们看出嵌入SQL的基本特点是：
1、每条嵌入式SQL语句都用EXEC SQL开始，表明它是一条SQL语句。这也是告诉预编译器在EXEC SQL和“；”之间是嵌入SQL语句。
2、如果一条嵌入式SQL语句占用多行，在C程序中可以用续行符“\”，在Fortran中必须有续行符。其他语言也有相应规定。
3、每一条嵌入SQL语句都有结束符号，如：在C中是“；”。
4、嵌入SQL语句的关键字不区分大小写。
5、可以使用“/*….*/”来添加注释。
从上面这个例子看出，INFORMIX数据库的嵌入SQL语句的格式同其他数据库基本相同。但是，它也有它自己本身的一些特点。本节把重点放在INFORMIX数据库所独有的一些语句或处理方式。
5.2 宿主变量
宿主变量就是在嵌入式SQL语句中引用主语言说明的程序变量。如：
EXEC SQL connect to :hostvar;
1）、定义宿主变量
方法1：采用BEGIN DECLARE SECTION 和END DECLARE SECTION之间给主变量说明。如：
EXEC SQL BEGIN DECLARE SECTION;
char fname[ FNAME_LEN + 1 ];
char lname[ LNAME_LEN + 1 ];
EXEC SQL END DECLARE SECTION;
方法2：在每个变量的数据类型前加上“$”。如：
$int hostint;
$double hostdbl;
ESQL/C对宿主变量的大小写敏感。但是，ESQL/C的关键字、语句标志符、游标名大小写不敏感。在SQL语句中，除了使用“:”来标志宿主变量外，还可以使用“$”。当然，“:”是ANSI标准。如：EXEC SQL connect to $hostvar。对于注释，可以使用“--”，也可以使用标准的“/*…*/”。
2）、宿主变量和NULL
方法1：使用指示符变量。
方法2：使用函数risnull()和rsetnull()。
3）、指示符变量
大多数程序设计语言（如C）都不支持NULL。所以对NULL的处理，一定要在SQL中完成。我们可以使用主机指示符变量来解决这个问题。在嵌入式SQL语句中，宿主变量和指示符变量共同规定一个单独的SQL类型值。指示变量和前面宿主变量之间用一个空格相分隔。如：
EXEC SQL select lname, company
into :name INDICATOR :nameind, :comp INDICATOR :compind
nameind是name变量的指示符，而compind是comp变量的指示符。
可以通过以下三种方法使用指示符变量：
方法1、使用INDICATOR关键字。
:hostvar INDICATOR :indvar
方法2、
:hostvar :indvar
方法3、使用$符号。
$hostvar $indvar。
无论采用哪种方法，都是实现指示符变量的作用。即：当宿主变量hostvar应该返回NULL时，指示符变量为-1。当宿主变量hostvar应该返回不是NULL而且无需截断时，指示符变量为0。当返回值太大而需要截断时，指示符变量是截断前数据的长度。SQLSTATE会返回01004错误信息。请看下面这个例子：
EXEC SQL BEGIN DECLARE SECTION;
char name[16];
char comp[20];
short nameind;
short compind;
EXEC SQL END DECLARE SECTION;
EXEC SQL select lname, company
into :name INDICATOR :nameind, :comp INDICATOR :compind
from customer
where customer_num = 105;
如果对应105的company为NULL，则compind小于0，如果lname的结果大于15个字节，那么name包含前15个字符。
4）、宿主变量的数据类型
INFROMIX ESQ/C的宿主变量数据类型除了标准C的数据类型外，可以是它自己定义的数据类型。如：
lvarchar数据类型
EXEC SQL BEGIN DECLARE SECTION;
varchar varc_name[n + 1];
EXEC SQL END DECLARE SECTION;
lint8数据类型
EXEC SQL BEGIN DECLARE SECTION;
int8 int8_var1;
ifx_int8_t int8_var2;
EXEC SQL BEGIN DECLARE SECTION;
lfixchar数据类型
EXEC SQL BEGIN DECLARE SECTION;
boolean flag;
fixchar my_boolflag;
int id;
EXEC SQL END DECLARE SECTION;
lDecimal数据类型
#define DECSIZE 16
struct decimal
{
short dec_exp;
short dec_pos;
short dec_ndgts;
char dec_dgts[DECSIZE];
};
typedef struct decimal dec_t;
lDatetime数据类型
EXEC SQL include datetime;
EXEC SQL BEGIN DECLARE SECTION;
datetime year; /* will cause an error */
datetime year to day year, today; /* ambiguous */
EXEC SQL END DECLARE SECTION;
lInterval hour等数据类型
EXEC SQL BEGIN DECLARE SECTION;
interval day(3) to day accrued_leave, leave_taken;
interval hour to second race_length;
interval scheduled;
EXEC SQL END DECLARE SECTION;
l其他数据类型
EXEC SQL BEGIN DECLARE SECTION;
loc_t my_simple_lo;
EXEC SQL END DECLARE SECTION;
my_simole_lo.loc_loctype = LOCMEMORY;
l在INFORMIX数据库中，'\0'表示为NULL。如：
id = 1;
flag = '\0'; /* valid boolean assignment to FALSE */
EXEC SQL insert into table2 values (:id, :flag); /* inserts FALSE */
在以SQL为基础的DBMS支持的数据类型与程序设计语言支持的数据类型之间有很大差别。如果你通过宿主变量从数据库取值，或者通过宿主变量向数据库插入值，都存在数据类型转换的问题。
下表列出了C的数据类型、ESQL/C定义的数据类型和SQL数据类型的兼容关系：
SQL数据类型 ESQL/C定义的数据类型 C的数据类型
BOOLEAN boolean
BYTE loc_t
CHAR(n)
CHARACTER(n) fixchar [n] 或string [n+1] char [n + 1] 或 char *
DATE date 4-byte integer
DATETIME datetime或ordtime_t
DECIMAL decimal或dec_t
DEC decimal或dec_t
NUMERIC decimal或dec_t
MONEY decimal或dec_t
FLOAT double
DOUBLE double
PRECISION double
INT8 int8或ifx_int8_t
INTEGER 4-byte integer
INT 4-byte integer
INTERVAL interval or intrvl_t
LVARCHAR lvarchar char [n + 1] orchar *
NCHAR(n) fixchar [n] orstring [n+1] char [n + 1] orchar *
NVARCHAR(m) varchar[m+1] orstring [m+1] char [m+1]
SERIAL 4-byte integer
SERIAL8 int8 or ifx_int8_t
SMALLFLOAT float
REAL float
SMALLINT 2-byte integer
TEXT loc_t
VARCHAR(m,x) varchar[m+1] or string [m+1] char d[m+1]
BLOB ifx_lo_t
CLOB ifx_lo_t
LIST(e) collection
MULTISET(e) collection
Opaque data type lvarchar,fixed binary或var binary
ROW(...) row
SET(e) collection
下表是INFORMIX数据库服务器支持的数据类型和类型代码：
SQL数据类型 类型代码 类型代码值
CHAR SQLCHAR 0
SMALLINT SQLSMINT 1
INTEGER SQLINT 2
FLOAT SQLFLOAT 3
SMALLFLOAT SQLSMFLOAT 4
DECIMAL SQLDECIMAL 5
SERIAL SQLSERIAL 6
DATE SQLDATE 7
MONEY SQLMONEY 8
DATETIME SQLDTIME 10
BYTE SQLBYTES 11
TEXT SQLTEXT 12
VARCHAR SQLVCHAR 13
INTERVAL SQLINTERVAL 14
NCHAR SQLNCHAR 15
NVARCHAR SQLNVCHAR 16
INT8 SQLINT8 17
SERIAL8 SQLSERIAL8 18
LVARCHAR SQLLVARCHAR 43
BOOLEAN SQLBOOL 45
SET SQLSET 19
MULTISET SQLMULTISET 20
LIST SQLLIST 21
ROW SQLROW 22
Varying-length
opaqueType SQLUDTVAR 40
Fixed-length
opaque type SQLUDTFIXED 41
SENDRECV
(client-side only) SQLSENDRECV 44
下表是ESQL/C定义的数据类型和类型代码，这些定义存放在各个头文件中。
ESQL/C数据类型 类型代码 类型代码值
char CCHARTYPE 100
short int CSHORTTYPE 101
int4 CINTTYPE 102
long CLONGTYPE 103
float CFLOATTYPE 104
double CDOUBLETYPE 105
dec_t或decimal CDECIMALTYPE 107
fixchar CFIXCHARTYPE 108
string CSTRINGTYPE 109
date CDATETYPE 110
dec_t或decimal CMONEYTYPE 111
datetime或dtime_t CDTIMETYPE 112
loc_t CLOCATORTYPE 113
varchar CVCHARTYPE 114
intrvl_t 或interval CINVTYPE 115
char CFILETYPE 116
int8 CINT8TYPE 117
collection (Universal
Data Option) CCOLTYPE 118
lvarchar CLVCHARTYPE 119
fixed binary CFIXBINTYPE 120
var binary (Universal
Data Option) CVARBINTYPE 121
boolean CBOOLTYPE 122
row (Universal Data
Option) CROWTYPE 123
INFORMIX的ESQL/C提供了很多函数来处理数据类型，这些函数的参数就是ESQL/C定义的数据类型。如：dectoasc()的作用是转换数据类型是decimal的值为ASCII。
| | |
