PROC是ORACLE数据库提供的编程接口.doc

PROC是ORACLE数据库提供的编程接口之一，其应用十分的广泛，本文通过一个具体的例子，介绍PROC编程的一些经验及应注意的地方。例子程序：#include #include #include #include #include EXEC SQL INCLUDE sqlca;/*RELEASE_CURSOR=YES 使PROC 在执行完后释放与嵌入SQL有关资源*/EXEC ORACLE OPTION (RELEASE_CURSOR = YES);EXEC SQL BEGIN DECLARE SECTION;varchar vc_user20;long al_empno=0;char ac_ename11=;char ac_hiredate20=;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(n%s,%ld,%sn, 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/tigerDEMO);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=%lfn,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型变量用0做结尾。如：ENAME在表中的定义为ename varchar2(10)，在PROC中可定义为：EXEC SQL BEGIN DECLARE SECTION;char ename11;EXEC SQL END DECLARE SECTION;常见错误说明:如果插入的字符串长度大于10，如：EXEC SQL INSERT INTO EMP(ENAME) VALUES(12345678901);会出现以下错误:error:ORA-01480: STR 赋值变量缺少空后缀。如果定义为:EXEC SQL BEGIN DECLARE SECTION;char ename15;EXEC SQL END DECLARE SECTION;当插入的字符串长度大于10，小于15时,如：EXEC SQL INSERT INTO EMP(ENAME) VALUES(12345678901);会出现以下错误: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 ename10;EXEC SQL END DECLARE SECTION; 如果ENAME在表中的值为abc,则取出的值为abc ;可用语句EXEC SQL VAR重定义CHAR型变量。这样宿主变量会自动去掉右空格。如下: EXEC SQL BEGIN DECLARE SECTION;char ename11;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)如果字段较多,可取字段到一个结构体中及与该结构体对应的指示器结构体中.如上面的例子中可定义结构体：struct str_emplong al_empno;char ac_ename;char ac_hiredate;double af_sal;struct str_emp_indlong 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的错误如 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 BEGINup_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=%sn,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，还要在程序头加上：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_text20=222;EXEC SQL END DECLARE SECTION; (1)sscanf(ac_text, %f, &d_demo);printf(ac_text=%s,d_demo=%fn,ac_text,d_demo);(2)sscanf(ac_text, %lf, &d_demo);printf(ac_text=%s,d_demo=%fn,ac_text,d_demo);(3)sscanf(ac_text, %f, &d_demo);printf(ac_text=%s,d_demo=%lfn,ac_text,d_demo);(4)sscanf(ac_text, %lf, &d_demo);printf(ac_text=%s,d_demo=%lfn,ac_text,d_demo);printf(*n);(5)sscanf(ac_text, %f, &f_demo);printf(ac_text=%s,f_demo=%fn,ac_text,f_demo);(6)sscanf(ac_text, %lf, &f_demo);printf(ac_text=%s,f_demo=%fn,ac_text,f_demo);(7)sscanf(ac_text, %f, &f_demo);printf(ac_text=%s,f_demo=%lfn,ac_text,f_demo);(8)sscanf(ac_text, %lf, &f_demo);printf(ac_text=%s,f_demo=%lfn,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)=%fn,ac_text,d_demo);d_demo=atof(ac_text);printf(ac_text=%s,atof(ac_text)=%lfn,ac_text,d_demo);f_demo=atof(ac_text);printf(ac_text=%s,atof(ac_text)=%fn,ac_text,f_demo);f_demo=atof(ac_text);printf(ac_text=%s,atof(ac_text)=%lfn,ac_text,f_demo);输出的结果: ac_text=222.00,atof(ac_text)=1243288.000000ac_text=222.00,atof(ac_text)=1243288.000000ac_text=222.00,atof(ac_text)=1243288.000000ac_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: 不能是查询(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_feen);strcat(c_sql,from USER_FEE_);strcat(c_sql,ac_mon);strcat(c_sql, n where c_user_id = :v1);EXEC SQ