ORACLE的ProC用法讲解（转）

来源：http://blog.sina.com.cn/s/blog_9b0604b40101kueq.html

 

 pro*c是高级的用法，OCI是oracle的基础用法

如何编译.pc文件： proc code=cpp  parse=none iname=filename.pc oname=filename.cpp

一， 首先要包涵头文件#include 这个头文件

二,  在声明宿主变量之前一定要先定义struct sqlca sqlca;这个变量。

三， 所有与oracle数据库SQL语句有关的变量必须在前面声明为宿主变量分配空间才可以使用，

宿主变量只能是oracle支持的数据类型，一般是基本类型的，结构体类型。只有宿主变量才可以和SQL还有函数中的其他变量之间相互通信。

例子：

 EXEC SQL BEGIN DECLARE SECTION; //开始申明
 char strsql[512]={0};

 EXEC SQL END DECLARE SECTION;   //结束申明

 

四，静态SQL语句之查一条记录的模式，通过宿主变量接收申sql语句所查到的结果

 举例：INTO :TotalRecord, :TotalMoney ，接收结果，proc中用：绑定变量

EXEC SQL  select count(B.msisdn) ,sum(B.payamount)
     INTO :TotalRecord, :TotalMoney
     from OM_MPAY_USER_INFO A , OM_MPAY B
      WHERE  A.msisdn = B.msisdn and A.region=trim(:areacode)  and B.pay_date = :sqltime;

 

五，查询多条记录一般我们使用动态SQL语句，结合游标来收集多条记录的结果，其次要注意的是循环得到结构。

举例：sprintf(strsql,"select *from mytable ")

    EXEC SQL PREPARE SqlCountRegion FROM :strsql; //或者活动区
    EXEC SQL DECLARE CurRegion CURSOR FOR SqlCountRegion; //对活动区申请游标
    EXEC SQL OPEN CurRegion;  //打开游标，打开游标的同时可以用using对其赋值

   do{
        EXEC SQL FETCH CurRegion INTO :regionnum ; //循环捕捉游标中变量的值
        if (sqlca.sqlcode ==1403) 

          break;
        cout<<regionnum<<endl;

 }while(1);

 EXEC SQL CLOSE CurRegion; //关闭游标
 EXEC SQL rollback work release; //回滚数据关闭释放所有资源和链接

 

六，捕捉异常错误和停止程序

//下面的意思是发生错误异常的时候跳转到标记处执行标记后面的语句

EXEC SQL whenever sqlerror  goto errinfo;

errinfo:
 printf("   sqlca.sqlcode=%d, sqlca.errm=%s\n",sqlca.sqlcode, sqlca.sqlerrd);

 EXEC SQL  whenever sqlerror stop; //发生异常的时候推出整个程序，关闭所有连接释放所有资源

  return sqlca.sqlcode;

 

七， proc中的SQL语句不要加分号(;) ,注释如果是C++工程使用如果是C++使用//或者，在oracle中的注释是--，   oracle数据库中的数据如果数据字节不足位数，默认用空格补全，宿主变量的空间要大于数据库的字段空间。

对于结构的特殊含义处理：sqlca.sqlcode ==1403 表示结束了查找循环，这个在动态sql语句中比较重要，

sqlca.sqlcode == -1405 表示查询值为空的时候报的错误，这个最好留给返回值待程序处理，

sqlca.sqlcode == -1480 表示在sql语句中传入的变量时空值一般就是传值失败，可能是空间的大小问题。

 

八， 关于Proc中特殊的SQL语句，如，delete ，update，alter，insert into 等数据变动性操作的时候，

要注意2点，可以再宿主变量区申请复杂的结构类型的指针，用指针将参数外部的数据绑定到sql语句中，接下来就是提交事物，操作完变动性数据之后就是commint提交事物，进行回滚。

例子：

int GetDBRecode( struct buf * tempbuf)

{

EXEC SQL BEGIN DECLARE SECTION; //开始申明
 char strsql[512]={0};

 struct buf * temp=  tempbuf;

 EXEC SQL END DECLARE SECTION;   //结束申明

 exec sql insert into mytable( name, sex, num)values( :temp->name, :temp->sex, :temp->num)

if( sqlca.sqlcode  )

{

       printf("判断是否执行成功");

}

}

 

                             例子讲解

//#include
//#include

//#include"CssCheckBill.h"

//#include

int main()

{

struct sqlca sqlca; //必须要有
 EXEC SQL BEGIN DECLARE SECTION;
 char strsql[512]={0};
 char user[20]={0};
 char pwd[20] ={0};
 char dbname[20]={0};
 char regionnum[7];

 char TotalMoney[15]={0};

 char  TotalRecord[15]={0};
 EXEC SQL END DECLARE SECTION;

 

strcpy(user,"user");
 strcpy(pwd,"pwd");
 strcpy(dbname,"dbname");
 //这句是连接数据库的操作
 EXEC SQL CONNECT :user IDENTIFIED BY :pwd USING :dbname;

 

EXEC SQL  select count(B.msisdn) ,sum(B.payamount)
     INTO :TotalRecord, :TotalMoney   //INTO绑定变量
     from OM_MPAY_USER_INFO A , OM_MPAY B
      WHERE  A.msisdn = B.msisdn and A.region=trim(:areacode)  and B.pay_date = :sqltime;

//设置捕获异常信息标志
 EXEC SQL  whenever sqlerror goto ORA_ERR;
 if(0 >=  ::snprintf(strsql,sizeof(strsql), "select distinct(region) from OM_MPAY_USER_INFO where region is not null" ))
 {
  printf("%s snprintf  create sql  fail !\n", __FUNCTION__);
         return -1;
 }

//动态游标方式获取查询结果
 EXEC SQL PREPARE SqlCountRegion FROM :strsql;
  EXEC SQL DECLARE CurRegion CURSOR FOR SqlCountRegion; //CURSOR FOR 可以直接跟SQL语句
 EXEC SQL OPEN CurRegion;

 do{
  EXEC SQL FETCH CurRegion INTO :regionnum ;
  if (sqlca.sqlcode ==1403) 
        break;
  printf("regionnum=%s,   len=%d\n",regionnum, strlen(regionnum));
    DelStrRightBlack( regionnum );

 }while(1);

//关闭异常捕获信息

 EXEC SQL  whenever sqlerror stop;
 EXEC SQL CLOSE CurRegion;

//设置回滚事物
 EXEC SQL rollback work release;
 return sqlca.sqlcode;
ORA_ERR:
 printf("sqlca.sqlcode=%d, sqlca.sqlerrp=%s\n",sqlca.sqlcode, sqlca.sqlerrp);
 return sqlca.sqlcode;

return 0 ;

}
总结了一下PRO*C中存储过程调用,普通SQL语句游标执行,动态SQL语句游标执行的方法；

PRO*C中常用SQL及游标、存储过程使用汇总：

1) exec sql select c1,c2 into :v1,v2 from table_a;

2) exec sql insert into table_a(v1,v2) select b.v1,b.v2 from table_b b where 1=2;

3) exec sql insert into table_a(v1,v2) select b.v1,b.v2 from table_b b where 1=2;

存储过程调用
4) exec sql call procedure_a(:v1,:v2);
5) sprintf(sta,"select v1,v2 into :v1,v2 from table_a where v3=%s",v3);
exec sql execute immediate :sta;

--普通SQL语句游标执行
6)exec sql declare cur1 cursor for
select v1,v2,v3 from table_a where 1=2;
exec sql open cur1;
do{
exec sql fetch cur1 into :v1,:v2,:v3;
if(sqlca.sqlcode==-1403) break;
....

}while(1) ;
exec sql close cur1;

--动态SQL语句游标执行
7)> sprintf(sta,"select c1,c2,c3 from table_a where c1=%s and c2=:v1 and c3=:v2",v1);
exec sql prepare select_msg from :sta;
exec sql declare cur1 cursor for select_msg;
exec sql open cur1 using :v1,:v2; //打开游标的时候使用using来传迪绑定的变量
do{
exec sql fetch cur1 into :c1,:c2,:c3;
if(sqlca.sqlcode==1403)break;
....
}while(1);
exec sql close cur1;

 

其他情况的proc讲解

Pro*C/C++编程讲解：

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(‘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 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)如果字段较多,可取字段到一个结构体中及与该结构体对应的指示器结构体中.如上面的例子中可定义结构体：

　　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=%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);