[原创] Pro*C实战

本总结跟网上那些垃圾转贴有本质的区别，乃实际环境摸索出来的。

============ 分割线 =============

数据库版本：ORACLE 10.2.0.1.0
操作系统：RHEL4.x

1. 编辑$ORACLE_HOME/precomp/admin/pcscfg.cfg，在sys_include=处加上stddef.h所在的目录。

附：stddef.h查找方法
    用root执行find / -name stddef.h -print
    关键在于确定查找出来的N个stddef.h的目录。以下为10.14.3.10服务器上的查找结果：
/usr/include/linux/stddef.h
/usr/include/xulrunner-sdk-1.9/system_wrappers/stddef.h
/usr/lib/gcc/x86_64-redhat-linux6E/4.3.2/include/stddef.h
/usr/lib/gcc/x86_64-redhat-linux/3.4.6/include/stddef.h
/usr/lib/gcc/x86_64-redhat-linux/4.1.1/include/stddef.h
/usr/lib/bcc/include/stddef.h
/usr/lib/syslinux/com32/include/stddef.h
/usr/lib/syslinux/com32/include/bitsize/stddef.h
/usr/src/kernels/2.6.18-128.el5-x86_64/include/linux/stddef.h
/home/term/pack/STLport-5.2.1/stlport/stddef.h
/home/mytools/STLport-5.1.5/stlport/stddef.h

根据http://blog.chinaunix.net/space.php?uid=20011314&do=blog&cuid=527167帖子介绍，我选择了
    /usr/lib/gcc/x86_64-redhat-linux/4.1.1/include/stddef.h加入到pcscfg.cfg文件中。（此处为关键步骤）


2. 编译执行：

$ proc dept.pc
$ cc -o dept dept.c -I $ORACLE_HOME/precomp/public -L $ORACLE_HOME/lib -l clntsh

3. 例一：dept.pc源代码

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlca.h>
#include <locale.h>

int main(int argc, char* argv[])
{
  EXEC SQL BEGIN DECLARE SECTION;
  char *uid = "ocs_test/ocs_test";
  char name[32]="NULL";
  /*int i;*/
  EXEC SQL VAR name is STRING(32);
  EXEC SQL END DECLARE SECTION;

  EXEC SQL CONNECT :uid;

  if(sqlca.sqlcode == 0)
    printf("connect success!\n");
  else
    printf("connect failure!\n");

  EXEC SQL DECLARE cur_client CURSOR FOR select itemname from ocs_sm_function;
  EXEC SQL OPEN cur_client;

  setlocale( LC_ALL, "chs" );
  while(1)
  {
    strcpy(name, "");
    EXEC SQL FETCH cur_client INTO :name;
    if(sqlca.sqlcode == 1403)
    {
        break;
    }
    printf("name is %s\n", name);
  }
  EXEC SQL CLOSE cur_client;
  /* 释放数据库资源 */
  EXEC SQL ROLLBACK WORK RELEASE;
  return 0;
}

4.例二：emp.pc源代码

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlda.h>
#include <sqlcpr.h>

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

EXEC SQL BEGIN DECLARE SECTION;
char *vc_user = "scott/tiger";
/*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("\n%s,%ld,%s\n", msg,sqlca.sqlcode,(char *)sqlca.sqlerrm.sqlerrmc);
    EXEC SQL ROLLBACK RELEASE;
    exit(-1);
}
int main(int argc, char *argv[])
{
    EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE ERROR: ");

    /*连接数据库*/
	/*
    strcpy(vc_user.arr,"scott/tiger");
    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, :ac_hiredate, :af_sal;
        if( sqlca.sqlcode == 1403)
        {
            break;
        }
        printf("empno=%ld,ename=%s,hiredate=%s,sal=%lf\n",al_empno,ac_ename,ac_hiredate,af_sal);
    }
    EXEC SQL CLOSE cur_emp;
    EXEC SQL ROLLBACK WORK RELEASE;
}

5. 例三：test1.pc

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlca.h>

int main(int argc,char* argv[]) 
{
        EXEC SQL BEGIN DECLARE SECTION;
        char *uid = "scott/tiger";
        long userid=0;
        char username[51]="";
        EXEC SQL VAR username IS STRING(51);
        EXEC SQL END DECLARE SECTION;

        EXEC SQL CONNECT :uid;
        if(sqlca.sqlcode == 0)
                printf("connect sucess!\n");
        else
                printf("connect fail!\n");
         EXEC SQL DECLARE cur_user CURSOR FOR SELECT DEPTNO, DNAME FROM DEPT;
         EXEC SQL OPEN cur_user;

         while(1){
          userid=0;
          strcpy(username,"");
          EXEC SQL FETCH cur_user INTO  :userid, :username;
          if( sqlca.sqlcode == 1403)
          {
              break;
          }

          printf("userid=%ld,username=%s\n",userid,username);
         }

         EXEC SQL CLOSE cur_user;
         // EXEC SQL COMMIT WORK RELEASE;
         EXEC SQL ROLLBACK WORK RELEASE;

         return 0;
}

6. 例四：test.pc

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
EXEC SQL include sqlca;
EXEC ORACLE OPTION (RELEASE_CURSOR = YES);
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR username[20];
VARCHAR password[20];
EXEC SQL END DECLARE SECTION;

void sql_error()
{
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	printf("/n Oracle error detected:/n");
	printf("/n%.70s/n",sqlca.sqlerrm.sqlerrmc);
	EXEC SQL ROLLBACK RELEASE;
exit(1);
}

main()
{
	strcpy(username.arr,"scott");
	strcpy(password.arr,"tiger");
	username.len=strlen(username.arr);
	password.len=strlen(password.arr);


	EXEC SQL CONNECT :username IDENTIFIED BY :password;
	printf("sqlca.sqlcode=%d\n",sqlca.sqlcode);
	printf("login user=%s\n",username.arr);
	exit(0);
	return 0;
}

问题：dept.pc输出的中文数据是乱码，尚未解决