介绍PROC编程的一些经验

发布时间：2013-07-18 17:43:28 浏览次数：188

摘要：介绍PROC编程的一些经验-elivans-ChinaUnix博客。

介绍PROC编程的一些经验-elivans-ChinaUnix博客

PROC是ORACLE数据库提供的编程接口之一，其应用十分的广泛，本文通过一个具体的例子，介绍PROC编程的一些经验及应注意的地方。

例子程序：

#include

EXEC SQL INCLUDE sqlca;

/*RELEASE_CURSOR=YES 使PROC 在执行完后释放与嵌入SQL有关资源*/ EXEC ORACLE OPTION (RELEASE_CURSOR = YES);

EXEC SQL BEGIN DECLARE SECTION;

varchar vc_user[20];

long al_empno=0;

char ac_ename[11]="";

char ac_hiredate[20]="";

double af_sal=0;

EXEC SQL VAR ac_ename IS STRING(11);

EXEC SQL VAR ac_hiredate IS STRING(20);

EXEC SQL END DECLARE SECTION;

/*错误处理函数*/

void sql_error(char *msg)

{

printf(" %s,%ld,%s ", msg,sqlca.sqlcode,(char *)sqlca.sqlerrm.sqlerrmc);

EXEC SQL ROLLBACK RELEASE;

exit(-1);

}

main()

{

EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE ERROR: ");

/*连接数据库*/

strcpy(vc_user.arr,"scott/tiger@DEMO");

vc_user.len=16;

exec sql connect :vc_user;

EXEC SQL DECLARE cur_emp CURSOR FOR

SELECT EMPNO, ENAME,to_char(HIREDATE,'yyyy/mm/dd

hh24:mi:ss'),SAL FROM EMP;

EXEC SQL OPEN cur_emp;

while(1)

{

al_empno=0;

strcpy(ac_ename,"");

strcpy(ac_hiredate,"");

af_sal=0;

EXEC SQL FETCH cur_emp

INTO :al_empno, :ac_ename:ename_ind, :ac_hiredate:hiredate_ind, :af_sal: sal_ind;

if( sqlca.sqlcode == 1403)

{

break;

}

printf("empno=%ld,ename=%s,hiredate=%s,sal=%lf

",al_empno,ac_ename,ac_hiredate,af_sal);

}

EXEC SQL CLOSE cur_emp;

EXEC SQL ROLLBACK WORK RELEASE;

}

1、宿主变量的声明

在PROC中,在SQL语句中用到的变量称为宿主变量。他们应在EXEC SQL BEGIN DECLARE SECTION;与EXEC SQL EDN DECLARE SECTION;

之间声明，如上面所示.在声明宿主变量时应注意以下几点：

(1) 在数据库表中定义为VARCHAR2，VARCHAR，CHAR的字段，在PROC中可声明为CHAR，但长度应为它们在表中定义的长度加1，因为PROC中

CHAR型变量用做结尾。

如：ENAME在表中的定义为ename varchar2(10)，在PROC中可定义为：

EXEC SQL BEGIN DECLARE SECTION;

char ename[11];

EXEC SQL END DECLARE SECTION;

常见错误说明:

如果插入的字符串长度大于10，如：EXEC SQL INSERT INTO EMP(ENAME) VALUES('12345678901');会出现以下错误:

error:ORA-01480: STR 赋值变量缺少空后缀。

如果定义为:

EXEC SQL BEGIN DECLARE SECTION;

char ename[15];

EXEC SQL END DECLARE SECTION;

当插入的字符串长度大于10，小于15时,如：EXEC SQL INSERT INTO

EMP(ENAME) VALUES('123

[NextPage][/NextPage]

45678901');会出现以下错误:

error:ORA-01401: 插入的值对于列过大。

当插入的字符串长度大于15，如：EXEC SQL INSERT INTO EMP(ENAME) VALUES('12345678901234');会出现以下错误:

error:ORA-01401:STR 赋值变量缺少空后缀。

(2) 从SQL语句中取字段的值到宿主变量中时，PROC不会自动给宿主变量去掉右空格。而是以在DECLARE SECTION 中定义的长度为准(与表中定义的无关)不足补右空格.如果不注意这一点，在PROC中进行字符串操作时（如比较相等）会出错。如: EXEC SQL BEGIN DECLARE SECTION;

char ename[10];

EXEC SQL END DECLARE SECTION;

如果ENAME在表中的值为'abc',则取出的值为'abc ';

可用语句EXEC SQL VAR重定义CHAR型变量。这样宿主变量会自动去掉右空格。如下:

EXEC SQL BEGIN DECLARE SECTION;

char ename[11];

EXEC SQL VAR ac_ename IS STRING(11);

EXEC SQL END DECLARE SECTION;

如果ENAME在表中的值为'abc',则取出的值为'abc';

(3) 对浮点型的变量，为保证精度，最好是声明成DOUBLE型的.因为DOUBLE型的精度比FLOAT型高很多.

(4) 整型可声明为LONG型(对较长的整型,而且所用的平台支持的话,如在SUN平台上,可声明为LONG LONG型).

(5) DATE型的处理：DATE型一般声明为CHAR(20)。

往表中插入DATE型数据时,一般用TO_DATE()函数进行类型转换,取出值时一般用TO_CHAR()函数进行类型转换.

EXEC SQL select to_char(hiredate,'yyyy/mm/dd hh24:mi:ss')

into :ac_hire_date from EMP where empno=1234;

EXEC SQL insert into EMP(EMPNO,HIREDATE)

values(123,to_date(:ac_hiredate,'yyyy/mm/dd hh24:mi:ss');

2、宿主变量的作用范围

如果宿主变量在所有的函数之外声明，则他们是全局变量。在使用之前要注意把变量的值初始化，宿主变量也可以在某个函数的内部定义。这时他们是局部变量。一般都习惯把宿主变量声明为全局变量。

3、数据库的连接与断开

数据库的连接有以下两种方法：

(1)

strcpy(vc_user.arr,"scott/tiger");

vc_user.len=11;

exec sql connect :vc_user;

(2)

strcpy(user,"scott");

strcpy(pass,"tiger");

exec sql connect :user identified by :pass;

注意：在有些平台上两种都可以,在有些平台上只能用第一种方法.

在PROC程序中，要记住用EXEC SQL ROLLBACK WORK RELEASE;断开与数据库的连接,并释放相关的数据库资源。

4、PROC中的NULL值的处理

如果某一字段取出的值是NULL,会报:sqlcode=-1405, sqlerr=ORA-01405: 读取的列值为NULL

并且相应的宿主变量的值不会被改变,为执行该SQL语句之前的值. 常用的处理NULL值的方法有：

(1)采用指示器变量,此时不会有-1405错误,当必须是所以为NULL的字段都有相应的指示器变量,如果某一字段没有指示器变量,但取出的值

为NULL值,则仍然会有-1405错误.当取出的值是NULL时，相应的指示器变量变量为-1，可根据指示器变量的值做响应的处理。

(2)如果字段较

[NextPage][/NextPage]

多,可取字段到一个结构体中及与该结构体对应的指示器结构体中.如上面的例子中可定义结构体：

struct str_emp{

long al_empno;

char ac_ename;

char ac_hiredate;

double af_sal;

};

struct str_emp_ind{

long al_empno;

char ac_ename;

char ac_hiredate;

double af_sal;

};

struct str_emp str_emp;

strcut str_emp_ind str_emp_ind;

在取之前可用memset(&str_emp,0,sizeof(str_emp)).清空该结构体,这样如果是字符型的NULL,会为"",整型的NULL会为0,

浮点型的会为0.00。此时不会有-1405错误。

(3)也可采用NVL()函数:举例如下：

EXEC SQL DECLARE authors CURSOR FOR

SELECT EMPNO,

NVL(ENAME,chr(0)),nvl(to_char(HIREDATE,'yyyy/mm/dd

hh24:mi:ss'),chr(0)),NVL(SAL,0) FROM EMP;

这样也不会有-1405错误不,当取出的值是NULL时,自动用NVL()中指定的值代替.

CHR(0)也可直接用''代替,如下:

SELECT EMPNO, NVL(ENAME,''),nvl(to_char(HIREDATE,'yyyy/mm/dd

hh24:mi:ss'),''),NVL(SAL,0) FROM EMP;

5、PROC中的错误的处理

所有的SQL语句都有可能出错.所以都要加以判断,但每个SQL语句后都加错误判断,太麻烦,可用一个函数如sql_error()来进行错误处理,

方法:

(1)定义ql_error()函数。

(2)在开头加上EXEC SQL WHENEVER SQLERROR DO sql_error();这样当发生sqlca.sqlcode <0 的错误时,程序自动转到sql_error()中执行. 注意:对

sqlca.sqlcode >0的错误如sqlca.sqlcode =1403 是不会转到sql_error()中执行的.

另外:在UNIX下，可以用OERR 来查找错误的描述。如：ora ORA -1405 查找错误号为-1405的描述.

6、PROC中调用存储过程的方法

要把存储过程放在EXEC SQL EXECUTE 和END-EXEC;之间，如下所示：

其中：al_empno,ac_ename 为输入参数,l_return,l_errno,c_errtext 为输出参数。

al_empno=8888;

strcpy(ac_ename,"ABCD");

EXEC SQL EXECUTE

BEGIN

up_db_emp(:al_empno,:ac_ename,:l_return,:l_errno,:c_errtext);

END;

END-EXEC;

if (l_return != 0)

{

printf("调用UP_PB_EMP存储过程出错,errno=%ld,errtext=%s

",l_errno,c_errtext);

}

7、PROC的命令行选项：PROC编译器有很多的命令行选项，在命令行下直接不带参数运行PROC，会列出所有的命令行选项来，并有说明。

(1)储存过程:编译储存过程是要带上用户名及密码

proc USERID=scott/tiger sqlcheck=SEMANTICS ireclen=512

iname=test.cpp

(2)PARSE=NONE 对非SQL代码不进行语法分析，默认对非SQL代码也进行语法分析.

在RED HAD6.3上的ORACLE8.1.5中用PROC时,会提

示:/USR/INCLUDE/STDIO.H 及其他的.H文件中有错. 可把PARSE=NONE加上,就好了.

8、注意加上：EXEC ORACLE OPTION (RELEASE_CURSOR = YES);

RELEASE_CURSOR=YES 使PROC 在执行完后释放与嵌入SQL有关资源，保证在该PROC程序执行完后，ORACLE不会锁住数据库资源，如锁表等。

如果在PROC中用到ORACA，

[NextPage][/NextPage]

还要在程序头加上：

EXEC ORACLE OPTION (ORACA=YES);

9、PROC中的类型转换

一、在C语言中:

(1)字符型到整型可用ATOI() ATOL(),SSCANF()

(2)整型,浮点型到字符型,可用SPRINTF()

(3)字符型到浮点型用ATOF()不行,最好用SSCANF(),举例如下: EXEC SQL BEGIN DECLARE SECTION;

double d_demo;

float f_demo;

char ac_text[20]="222";

EXEC SQL END DECLARE SECTION;

(1)sscanf(ac_text, "%f", &d_demo);

printf("ac_text=%s,d_demo=%f ",ac_text,d_demo); (2)sscanf(ac_text, "%lf", &d_demo);

printf("ac_text=%s,d_demo=%f ",ac_text,d_demo); (3)sscanf(ac_text, "%f", &d_demo);

printf("ac_text=%s,d_demo=%lf ",ac_text,d_demo); (4)sscanf(ac_text, "%lf", &d_demo);

printf("ac_text=%s,d_demo=%lf ",ac_text,d_demo); printf("******************* ");

(5)sscanf(ac_text, "%f", &f_demo);

printf("ac_text=%s,f_demo=%f ",ac_text,f_demo); (6)sscanf(ac_text, "%lf", &f_demo);

printf("ac_text=%s,f_demo=%f ",ac_text,f_demo); (7)sscanf(ac_text, "%f", &f_demo);

printf("ac_text=%s,f_demo=%lf ",ac_text,f_demo); (8)sscanf(ac_text, "%lf", &f_demo);

printf("ac_text=%s,f_demo=%lf ",ac_text,f_demo);

输出的结果:

ac_text=222.00,d_demo=0.000000

ac_text=222.00,d_demo=222.000000

ac_text=222.00,d_demo=222.000032

ac_text=222.00,d_demo=222.000000

*******************

ac_text=222.00,f_demo=222.000000

ac_text=222.00,f_demo=0.000000

ac_text=222.00,f_demo=222.000000

ac_text=222.00,f_demo=0.000000

d_demo=atof(ac_text);

printf("ac_text=%s,atof(ac_text)=%f ",ac_text,d_demo);

d_demo=atof(ac_text);

printf("ac_text=%s,atof(ac_text)=%lf ",ac_text,d_demo);

f_demo=atof(ac_text);

printf("ac_text=%s,atof(ac_text)=%f ",ac_text,f_demo);

f_demo=atof(ac_text);

printf("ac_text=%s,atof(ac_text)=%lf ",ac_text,f_demo);

输出的结果:

ac_text=222.00,atof(ac_text)=1243288.000000

从上面的结果可见:

DOUBLE型应采用sscanf(ac_app_capcity, "%lf", &d_app); 打印用

"%lf","%f" 都可以. (2),(4)正确

FLOAT型应采用sscanf(ac_app_capcity, "%f", &d_app); 打印用"%lf","%f" 都可以. (5),(7)正确

采用ATOF()转换的结果都是错的,所以不要用它。

二、写表或从表中取数据时:

(1)字符型与整型之间可不用转换,采用默认方式。

(2)字符型与浮点型之间可不用转换,采用默认方式。

(3)日期型与字符型之间可用TO_CHAR(),TO_DATE()。

10、PROC中的4种动态SQL简介

(1)动态SQL1

[NextPage][/NextPage]

: 不能是查询(SELECT)语句,并且没有宿主变量.

用法:拼一串动态SQL语句,并用EXECUTE IMMEDIATE执行,如:

EXEC SQL EXECUTE IMMEDIATE "CREATE TABLE dyn1 (col1 VARCHAR2(4))";

(2)动态SQL2: 不能是查询(SELECT)语句,并且输入的宿主变量数目是知道的,

用法:拼一串动态SQL语句,用PREPARE,EXECUTE语句执行.

strcpy(c_sql, "DELETE FROM EMP WHERE EMPNO = :");

EXEC SQL PREPARE sql_stmt FROM :c_sql;

EXEC SQL EXECUTE sql_stmt USING :emp_number;

(3)动态SQL3: 用于创建动态查询, 并且要查询的字段及输入的宿主变量数目是知道的

用法: 拼一串动态SQL语句,用PREPARE分析该语句,并要定义一个CURSOR进行取值

如:如要查询的数据按一年12月放到12张表中。表名为user_fee_1mon,

user_fee_2mon,....可采用动态SQL3来进行查询

strcpy(c_sql,"select

c_user_id,c_user_name,to_char(t_date,'yyyy/mm/dd hh:mi:ss'),n_fee ");

strcat(c_sql,"from USER_FEE_");

strcat(c_sql,ac_mon);

strcat(c_sql," where c_user_id = :v1");

EXEC SQL PREPARE s FROM :c_sql;

EXEC SQL DECLARE cur_user_fee CURSOR FOR s;

EXEC SQL OPEN cur_user_fee USING :ac_user_id;

while(1)

{

EXEC SQL FETCH cur_user_fee

into :c_user_id,:c_user_name,:c_date,:n_fee);

if (sqlca.sqlcode < 0)

{

/*FETCH CURSOR失败*/

printf("fetch cursor cur_user_fee

fail,sqlcode=%ld,sqlserr=%s",sqlca.sqlcode,sqlca.sqlerrm.sqlerrmc);

}

if( sqlca.sqlcode == SQLNOTFOUND)

{

break;

}

EXEC SQL CLOSE cur_user_fee;

(4)动态SQL4:要处理的字段及输入的宿主变量数目和主变量的类型事先是不知道的,如：

INSERT INTO EMP () VALUES ()

是最复杂的动态SQL,很少用,在此不做介绍。

11、SQLCA：SQL是ORACLE的一个结构体，它的域用于最近的一条SQL语句执行后的一些信息，如错误号，错误描述，警告，状态等。常用的

域介绍如下:

SQLCA.sqlcode:错误号,=0正确,=1403没取到数据

SQLCA.sqlserrm.sqlerrmc:错误描述

SQLCA.sqlerrd[3]:最近的一条SQL语句所处理的行数,如果该语句处理失败,则它的值是不定的,如果错误在一个CURSOR操作中发生,则

它的值指已成功处理的行数.在DELETE,UPDATE中,它不包含因外键约束而删除,更新的那些行,

DELETE FROM EMP WHERE DEPT='SALE';

在表EMP中删除20行,但如果表EMP与表ADDRESS有外键约束,导致表ADDRESS也被删除20行,则SQLCA.sqlerrd[3]=20,而不是40。

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Pro*C介绍-内嵌SQL

[ Translate by Z.Jingwei. Document address：

https://www.360docs.net/doc/fd17849613.html,/~ullman/fcdb/oracle/or-proc.html] Pro

[NextPage][/NextPage]

*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 ", 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

[NextPage][/NextPage]

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，译注：'')的变长字符串。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;

/* ... */