OCI小例

为什么80%的码农都做不了架构师？>>>   

环境：

WinXPSP3 + ORACLE 10G + VC6

步骤：

1）新建demo.c，内容如下：

#include <stdio.h>
#include <oratypes.h>
#include <ocidfn.h>
#include <ocidem.h>

//声明cda和lda
struct cda_def	cda;
struct cda_def	lda;

//删表和建表DDL
text *dt=(text *)"DROP TABLE part_nos";
text *ct=(text *)"create table part_nos(partno number, description varchar2(20))";

//建包语句
text *cp=(text *)"/
			create or replace package update_parts as/
				type part_number is table of part_nos.partno%type/
					index by binary_integer;/
				type part_description is table of part_nos.description%type/
					index by binary_integer;/
				procedure add_parts(n		in	number,/
									descrip in	part_description,/
									partno	in	part_number);/
			end update_parts;";
text *cb=(text *)"/
			create or replace package body update_parts as/
				procedure add_parts(n		in	number,/
									descrip	in	part_description,/
									partno	in	part_number) is/
				begin/
					for i in 1..n loop/
						insert into part_nos/
							values(partno(i),descrip(i));/
					end loop;/
				end add_parts;/
			end update_parts;";

#define DESC_LEN		20
#define MAX_TABLE_SIZE	1200

//PLSQ匿名块
text *pl_sql_block=(text *)"/
		BEGIN/
			update_parts.add_parts(3,:description,:partno);/
		end;";
text	descrip[3][20]={"Frammis","Widget","Thingie"};
sword	numbers[]={12125,23169,12126};

ub2	descrip_alen[3]={DESC_LEN,DESC_LEN,DESC_LEN};
ub2	descrip_rc[3];
ub4	descrip_cs=(ub4)3;
ub2	descrip_indp[3];

ub2	num_alen[3]={(ub2)sizeof(sword),(ub2)sizeof(sword),(ub2)sizeof(sword)};
ub2	num_rc[3];
ub4	num_cs=(ub4)3;
ub2	num_indp[3];

dvoid	oci_error(void);

main()
{
	//连接oracle
	printf("connecting to oracle...");
	if(olon(&lda,"scott/x",-1,NULL,-1,-1))
	{//连接失败
		printf("Cannot logon as scott. Exiting.../n");
		exit(1);
	}

	//打开光标
	if(oopen(&cda,&lda,NULL,-1,-1,NULL,-1))
	{//打开光标失败
		printf("Cannot open cursor, exiting.../n");
		exit(1);
	}

	//删除表
	printf("/nDropping table...");
	//分析和执行SQL语句(drop table)
	if(oparse(&cda,dt,-1,0,2))
		if(cda.rc!=942)
			oci_error();

	//创建表
	printf("/ncreating table...");
	//分析和执行sql语句(create table)
	if(oparse(&cda,ct,-1,0,2))
		oci_error();

	//分析和执行建包语句(create or replace package)
	printf("/ncreating package...");
	if(oparse(&cda,cp,-1,0,2))
		oci_error();
	if(oexec(&cda))
		oci_error();

	printf("/ncreating package body...");
	if(oparse(&cda,cb,-1,0,2))
		oci_error();
	if(oexec(&cda))
		oci_error();

	//分析调用存储过程的PLSQL匿名块
	printf("/nparsing PLSQL block...");
	if(oparse(&cda,pl_sql_block,-1,0,2))
		oci_error();

	//把C数组绑定到PLSQ匿名块上
	printf("/nBinding arrays...");
	if(obndra(&cda,
				(text *)":description",
				-1,
				(ub1 *)descrip,
				DESC_LEN,
				VARCHAR2_TYPE,
				-1,
				descrip_indp,
				descrip_alen,
				descrip_rc,
				(ub4)MAX_TABLE_SIZE,
				&descrip_cs,
				(text *)0,
				-1,
				-1))
		oci_error();
	if(obndra(&cda,
				(text *)":partno",
				-1,
				(ub1 *)numbers,
				(sword)sizeof(sword),
				INT_TYPE,
				-1,
				num_indp,
				num_alen,
				num_rc,
				(ub4)MAX_TABLE_SIZE,
				&num_cs,
				(text *)0,
				-1,
				-1))
		oci_error();

	//执行该块
	printf("/nExecuting block...");
	if(oexec(&cda))
		oci_error();
	printf("/n");

	//关闭光标
	if(oclose(&cda))
	{
		printf("error closing cursor!/n");
		return -1;
	}

	//结束事务，退出oracle
	if(ologof(&lda))
	{
		printf("error logging off!/n");
		return -1;
	}
	exit(1);
}

//oci 错误处理
dvoid
oci_error(void)
{
	text msg[600];
	sword rv;
	//取错误信息
	rv=oerhms(&lda,cda.rc,msg,600);
	//显示错误码和错误信息
	printf("/n/n%.*s",rv,msg);
	//显示发生错误的oci函数
	printf("processing oci function %s/n",oci_func_tab[cda.fc]);
	//关闭光标
	if(oclose(&cda))
		printf("error closing cursor!/n");
	if(ologof(&lda))
		printf("error logging off!/n");
	exit(1);
}

2）编译：

cl demo.c /nologo /c /I E:/oracle/product/10.2.0/db_1/OCI/include

3）链接：

link demo.obj /nologo /LIBPATH:E:/oracle/product/10.2.0/db_1/OCI/lib/MSVC oci.lib

4）执行demo.exe进行测试。

 

 

 

 

 

 

 

 

 

原文链接： http://blog.youkuaiyun.com/t0nsha/article/details/5822442

转载于:https://my.oschina.net/dtec/blog/46773