oracle12 proc,oracle proc 详解

Proc也就是嵌入式C，与informix的ESQ/C有类似之处，本部分主要列出Proc与Esql的区别，相同部分请参见informix部分。

1.数组功能

Proc中支持使用宿主变量数组一次查询SELECT/插入INSERT多条记录。在某些情况下非常有用，但不具有移植性。

2.sqlca通讯区结构

struct   sqlca

{

char    sqlcaid[8];

long    sqlabc;

long    sqlcode;

struct

{

unsigned short sqlerrml;

char        sqlerrmc[70];

} sqlerrm;

char    sqlerrp[8];

long    sqlerrd[6];

char    sqlwarn[8];

char    sqlext[8];

};

当MODE={ANSI13|ORACLE}时，要求说明SQLCA，不说明将导致编译或连接错误。当MODE={ANSI|ANSI14}时，SQLCA的说明是可选的，但必须说明一个SQLCODE状态变量。

说明形式：

EXEC SQL INCLUDEsqlca；

当Proc源程序由多个文件组成时，sqlca说明在一个文件里，其他文件使用如下引用方式：

#defineSQLCA_STORAGE_CLASS extern

可通过定义符号SQLCA_INIT来初始化SQLCA。

字段说明：

sqlcaid

初始化为sqlca，用于标示一个SQL通讯区。

sqlabc

保存SQL通讯区的长度，以字节为单位。

sqlcode

最近执行的SQL语句的状态。有以下值：

·0：正确执行，没有错误和例外；

·>0：执行了语句，但出现了一个例外。如没有行找到；

·<0：未执行语句，出现错误。

在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是错误信息的实际长度。

ORACLE错误信息的最大长度是512字节。

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.登录数据库

(1).登录到当前节点的缺省数据库

有如下三种形式：

形式1：

EXEC SQL CONNECT:username IDENTIFIED BY :password;

其中，username和password是CHAR或VARCHAR类型的宿主变量，分别包含用户名和密码。这里必须使用宿主变量，不能直接使用字符串。

形式二：

EXEC SQL CONNECT:user_pwd;

其中宿主变量user_pwd包含由字符’/’分隔的用户名和密码，即(username/password)。

形式三(自动登录)：

char oracleid =‘/’; EXEC SQL CONNECT :oracleid;

使用用户名OPS$username登录，其中username是当前登录到操作系统上的用户名或任务名。

(2).登录到远程数据库

[ EXEC SQL DECLAREdb_link_name DATABASE; ] EXEC SQL CONNECT :username IDENTIFIED BY :password [AT db_link_name ] USING :db_name;

其中，db_name包含远程数据库名(对于oracle8i，就是在配置文件tnsnames.ora中定义的名称)。

可以为该连接定义一个连接名，db_link_name可以是一个标示符，或者是宿主变量。若为标示符，则需要使用DECLARE DATABAE进行说明。

若使用了AT子句，则在后面的SQL操作中也要指定该连接，否则操作的是缺省连接上的数据，而不是AT指定的连接。如：

EXEC SQL ATdb_link_name SELECT…; EXEC SQL AT db_link_name INSERT…; EXEC SQL ATdb_link_name UPDATE…; EXEC SQL AT db_link_name DECLARE cursor_name CURSOR…;

不能在PREPARE、DESCRIBE、OPEN、FETCH、CLOSE中使用AT子句。

可以使用多条EXEC SQL CONNECT AT语句登录到多个数据库上，或为同一个数据库建立多个连接。

(3).使用数据库链

EXEC SQL CREATEDATABASE LINK link_name CONNECT TO username IDENTIFIED BY password USINGdb_name;

之后操作数据库对象时要指定链名：

EXEC SQL SELECT *FROM emp@link_name;

4.滚动游标

Oracle中不支持滚动游标。

5.事务处理

Oracle中没有BEGIN WORK之类的事务开始语句，程序中的第一个SQL语句(非CONNECT语句)自动开始一个事务。

事务的结束有如下情况：

·调用COMMIT和ROLLBAK；

·数据定义语句(ALTER、CREATE、GRANT等)在执行前和执行后都自动执行一个COMMIT语句；

·当系统出现故障或发生意外时，ORACLE会回滚事务。

(1).事务提交

EXEC SQL [ AT:link_name ] COMMIT WORK [ RELEASE ];

完成如下功能：

·使当前事务对数据库所作的改变永久化；

·使这些改变对其他用户可见；

·取消所有的保留点；

·释放所有的行或表锁，但不分析锁；

·关闭CURRENT OF子句中引用的游标，当MODE = {ANSI |ANSI14 }时，关闭所有显示游标；

·结束事务。

使用RELEASE除了完成上述功能外，还释放所有资源并切断与ORACLE的连接。

程序中必须用COMMIT/ROLLBACK RELEASE来显示结束最后一个事务并断开连接。

(2).事务保留点

事务保留点是事务内的标志点，建立方法如下：

EXEC SQL [ ATlink_name ] SAVEPOINT savepoint_name;

保留点用于部分回滚。

如果给两个保留点设置相同的名字，则前一个保留点被取消。每个用户会话期的活动保留点最大数为5(缺省值)。该值可修改配置文件init.ora中的SAVEPOINTS来改变，最大255。

(3).事务回滚

事务级回滚：

EXEC SQL [ ATlink_name ] ROLLBACK WORK [ RELEASE ];

完成如下功能：

·取消当前事务对数据库的全部改变；

·取消当前事务中的全部保留点；

·释放所有的行或表锁，但不分析锁；

·关闭CURRENT OF子句中引用的游标，当MODE = {ANSI |ANSI14 }时，关闭所有显示游标；

·结束该事务。

部分事务回滚：

EXEC SQL [ ATlink_name ] ROLLBACK WORK TO \ SAVEPOINT savepoint_name;

完成如下功能：

·取消指定保留点以来的全部改变；

·取消指定保留点之后的全部保留点，但回滚到的保留点不取消；

·取消指定保留点以来所获得的全部行或表锁；

·语句级回滚：

如果执行一条SQL语句失败，ORACLE会自动将其回滚，并在sqlca.sqlcode中返回一个错误码。

(4).只读事务

6.sqlda结构

结构构成：

struct SQLDA { intN; char **V; int *L; short *T; short **I; int F; char **S; short *M; short *C;char **X; short *Y; short *Z; };

(1).N变量

指定要描述的选择表项或虚拟输入宿主变量的最大个数。N确定了描述区中数组的元素个数。在发出DESCRIBE命令之前，必须用库函数sqlald()把N设置为描述区中数组的维数。在DESCRIBE之后，必须再把N设置为所描述的选择表项或虚拟输入宿主变量的实际个数，该数据被存储在F变量中。

(2).V变量

V是一个数组指针，其元素分别指向缓冲区中存储的每一个选择表项或实输入宿主变量值。

当对该描述区进行分配时，函数sqlald()把数组元素V[0]~V[N-1]设置为0。

对于选择描述区，必须在发FETCH命令之前分配数据缓冲区和设置该数组。

对于结合缓冲区，必须在发OPEN命令之前设置该数组。

(3).L变量

L是一个指针，它指向一个数组，该数组包含存储在数据缓冲区中的选择表项或实输入宿主变量的长度。

对于选择描述区，DESCRIBE SELECT LIST语句把长度数组设置为每一个选择表项所期望的最大数。

在FETCH之前必须把L[i]重新设置为所需要的数据缓冲区的长度。

对于结合缓冲区，在发OPEN命令之前，必须设置长度数组。

(4).T变量

T是一个指针，它指向一个数组，该数组存放选择表项或实输入宿主变量的数据类型代码。

对于选择描述区，DESCRIBE SELECT LIST语句把数据类型代码数组设置为选择表项的内部数据类型(如CHAR、NUMBER或DATE)。

因为ORACLE数据类型的内部格式很难处理，所以在FETCH之前，可以设置某些数据类型。

T[i]的高位指出第i个选择表项的NULL/NOT NULL状态。在发OPEN或FETCH之前，一定要清除该位。用库函数sqlnull()来检索数据类型代码和清除NULL/NOT NULL位。

对于结合描述区，DESCRIBE BIND VARIABLES把数据类型代码的数组设置为0。在发OPEN之前，必须重新设置存储在每一个元素的数据类型代码。该代码表示V[i]所指向的数据缓冲区的外部(C)数据类型。

(5).I变量

I是一个数组指针，其元素分别指向数据缓冲区中每一个指示器变量的值。必须设置元素I[0]~I[N-1]。

对于选择缓冲区，必须在发FETCH之前设置该地址数组。当ORACLE执行FETCH之后，如果第i个返回的选择表项值为NULL，那么I[i]所指向的那个指示器变量的值被置为-1，否则，它被置为0或一个正整数(值被截短)。

对于结合描述区，必须在发OPEN之前设置地址数组和相关的数据缓冲区。当ORACLE执行OPEN时，I[i]所指向的那个指示器变量的值决定第i个实输入宿主变量是否是一个NULL值。如果指示器变量的值为-1，则其相关实输入宿主变量的值为NULL。

(6).F变量

F是DESCRIBE所发现的选择表项或虚拟输入宿主变量的实际个数。因此，F是由DESCRIBE来设置。如果F<0，则表示DESCRIBE发现了过多的选择表项或虚拟输入宿主变量(相对于N变量)，F的绝对值就是实际个数。

(7).S变量

S是一个数组指针，其元素分别指向数据缓冲区中所存储的每一个选择表项或虚拟输入宿主变量的名字。

使用sqlald()来分配该数据缓冲区，并把其地址存放在S数组中。

(8).M变量

M是一个指针，它指向一个数组，该数组存放缓冲区中所包含的选择表项或虚拟输入宿主变量名字最大长度。这些缓冲区由S数组元素寻址。

当分配一个描述区时，sqlald()设置该数组元素M[0]~M[N-1]。当对S[i]指向的数据缓冲区存储时，第i个名字被截成M[i]中的长度(如果有必要的话)。

(9).C变量

C指向一个数组，该数组元素包含选择表项或虚拟输入宿主变量名字的当前长度。

DESCRIBE设置C[0]~C[N-1]。

(10).X变量

X是一个数组指针，其元素分别指向数据缓冲区中所存储的每一个指示器变量的名字。只能把指示器变量的名字与实输入宿主变量联系起来，因此，X只适用于结合缓冲区。

用sqlald()来分配数据缓冲区并把它们的地址存储在X数组中。

DESCRIBE BIND VARIABLES指示ORALCE把第i个指示器变量的名字存储在X[i]所指向的数据缓冲区中。

(11).Y变量

Y指向一个数组，该数组的元素分别包含每一个指示器变量名字的最大长度。Y也只适用于结合缓冲区。

(12).Z变量

Z指向一个数组，该数组的元素分别包含当前每一个指示器变量名字的最大长度。Z也只适用于结合缓冲区。

转载出处：http://blog.sina.com.cn/s/blog_4c705ccb0100anl0.html