ORACLE PROC 常用知识
1.数组功能
Proc中支持使用宿主变量数组一次查询SELECT/插入INSERT多条记录。在某些情况下非常有用，但不具有移植性。2.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];
};
当Proc源程序由多个文件组成时，sqlca说明在一个文件里，其他文件使用如下引用方式：
#define SQLCA_STORAGE_CLASS extern
sqlcaid
初始化为sqlca，用于标示一个SQL通讯区。
sqlabc
保存SQL通讯区的长度，以字节为单位。
sqlcode
最近执行的SQL语句的状态。有以下值：在Oracle中，“记录没有找到”的错误码有两个：在ANSI模式下是100，在Oracle模式下是1403。
sqlerrm.sqlerrml
保存sqlerrm.sqlerrmc中信息文本的长度。
sqlerrm.sqlerrmc
保存与sqlcode相对应的错误信息文本，最长不超过70个字符。要获得超过70个字符的完整信息，需要使用 sqlglm()函数。sqlglm函数说明如下：
sqlglm( message_buff, &buffer_size, &message_length );
其中，message_buff是文本缓冲区；
buffer_size存放缓冲区的长度，以字节为单位；
message_length是错误信息的实际长度。
sqlerrp
保留，未使用。
sqlerrd
sqlerrd[0]：保留；sqlerrd[1]：保留；
sqlerrd[2]：保存当前sql语句处理的行数。当语句处理失败时，该值无定义，当处理过程中出错时，则该值为成功处理的记录数；
sqlerrd[3]：保留；
sqlerrd[4]：保存相对位移，指出在哪个字符位置开始出现（语法）分析错误。第一个字符的相对位移是0；
sqlerrd[5]：保留。
sqlwarn
有8个元素，当有警告发生时，相应元素被设置位’W’。sqlwarn[0]：如果其他标志被设置，则其也被设置。若其为空，则表示其他7个全部没有设置；
sqlwarn[1]：如果字符数据输出到宿主变量时被截断，则设置该标志，对于数字数据被截断并不设置该标志。为了检查具体的截断情况，可查看宿主变量对应的指示变量，指示变量的值是列的原始长度。
sqlwarn[2]：未使用；
sqlwarn[3]：如果查询选择的列数不等于宿主变量的个数，则设置该标志；
sqlwarn[4]：如果DELETE和UPDATE语句没有WHERE子句，则设置该标志；
sqlwarn[5]：当执行CREATE语句由于PL/SQL编译错误失败时，设置该标志；
sqlwarn[6]：未使用；
sqlwarn[7]：未使用。
sqlext
保留。3.登录数据库
登录到当前节点的缺省数据库
有如下三种形式：形式二：
EXEC SQL CONNECT :user_pwd;
其中宿主变量user_pwd包含由字符’/’分隔的用户名和密码，即（username/password）。
登录到远程数据库
[ EXEC SQL DECLARE db_link_name DATABASE; ]EXEC SQL CONNECT :username IDENTIFIED BY :password
[ AT db_link_name ] USING :db_name;
不能在PREPARE、DESCRIBE、OPEN、FETCH、CLOSE中使用AT子句。
可以使用多条EXEC SQL CONNECT AT语句登录到多个数据库上，或为同一个数据库建立多个连接。
使用数据库链
EXEC SQL CREATE DATABASE LINK link_nameCONNECT TO username IDENTIFIED BY password
USING db_name;
之后操作数据库对象时要指定链名：
EXEC SQL SELECT * FROM emp@link_name;
4.滚动游标
Oracle中不支持滚动游标。5.事务处理
Oracle中没有BEGIN WORK之类的事务开始语句，程序中的第一个SQL语句（非CONNECT语句）自动开始一个事务。
事务提交
EXEC SQL [ AT :link_name ] COMMIT WORK [ RELEASE ];- 使当前事务对数据库所作的改变永久化；
- 使这些改变对其他用户可见；
- 取消所有的保留点；
- 释放所有的行或表锁，但不分析锁；
- 关闭CURRENT OF子句中引用的游标，当MODE = {ANSI | ANSI14 }时，关闭所有显示游标；
- 结束事务。
程序中必须用COMMIT/ROLLBACK RELEASE来显示结束最后一个事务并断开连接。
事务保留点
事务保留点是事务内的标志点，建立方法如下：EXEC SQL [ AT link_name ] SAVEPOINT savepoint_name;
如果给两个保留点设置相同的名字，则前一个保留点被取消。每个用户会话期的活动保留点最大数为5（缺省值）。该值可修改配置文件init.ora中的SAVEPOINTS来改变，最大255。
事务回滚
事务级回滚：EXEC SQL [ AT link_name ] ROLLBACK WORK [ RELEASE ];
- 取消当前事务对数据库的全部改变；
- 取消当前事务中的全部保留点；
- 释放所有的行或表锁，但不分析锁；
- 关闭CURRENT OF子句中引用的游标，当MODE = {ANSI | ANSI14 }时，关闭所有显示游标；
- 结束该事务。
EXEC SQL [ AT link_name ] ROLLBACK WORK TO SAVEPOINT savepoint_name;
如果执行一条SQL语句失败，ORACLE会自动将其回滚，并在sqlca.sqlcode中返回一个错误码。
只读事务
6.sqlda结构
结构构成
int N; /* Descriptor size in number of entries */
char **V; /* Ptr to Arr of addresses of main variables*/
int *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 */
int 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 */
};
N变量
指定要描述的选择表项或虚拟输入宿主变量的最大个数。N确定了描述区中数组的元素个数。在发出DESCRIBE命令 之前，必须用库函数sqlald()把N设置为描述区中数组的维数。在DESCRIBE之后，必须再把N设置为所描述的选择表项或虚拟输入宿主变量的实际 个数，该数据被存储在F变量中。
V变量
V是一个数组指针，其元素分别指向缓冲区中存储的每一个选择表项或实输入宿主变量值。当对该描述区进行分配时，函数sqlald()把数组元素V[0]~V[N-1]设置为0。
对于选择描述区，必须在发FETCH命令之前分配数据缓冲区和设置该数组。
L变量
L是一个指针，它指向一个数组，该数组包含存储在数据缓冲区中的选择表项或实输入宿主变量的长度。对于选择描述区，DESCRIBE SELECT LIST语句把长度数组设置为每一个选择表项所期望的最大数。
在FETCH之前必须把L[i]重新设置为所需要的数据缓冲区的长度。
T变量
T是一个指针，它指向一个数组，该数组存放选择表项或实输入宿主变量的数据类型代码。对于选择描述区，DESCRIBE SELECT LIST语句把数据类型代码数组设置为选择表项的内部数据类型（如CHAR、NUMBER或DATE）。
因为ORACLE数据类型的内部格式很难处理，所以在FETCH之前，可以设置某些数据类型。
T[i]的高位指出第i个选择表项的NULL/NOT NULL状态。在发OPEN或FETCH之前，一定要清除该位。用库函数sqlnull()来检索数据类型代码和清除NULL/NOT NULL位。
I变量
I是一个数组指针，其元素分别指向数据缓冲区中每一个指示器变量的值。必须设置元素I[0]~I[N-1]。
F变量
F是DESCRIBE所发现的选择表项或虚拟输入宿主变量的实际个数。因此，F是由DESCRIBE来设置。如果 F<0，则表示DESCRIBE发现了过多的选择表项或虚拟输入宿主变量（相对于N变量），F的绝对值就是实际个数。
S变量
S是一个数组指针，其元素分别指向数据缓冲区中所存储的每一个选择表项或虚拟输入宿主变量的名字。使用sqlald()来分配该数据缓冲区，并把其地址存放在S数组中。
M变量
M是一个指针，它指向一个数组，该数组存放缓冲区中所包含的选择表项或虚拟输入宿主变量名字最大长度。这些缓冲区由 S数组元素寻址。当分配一个描述区时，sqlald()设置该数组元素M[0]~M[N-1]。当对S[i]指向的数据缓冲区存储时，第i个名字被截成M[i]中的长度（如果有必要的话）。
C变量
C指向一个数组，该数组元素包含选择表项或虚拟输入宿主变量名字的当前长度。
X变量
X是一个数组指针，其元素分别指向数据缓冲区中所存储的每一个指示器变量的名字。只能把指示器变量的名字与实输入宿主变量联系起来，因此，X只适用于结合缓冲区。用sqlald()来分配数据缓冲区并把它们的地址存储在X数组中。
DESCRIBE BIND VARIABLES指示ORALCE把第i个指示器变量的名字存储在X[i]所指向的数据缓冲区中。
Y变量
Y指向一个数组，该数组的元素分别包含每一个指示器变量名字的最大长度。Y也只适用于结合缓冲区。
Z变量
Z指向一个数组，该数组的元素分别包含当前每一个指示器变量名字的最大长度。Z也只适用于结合缓冲区。7.sqlda动态SQL预备知识
sqlda的引用
sqlda *bind_dp;
bind_dp = sqlald( … );
sqlald函数
sqlald函数为sqlda结构分配空间。函数格式如下：descriptor_name = sqlald(max_vars, max_name, max_ind_name );
数据类型转换
对于选择描述区，DESCRIBE SELECT LIST返回ORACLE的内部数据类型。通常，该内部数据类型正好对应所要用的外部数据类型；但是，个别的映射难以处理，因此，可以重新设置T变量的某 些元素。ORACLE在FETCH时做必要的内部和外部数据类型的转换。除了设置T变量外，有些情况下，还要设置缓冲区长度。在FETCH之前必须重新设置L变量的相应元素，以告诉ORACLE所用的缓冲区长度。
处理Null/Not Null类型
对于每一个选择表列（不是表达式），DESCRIBE SELECT LIST在T变量中返回一个Null/Not Null指示。如果第i个选择表项被强制为Not Null，那么T[i]的高位被清除，否则被设置。8.sqlda动态SQL基本处理步骤
说明一个串型宿主变量
在说明段说明一个串型的宿主变量，以保存查询语句的文本。EXEC SQL BEGIN DECLARE SECTION
VARCHAR select_stmt[120];
EXEC SQL END DECLARE SECTION
说明SQLDA
说明选择和结合SQLDA。EXEC SQL INCLUDE sqlda;
sqlda *select_des;
sqlda *bind_des;
分配空间
分配选择和结合缓冲区的空间。如果sqlald()成功，则返回该描述区的地址，失败则返回空指针。
select_des = sqlald(3, 5, 0);
bind_des = sqlald(3, 5, 4);
设置DESCRIBE的最大个数
设置能被DESCRIBE的选择表项和虚拟输入宿主变量的最大个数。select_des->N = 3;
bind_des->N = 3;
把查询文本存放在宿主串中
把查询语句文本存放在串型宿主变量中。strcpy(select_stmt.arr, “select … where …” );
select_stmt.len = strlen(select_stmt.arr);
分析查询语句
PREPARE宿主串中的查询语句。EXEC SQL PREPARE sql_stmt FROM :select_stmt；
说明一个光标
DECLARE一个查询光标。EXEC SQL DEACLARE emp_cursor CURSOR FOR sql_stmt;
注意：对于此种方法的动态sql语句（不只是查询语句），都必须说明一个光标，在非查询语句情况下，打开光标即执行该动态sql语句。
DESCRIBE实输入宿主变量
把实输入宿主变量DESCRIBE进结合描述区中。EXEC SQL DESCRIBE BIND VARIABLES FOR sql_stmt INTO bind_des;
重新设置虚拟输入宿主变量的个数
把虚拟输入宿主变量的个数重新设置为DESCRIBE实际发现的个数。
为实输入宿主变量分配空间和取值
为DESCRIBE发现的实输入宿主变量取值和分配存储空间。bind_des->V[i] = malloc(bind_des->L[i] + 1);
bind_des->I[i] = (unsigned short *)malloc(sizeof(short));
strcpy(bind_des->V[i], hostval);
bind_des->I[i] = 0;
bind_des->T[i] = 1;
打开光标
用OPEN…USING打开与结合描述区相对应的光标。EXEC SQL OPEN emp_cursor USING DESCRIPTOR bind_des;
描述选择表
用DESCRIBE SELECT LIST语句描述选择描述区。EXEC SQL DESCRIBE SELECT LIST FOR sql_stmt INTO select_des;
注意：DESCRIBE把F设置为该查询选择表项的实际个数，如果语句不是查询，则被设置为0。
此时NUMBER的长度仍然不能用，还需要用sqlprc()来分离精度和定标。
重新设置选择表项的最大数
把选择表项的个数重新设置为DESCRIBE实际发现的个数。select_stmt->N = select_stmt->F;
重新设置选择表项的长度和数据类型
重新设置选择表项的长度和数据类型，以便显示它们。switch( select_stmt->T[i]) {
case 1: break;
case 2: sqlprc(&select_stmt->L[i], &prec, &scal);
if ( prec == 0 ) prec = 40;
select_des->L[i] = prec + 2;
if (scal < 0) select_des->L[i] += -scal;
break;
case 8: select_des->L[i] = 240;
break;
case 11:select_des->L[i] = 18;
break;
case 12: select_des->L[i] = 9;
break;
case 23: break;
case 24: select_des->L[i] = 240;
break;
}
select_stmt->V[i] = malloc(select_stmt->L[i]);
select_stmt->I[i] = (unsigned short *)malloc(sizeof(short));
if (select_stmt->T[i] != 24) select_stmt->T[i] = 1;
从光标缓冲区中提取行
用FETCH把提取的行放入选择描述区所指向的数据缓冲区。EXEC SQL FETCH emp_cursor USING DESCRIPTOR select_des;
在输出缓冲区中，对于数据类型1，ORACLE使用存放在L数组中的长度，左对齐CHAR和 VARCHAR2数据，右对齐NUMBER数据。
取和处理选择表项的值
处理FETCH所返回的选择表项。
撤销存储分配
释放选择表项、虚拟输入宿主变量、指示器变量和描述区所用的空间。使用sqlclu()库函数撤销描述区本身的存储空间。
sqlclu(select_des);
sqlclu(bind_des);
关闭光标
关闭光标。9.关于游标的释放
在一个连接中，若该连接引用记数为1，则关闭该连接也释放游标；如在同一个进程中打开连接，操作数据，关闭连接。否则不能释放，最终会报-1000错：maximum open cursors exceeded。
10.Pro*C命令选项
Pro*C的命令格式如下：proc INAME=filename [Option_name1=value1 Option_name2=value2 … ]
在程序中指定选项的格式如下：
EXEC SQL OPTION(option_name=value);
ASACC
ASACC=YES|NO；缺省值为NO。指出清单文件是否为了回车控制而遵循使用每一行第一列的ASA约定。
CODE
CODE=ANSI_C|KR_C
DBMS
DBMS=NATIVE|V6|V7缺省值为NATIVE。指定所用的ORACLE数据库管理系统的版本，是6版、7版或本国版。
DEFINE
用于定义一个符号，用于进行条件编译。如果在程序中输入时，使用如下语法：
EXEC ORACLE DEFINE symbol;
ERRORS
ERRORS=YES|NO;缺省值为YES。指出预编译程序的错误信息是否被发送到终端和清单文件上，或仅仅给清单文件。
FIPS
FIPS=YES|NO;缺省值为NO。当FIPS=YES时，如果使用一个ANSI SQL的ORACLE扩展，或以不一致的方式使用一个ANSI SQL性能，就发出警告（不是错误）信息。
HOLD_CURSOR
HOLD_CURSOR=YES|NO;当执行SQL数据操纵语句时，其相关的光标被连到光标高速缓冲存储器中的一项上，该项又被依次连接到 ORACLE专用的SQL区域上，该区域存储处理该语句所需的信息。
当HOLD_CURSOR=YES时，该链被保留，预编译程序不再使用它。这对经常使用的SQL语句是有用的。
注意：RELEASE_CURSOR=YES优先于 HOLD_CURSOR=YES；HOLD_CURSOR=NO优先于RELEASE_CURSOR=NO。
HOST
HOST=C|COB74|COBOL|FORTRAN|PASCAL|PLI;
INCLUDE
INCLUDE=path；缺省值为当前目录。指定EXEX SQL INCLUDE文件的目录路径，它只适用于使用目录的操作系统。
IRECLEN
IRECLEN=length;注意：只能在命令行输入，指定的值不应超过ORECLEN的值。
LINES
LINES=YES|NO;缺省值为NO。当LINES=YES时，预编译程序对输出文件加C预编译程序命令#line。为NO时不输出。
LNAME
LNAME=path and filename;
LRECLEN
LRECLEN=integer;
LTYPE
LTYPE=LONG|SHORT|NONE;指定清单类型。当LTYPE=LONG时，清单文件中包含输入行；当LTYPE=SHORT时，不包含输入行；当LTYPE=NONE时，不建立清单文件。
MAXLITERAL
MAXLITERAL=integer;缺省值为1000（C语言）。指出预编译程序生成的串文字的最大长度。
MAXOPENCURSORS
MAXOPENCURSORS=integer;缺省值为10。指定同时打开的光标数。每个用户处理能打开的光标的最大数由ORACLE初始化参数 OPEN_CURSORS设置，其范围为5至255。该选项可指定一个较初始值小的值，必须比初始值至少低6。
MODE
MODE=ANSI|ISO|ANSI14|ISO14|ANSI13|ISO13|ORACLE;
ONAME
ONAME=path and filename;
ORACA
ORACA=YES|NO;
ORECLEN
ORECLEN=integer;
PAGELEN
PAGELEN=integer;
RELEASE_CURSOR
RELEASE_CURSOR=YES|NO;该选项用来控制光标和光标高速缓冲存储器之间的链。为了保证在关闭光标时使有关的资源被释放，必须指定：
RELEASE_CURSOR=YES。
SELECT_ERROR
SELECT_ERROR=YES|NO。
SQLCHECK
SQLCHECK=SEMANTICS|FULL|SYNTAX|LIMITED|NONE;
USERID
USERID=username/password;
XREF
XREF=YES|NO;转自：http://blog.163.com/zhhzhh-43/blog/static/126697371201054103414784/
