转自传智播客
.SUFFIXES: .c .o
CC=gcc
PROC=proc
PROCSRCS=oracle.pc
SRCS=$(PROCSRCS:.pc=.c)
OBJS=$(SRCS:.c=.o)
ORACLE_HOME=/opt/oracle/product/11.2.0
ORAFLAGS1=/usr/include/linux
ORAFLAGS2=/usr/lib/gcc/i686-redhat-linux/4.4.4/include
EXE=abc
all: $(OBJS)
$(CC) -o $(EXE) $(OBJS) -L$(ORACLE_HOME)/lib -lclntsh
$(CC) -Wall -g -o $@ -c $<
$(SRCS):
$(PROC) INAME=$(PROCSRCS) INCLUDE=$(ORAFLAGS1) INCLUDE=$(ORAFLAGS2) CPOOL=YES MODE=ANSI CODE=ANSI_C PARSE=PARTIAL THREADS=YES ONAME=$(SRCS)
clean:
-rm -f $(OBJS)
-rm -f $(SRCS)
-rm -f core*
proc程序编写
/*
============================================================================
Name : oracle.c
Author : zhujy
Version :
Copyright : Your copyright notice
Description : Hello World in C, Ansi-style
============================================================================
*/
#include
#include
#include
#include
#include
//在pc文件当中,如果是嵌入式的SQL语句需要用到的变量,并且proc PARSE=PARTIAL,那么变量必须再BEGIN和END之间定义。 EXEC SQL BEGIN DECLARE SECTION; sql_context pContext; long SQLCODE;//变量类型和名称都不可以改变 EXEC SQL END DECLARE SECTION; extern void sqlglmt(void*, char*, size_t*, size_t* ); //安装错误处理函数 void sql_error() { char sErrorString[512];//自定义一个数组,数组的名字可以自定义 size_t tMessageSize = 0; size_t tErrorSize = sizeof(sErrorString); memset(sErrorString, 0, sizeof(sErrorString)); sqlglmt(pContext, sErrorString, &tErrorSize, &tMessageSize);//调用sqlglmt这个函数得到具体错误描述 sErrorString[tMessageSize] = 0;//设置错误描述字符串最后以0结尾 printf("%s\n", sErrorString);//打印错误描述 } //初始化数据库 void sql_init() { SQLCODE = 0; pContext = NULL; EXEC SQL ENABLE THREADS;//可以在代码当中生成线程相关代码 EXEC SQL CONTEXT ALLOCATE :pContext;//为pContext分配内存 EXEC SQL CONTEXT USE :pContext;//使用pContext } int sql_connect(const char *user, const char *passwd, const char *dbname) { EXEC SQL BEGIN DECLARE SECTION; const char *sUser; const char *sPasswd; const char *sDBname; EXEC SQL END DECLARE SECTION; SQLCODE = 0; sUser = user; sPasswd = passwd; sDBname = dbname; //在嵌入式SQL语句里面 冒号代表要用输入变量 EXEC SQL CONNECT :sUser IDENTIFIED BY :sPasswd USING :sDBname; if (SQLCODE != 0) { sql_error(); return 1; }else { return 0; } } int sql_disconnect() { SQLCODE = 0; EXEC SQL ROLLBACK WORK RELEASE; if (SQLCODE != 0) { sql_error(); return 1; }else { return 0; } } int sql_free() { SQLCODE = 0; EXEC SQL CONTEXT FREE :pContext; if (SQLCODE != 0) { sql_error(); return 1; }else { return 0; } } int sql_exec1(const char *s) { EXEC SQL BEGIN DECLARE SECTION; char SQL[1024]; EXEC SQL END DECLARE SECTION; SQLCODE = 0; memset(SQL, 0, sizeof(SQL)); strcpy(SQL, s); EXEC SQL EXECUTE IMMEDIATE :SQL; if (SQLCODE != 0) { sql_error(); return 1; }else { return 0; } } int sql_exec2() { EXEC SQL BEGIN DECLARE SECTION; char SQL[1024]; int id; char name[30]; EXEC SQL END DECLARE SECTION; id = 1; memset(SQL, 0, sizeof(SQL)); memset(name, 0, sizeof(name)); strcpy(name, "张学友"); strcpy(SQL, "insert into table1 (id, name) values (:id, :name)"); SQLCODE = 0; EXEC SQL PREPARE stat FROM :SQL;//准备执行一条SQL语句, EXEC SQL EXECUTE stat USING :id, :name;//根据输入宿主变量的值,执行SQL //insert into table1 (id, name) values (1, '张学友') if (SQLCODE != 0) { sql_error(); return 1; }else { return 0; } } int sql_exec3() { EXEC SQL BEGIN DECLARE SECTION; char SQL[1024]; int n; int id; char name[32]; EXEC SQL END DECLARE SECTION; memset(SQL, 0, sizeof(SQL)); memset(name, 0, sizeof(name)); n = 1; strcpy(SQL, "select id, name from table1 where id = :n"); SQLCODE = 0; EXEC SQL PREPARE stat FROM :SQL;//要准备执行动态SQL。 EXEC SQL DECLARE C1 CURSOR FOR stat;//定义一个光标表,名字叫C1 EXEC SQL OPEN C1 USING :n;//在光标C1中使用输入宿主变量 EXEC SQL OPEN C1;//打开光标C1 EXEC SQL WHENEVER NOT FOUND DO break;//循环读取光标C1,读取表中每一行,直到最后没有数据,循环break while(1) { EXEC SQL FETCH C1 INTO :id, :name;//将查询结果放入到输出变量id,name printf("id = %d, name = %s\n", id, name); } return 0; } int sql_exec4(const char *DySQL) { EXEC SQL BEGIN DECLARE SECTION; int i, iOutput_count, iOccurs, iType , iLen; short iInd; char sData[1024];//存放select查询数据的返回buffer char sOutput[64]; char sInput[64]; const char *SQL; EXEC SQL END DECLARE SECTION; SQLCODE = 0; iLen = sizeof(sData);//指示buffer大小 iType = 12;//所有select返回的数据集,都按照varchar2类型来处理 SQL = DySQL; sprintf(sOutput, "output%p", pContext);//只是为了动态生成一个系统当中不重复的字符串 sprintf(sInput, "input%p", pContext);//只是为了动态生成一个系统当中不重复的字符串 EXEC SQL ALLOCATE DESCRIPTOR :sOutput;//分配SELECT语句查询输出结果缓冲区 EXEC SQL ALLOCATE DESCRIPTOR :sInput; EXEC SQL PREPARE S FROM :SQL;//准备执行相应的SQL语句 if (SQLCODE != 0) { sql_error(); EXEC SQL DEALLOCATE DESCRIPTOR :sOutput;//释放SELECT语句查询输出结果缓冲区 EXEC SQL DEALLOCATE DESCRIPTOR :sInput; return 1; } EXEC SQL DECLARE C CURSOR FOR S; EXEC SQL OPEN C USING DESCRIPTOR :sInput;//使用输入缓冲区打开一个光标 //选择输出缓冲区 EXEC SQL DESCRIBE OUTPUT S USING DESCRIPTOR :sOutput; //得到SELECt语句返回多少列 EXEC SQL GET DESCRIPTOR :sOutput :iOutput_count = COUNT; for(i=0;i