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...

My Code

To the pursuit of truth.

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

最小化实现代码

相关

发表评论取消回复

2021年2月
一	二	三	四	五	六	日
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28

最小化实现代码

相关

发表评论 取消回复

发表评论取消回复