oracle proc 编程基础及最小化案例

oracle proc 编程是在我的概念中是非常陌生的，学习了一天下来发现这东西真的好古老，但是依然健壮、稳定，其中间是编辑一个 .pc 的文件使用 proc 进行预处理，预处理后生成一个真正的.c文件，此时再由 gcc 去编译成一个可执行文件运行。因为 proc 涉及到的知识较多，我无法整理到几篇博文中就概述这些所涉及的知识，所以只能总结代码做好注释防止以后自己需要时忘记。如果有想进一步了解 proc 编程的，可以买名为“Oracle9I ProC C++编程指南”。包括 oracle 的环境搭建也并不是我们讨论的重点，如果有精力的话，我会给大家编写类似的教程。

最小化实现代码

#include <stdio.h>
#include "sqlca.h"						// 包含 proc 头文件

EXEC SQL BEGIN DECLARE SECTION;
	char* serversid = "scott/tiger@orcl";	// 定义宿主变量
EXEC SQL END DECLARE SECTION;

int main(int argc, char* argv)
{
	int ret = 0;
	printf("connect server str = %s\n", serversid);
	
	EXEC SQL connect :serversid;				// 连接服务器
	if (sqlca.sqlcode != 0)						// 错误处理机制，后文会有详细介绍
	{
		ret = sqlca.sqlcode;
		printf("connect error...errcode = %d\n", ret);
		return ret;
	}
	
	printf("connect oracler server success...\n");
	EXEC SQL commit release;				// 提交并关闭连接
	return 0;
}

使用 proc 命令进行编译，编译结果如下：

[oracle@localhost 0723]$ proc hello.pc

Pro*C/C++: Release 11.2.0.1.0 - Production on Fri Jul 24 13:20:03 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

System default option values taken from: /home/oracle_11/app/oracle/product/11.2.0/db_1/precomp/admin/pcscfg.cfg

此时会在当前目录下生成一个预处理过的 .c 文件，.c文件的内容如下：

/* Result Sets Interface */
#ifndef SQL_CRSR
#  define SQL_CRSR
  struct sql_cursor
  {
    unsigned int curocn;
    void *ptr1;
    void *ptr2;
    unsigned int magic;
  };
  typedef struct sql_cursor sql_cursor;
  typedef struct sql_cursor SQL_CURSOR;
#endif /* SQL_CRSR */

/* Thread Safety */
typedef void * sql_context;
typedef void * SQL_CONTEXT;

/* Object support */
struct sqltvn
{
  unsigned char *tvnvsn; 
  unsigned short tvnvsnl; 
  unsigned char *tvnnm;
  unsigned short tvnnml; 
  unsigned char *tvnsnm;
  unsigned short tvnsnml;
};
typedef struct sqltvn sqltvn;

struct sqladts
{
  unsigned int adtvsn; 
  unsigned short adtmode; 
  unsigned short adtnum;  
  sqltvn adttvn[1];       
};
typedef struct sqladts sqladts;

static struct sqladts sqladt = {
  1,1,0,
};

/* Binding to PL/SQL Records */
struct sqltdss
{
  unsigned int tdsvsn; 
  unsigned short tdsnum; 
  unsigned char *tdsval[1]; 
};
typedef struct sqltdss sqltdss;
static struct sqltdss sqltds =
{
  1,
  0,
};

/* File name & Package Name */
struct sqlcxp
{
  unsigned short fillen;
           char  filnam[9];
};
static struct sqlcxp sqlfpn =
{
    8,
    "hello.pc"
};


static unsigned int sqlctx = 18323;


static struct sqlexd {
   unsigned long  sqlvsn;
   unsigned int   arrsiz;
   unsigned int   iters;
   unsigned int   offset;
   unsigned short selerr;
   unsigned short sqlety;
   unsigned int   occurs;
            short *cud;
   unsigned char  *sqlest;
            char  *stmt;
   sqladts *sqladtp;
   sqltdss *sqltdsp;
   unsigned char  **sqphsv;
   unsigned long  *sqphsl;
            int   *sqphss;
            short **sqpind;
            int   *sqpins;
   unsigned long  *sqparm;
   unsigned long  **sqparc;
   unsigned short  *sqpadto;
   unsigned short  *sqptdso;
   unsigned int   sqlcmax;
   unsigned int   sqlcmin;
   unsigned int   sqlcincr;
   unsigned int   sqlctimeout;
   unsigned int   sqlcnowait;
            int   sqfoff;
   unsigned int   sqcmod;
   unsigned int   sqfmod;
   unsigned char  *sqhstv[4];
   unsigned long  sqhstl[4];
            int   sqhsts[4];
            short *sqindv[4];
            int   sqinds[4];
   unsigned long  sqharm[4];
   unsigned long  *sqharc[4];
   unsigned short  sqadto[4];
   unsigned short  sqtdso[4];
} sqlstm = {12,4};

/* SQLLIB Prototypes */
extern sqlcxt ( void **, unsigned int *,
                   struct sqlexd *, struct sqlcxp * );
extern sqlcx2t( void **, unsigned int *,
                   struct sqlexd *, struct sqlcxp * );
extern sqlbuft( void **, char * );
extern sqlgs2t( void **, char * );
extern sqlorat( void **, unsigned int *, void * );

/* Forms Interface */
static int IAPSUCC = 0;
static int IAPFAIL = 1403;
static int IAPFTL  = 535;
extern void sqliem( unsigned char *, signed int * );

typedef struct { unsigned short len; unsigned char arr[1]; } VARCHAR;
typedef struct { unsigned short len; unsigned char arr[1]; } varchar;

/* CUD (Compilation Unit Data) Array */
static short sqlcud0[] =
{12,4130,1,0,0,
5,0,0,0,0,0,27,13,0,0,4,4,0,1,0,1,97,0,0,1,10,0,0,1,10,0,0,1,10,0,0,
36,0,0,2,0,0,30,22,0,0,0,0,0,1,0,
};


#include <stdio.h>
#include "sqlca.h"

/* EXEC SQL BEGIN DECLARE SECTION; */ 

	char* serversid = "scott/tiger@orcl";			// 定义宿主变量
/* EXEC SQL END DECLARE SECTION; */ 


int main(int argc, char* argv[])
{
	int ret = 0;
	printf("serversid: %s\n", serversid);			// 打印一下服务器的连接地址字符串
	
	/* EXEC SQL CONNECT :serversid; */ 

{
 struct sqlexd sqlstm;
 sqlstm.sqlvsn = 12;
 sqlstm.arrsiz = 4;
 sqlstm.sqladtp = &sqladt;
 sqlstm.sqltdsp = &sqltds;
 sqlstm.iters = (unsigned int  )10;
 sqlstm.offset = (unsigned int  )5;
 sqlstm.cud = sqlcud0;
 sqlstm.sqlest = (unsigned char  *)&sqlca;
 sqlstm.sqlety = (unsigned short)4352;
 sqlstm.occurs = (unsigned int  )0;
 sqlstm.sqhstv[0] = (unsigned char  *)serversid;
 sqlstm.sqhstl[0] = (unsigned long )0;
 sqlstm.sqhsts[0] = (         int  )0;
 sqlstm.sqindv[0] = (         short *)0;
 sqlstm.sqinds[0] = (         int  )0;
 sqlstm.sqharm[0] = (unsigned long )0;
 sqlstm.sqadto[0] = (unsigned short )0;
 sqlstm.sqtdso[0] = (unsigned short )0;
 sqlstm.sqphsv = sqlstm.sqhstv;
 sqlstm.sqphsl = sqlstm.sqhstl;
 sqlstm.sqphss = sqlstm.sqhsts;
 sqlstm.sqpind = sqlstm.sqindv;
 sqlstm.sqpins = sqlstm.sqinds;
 sqlstm.sqparm = sqlstm.sqharm;
 sqlstm.sqparc = sqlstm.sqharc;
 sqlstm.sqpadto = sqlstm.sqadto;
 sqlstm.sqptdso = sqlstm.sqtdso;
 sqlstm.sqlcmax = (unsigned int )100;
 sqlstm.sqlcmin = (unsigned int )2;
 sqlstm.sqlcincr = (unsigned int )1;
 sqlstm.sqlctimeout = (unsigned int )0;
 sqlstm.sqlcnowait = (unsigned int )0;
 sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
}

							// 连接服务器
	if (sqlca.sqlcode != 0)										// 容错处理，后面会有更详细的文章介绍
	{
		ret = sqlca.sqlcode;
		printf("connect oracle error... code = %d\n", ret);
		return ret;
	}
	
	printf("connect oracle success...\n");
	/* EXEC SQL COMMIT RELEASE; */ 

{
 struct sqlexd sqlstm;
 sqlstm.sqlvsn = 12;
 sqlstm.arrsiz = 4;
 sqlstm.sqladtp = &sqladt;
 sqlstm.sqltdsp = &sqltds;
 sqlstm.iters = (unsigned int  )1;
 sqlstm.offset = (unsigned int  )36;
 sqlstm.cud = sqlcud0;
 sqlstm.sqlest = (unsigned char  *)&sqlca;
 sqlstm.sqlety = (unsigned short)4352;
 sqlstm.occurs = (unsigned int  )0;
 sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
}

									// 提交(commit)并关闭(release)连接
	
	return 0;
}

这是由 proc 预处理过后的 .c 文件，只不过是将我们之前编写的 pc 文件中的一些 EXE SQL … 等关键字替换成了真正的 c 语言执行语句。最后我们需要使用 gcc 来编译它。如果你直接编译的话，会收到如下报错：

[oracle@localhost 0723]$ gcc hello.c -o hello
/tmp/cchZbCeQ.o: In function `main':
hello.c:(.text+0x86): undefined reference to `ECPGget_sqlca'
hello.c:(.text+0x1ed): undefined reference to `sqlcxt'
hello.c:(.text+0x1f2): undefined reference to `ECPGget_sqlca'
hello.c:(.text+0x200): undefined reference to `ECPGget_sqlca'
hello.c:(.text+0x27f): undefined reference to `ECPGget_sqlca'
hello.c:(.text+0x2b7): undefined reference to `sqlcxt'

这提示的意思是找不到动态库，所以我们需要手动指定动态库和头文件所在路径。正确的编译方法如下：

gcc hello.c -o hello -I $ORACLE_HOME/precomp/public -L $ORACLE_HOME/lib -l clntsh

编译完成后运行程序，如果提示连接成功，那么证明你的程序就可以正常连接 oracle 服务器了：

[oracle@localhost 0723]$ ./hello
serversid: scott/tiger@orcl
connect oracle success...

oracle proc 编程基础及最小化案例

oracle proc 编程基础及最小化案例

最小化实现代码

相关

发表回复取消回复

oracle proc 编程基础及最小化案例

oracle proc 编程基础及最小化案例

最小化实现代码

相关

proc 编程处理 select 获取的数据集

proc 编程错误处理机制及null处理

proc 编程执行 insert、delete、update语句

发表回复 取消回复

发表回复取消回复