C语言OCI的方式连接oracle数据库

sql语句

CREATE TABLE "SYS_LOG" (
  "ID" NVARCHAR2(32) NOT NULL ,
  "LOG_TYPE" NUMBER(11) ,
  "LOG_CONTENT" NVARCHAR2(1000) ,
  "OPERATE_TYPE" NUMBER(11) ,
  "USERID" NVARCHAR2(32) ,
  "USERNAME" NVARCHAR2(100) ,
  "IP" NVARCHAR2(100) ,
  "METHOD" NVARCHAR2(500) ,
  "REQUEST_URL" NVARCHAR2(255) ,
  "REQUEST_PARAM" NCLOB ,
  "REQUEST_TYPE" NVARCHAR2(10) ,
  "COST_TIME" NUMBER(20) ,
  "CREATE_BY" NVARCHAR2(32) ,
  "CREATE_TIME" DATE ,
  "UPDATE_BY" NVARCHAR2(32) ,
  "UPDATE_TIME" DATE 
);



INSERT INTO "SYS_LOG" VALUES ('1487d69ff97888f3a899e2ababb5ae48', '1', '用户名: admin,登录成功！', NULL, NULL, NULL, '127.0.0.1', NULL, NULL, NULL, NULL, NULL, 'jeecg-boot', TO_DATE('2019-01-22 14:21:17', 'SYYYY-MM-DD HH24:MI:SS'), NULL, NULL);
INSERT INTO "SYS_LOG" VALUES ('cc7fa5567e7833a3475b29b7441a2976', '1', '用户名: admin,登录成功！', NULL, NULL, NULL, '127.0.0.1', NULL, NULL, NULL, NULL, NULL, 'jeecg-boot', TO_DATE('2019-01-22 14:21:31', 'SYYYY-MM-DD HH24:MI:SS'), NULL, NULL);
INSERT INTO "SYS_LOG" VALUES ('asdqwe567e7833a3475b29b7441asdqw', '1', '用户名: cxx,登录成功！', NULL, NULL, NULL, '127.0.0.1', NULL, NULL, NULL, NULL, NULL, 'jeecg-boot', TO_DATE('2019-01-22 14:21:31', 'SYYYY-MM-DD HH24:MI:SS'), NULL, NULL);

select查询

#define _CRT_SECURE_NO_WARNINGS     //这个宏定义最好要放到.c文件的第一行
#include 
#include 
#include 
#include 
static text *username = (text *) "scott";
static text *password = (text *) "oracle";

/* Define SQL statements to be used in program. ,LOG_TYPE,LOG_CONTENT  */
static text *selectlogbytype = (text *)"SELECT ID,LOG_CONTENT,LOG_TYPE FROM SYS_LOG WHERE LOG_TYPE = 1";

static OCIEnv *envhp;
static OCIError *errhp;

static void checkerr(/*_ OCIError *errhp, sword status _*/);
static void cleanup(/*_ void _*/);
static void myfflush(/*_ void _*/);
int main(/*_ int argc, char *argv[] _*/);

static sword status;

int main()
{
	//参数类型
	//ub1 logid, logType,logContent;
	sb2 ind[3];					/* 指示符变量 */
	OCIDescribe  *dschndl1 = (OCIDescribe *)0,
		*dschndl2 = (OCIDescribe *)0,
		*dschndl3 = (OCIDescribe *)0;

	OCISession *authp = (OCISession *)0;	/* 用户会话句柄 */
	OCIServer *srvhp;	/* 服务器句柄 */
	OCISvcCtx *svchp;	/* 服务句柄 */
	OCIStmt   *stmthp;
	OCIDefine *defnp = (OCIDefine *)0;

	OCIBind  *bnd1p = (OCIBind *)0;             /* the first bind handle */
	OCIBind  *bnd2p = (OCIBind *)0;             /* the second bind handle */
	OCIBind  *bnd3p = (OCIBind *)0;             /* the third bind handle */
	OCIBind  *bnd4p = (OCIBind *)0;             /* the fourth bind handle */
	OCIBind  *bnd5p = (OCIBind *)0;             /* the fifth bind handle */
	OCIBind  *bnd6p = (OCIBind *)0;             /* the sixth bind handle */

	sword errcode = 0;
	/* 将模式初始化为线程和对象环境 */
	errcode = OCIEnvCreate((OCIEnv **)&envhp, (ub4)OCI_DEFAULT,
		(dvoid *)0, (dvoid * (*)(dvoid *, size_t)) 0,
		(dvoid * (*)(dvoid *, dvoid *, size_t)) 0,
		(void(*)(dvoid *, dvoid *)) 0, (size_t)0, (dvoid **)0);

	if (errcode != 0) {
		(void)printf("OCIEnvCreate failed with errcode = %d.\n", errcode);
		exit(1);
	}

	/* 分配一个错误句柄 */
	(void)OCIHandleAlloc((dvoid *)envhp, (dvoid **)&errhp, OCI_HTYPE_ERROR,
		(size_t)0, (dvoid **)0);
	/* 分配一个服务器句柄 */
	(void)OCIHandleAlloc((dvoid *)envhp, (dvoid **)&srvhp, OCI_HTYPE_SERVER,
		(size_t)0, (dvoid **)0);
	/* 分配一个服务句柄 */
	(void)OCIHandleAlloc((dvoid *)envhp, (dvoid **)&svchp, OCI_HTYPE_SVCCTX,
		(size_t)0, (dvoid **)0);

	//(void)OCIServerAttach(srvhp, errhp, (text *)"", strlen(""), 0);
	//连接远程服务器
	(void)OCIServerAttach(srvhp, errhp, (text *)"82.156.213.852:1521/oracle", strlen("82.156.213.852:1521/oracle"), 0);



	/* 在服务上下文句柄中设置服务器属性*/
	(void)OCIAttrSet((dvoid *)svchp, OCI_HTYPE_SVCCTX, (dvoid *)srvhp,
		(ub4)0, OCI_ATTR_SERVER, (OCIError *)errhp);
	/* 分配一个用户会话句柄 */
	(void)OCIHandleAlloc((dvoid *)envhp, (dvoid **)&authp,
		(ub4)OCI_HTYPE_SESSION, (size_t)0, (dvoid **)0);
	/* 在用户会话句柄中设置用户名属性 */
	(void)OCIAttrSet((dvoid *)authp, (ub4)OCI_HTYPE_SESSION,
		(dvoid *)username, (ub4)strlen((char *)username),
		(ub4)OCI_ATTR_USERNAME, errhp);
	/* 在用户会话句柄中设置密码属性 */
	(void)OCIAttrSet((dvoid *)authp, (ub4)OCI_HTYPE_SESSION,
		(dvoid *)password, (ub4)strlen((char *)password),(ub4)OCI_ATTR_PASSWORD, errhp);

	checkerr(errhp, OCISessionBegin(svchp, errhp, authp, OCI_CRED_RDBMS,(ub4)OCI_DEFAULT));
	/* 在服务上下文句柄中设置用户会话属性*/
	(void)OCIAttrSet((dvoid *)svchp, (ub4)OCI_HTYPE_SVCCTX,
		(dvoid *)authp, (ub4)0,
		(ub4)OCI_ATTR_SESSION, errhp);

	checkerr(errhp, OCIHandleAlloc((dvoid *)envhp, (dvoid **)&stmthp,
		OCI_HTYPE_STMT, (size_t)0, (dvoid **)0));
	 
	//定义变量的类型 ,LOG_TYPE,LOG_CONTENT
	text logId[50];

	text logContent[100];
	int logType;

	//准备sql语句
	checkerr(errhp, OCIStmtPrepare(stmthp, errhp, selectlogbytype,
		(ub4)strlen((char *)selectlogbytype),
		(ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT));

		//绑定输出列
		checkerr(errhp, OCIDefineByPos(stmthp, &defnp, errhp, 1, (ub1*)logId,
			sizeof(logId), SQLT_STR, &ind[0], (ub2 *)0,(ub2 *)0, OCI_DEFAULT));
		checkerr(errhp, OCIDefineByPos(stmthp, &defnp, errhp,2, (ub1*)logContent,
			sizeof(logContent), SQLT_STR, &ind[1], (ub2 *)0, (ub2 *)0, OCI_DEFAULT));
		checkerr(errhp, OCIDefineByPos(stmthp, &defnp, errhp, 3, (dvoid *)&logType,
			(sb4)sizeof(int), SQLT_INT, &ind[2], (ub2 *)0, (ub2 *)0, OCI_DEFAULT));

		if ((status = OCIStmtExecute(svchp, stmthp, errhp, (ub4)1, (ub4)0,
			(CONST OCISnapshot *) NULL, (OCISnapshot *)NULL, OCI_DEFAULT))
			)
		{
			checkerr(errhp, status);
			cleanup();
			return OCI_ERROR;
		}
		else {
			//用do while是因为 先执行一次
			do
			{
				printf("logId=%s,logContent=%s,logType=%d\n", logId, logContent, logType);
			} while ((OCIStmtFetch(stmthp, errhp, 1, OCI_FETCH_NEXT, OCI_DEFAULT)) != OCI_NO_DATA);
		
		}
}


void checkerr(errhp, status)
OCIError *errhp;
sword status;
{
	text errbuf[512];
	sb4 errcode = 0;

	switch (status)
	{
	case OCI_SUCCESS:
		break;
	case OCI_SUCCESS_WITH_INFO:
		(void)printf("Error - OCI_SUCCESS_WITH_INFO\n");
		break;
	case OCI_NEED_DATA:
		(void)printf("Error - OCI_NEED_DATA\n");
		break;
	case OCI_NO_DATA:
		(void)printf("Error - OCI_NODATA\n");
		break;
	case OCI_ERROR:
		(void)OCIErrorGet((dvoid *)errhp, (ub4)1, (text *)NULL, &errcode,
			errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
		(void)printf("Error - %.*s\n", 512, errbuf);
		break;
	case OCI_INVALID_HANDLE:
		(void)printf("Error - OCI_INVALID_HANDLE\n");
		break;
	case OCI_STILL_EXECUTING:
		(void)printf("Error - OCI_STILL_EXECUTE\n");
		break;
	case OCI_CONTINUE:
		(void)printf("Error - OCI_CONTINUE\n");
		break;
	default:
		break;
	}
}


/*
 *  Exit program with an exit code.
 */
void cleanup()
{
	if (envhp)
		(void)OCIHandleFree((dvoid *)envhp, OCI_HTYPE_ENV);
	return;
}


void myfflush()
{
	eb1 buf[50];

	fgets((char *)buf, 50, stdin);
}


/* end of file cdemo81.c */

insert插入

#define _CRT_SECURE_NO_WARNINGS     //这个宏定义最好要放到.c文件的第一行
#include 
#include 
#include 
#include 
static text *username = (text *) "scott";
static text *password = (text *) "oracle";

/* Define SQL statements to be used in program. ,LOG_TYPE,LOG_CONTENT  */
static text *selectlogbytype = (text *)"SELECT ID,LOG_CONTENT,LOG_TYPE FROM SYS_LOG WHERE LOG_TYPE = 1";
static text *insertsql = (text *)"INSERT INTO SYS_LOG(ID, LOG_CONTENT,LOG_TYPE) VALUES (:logId, :logContent, :logType)";


static OCIEnv *envhp;
static OCIError *errhp;

static void checkerr(/*_ OCIError *errhp, sword status _*/);
static void cleanup(/*_ void _*/);
static void myfflush(/*_ void _*/);
int main(/*_ int argc, char *argv[] _*/);

static sword status;

int main()
{
	//参数类型
	//ub1 logid, logType,logContent;
	sb2 ind[3];					/* 指示符变量 */
	OCIDescribe  *dschndl1 = (OCIDescribe *)0,
		*dschndl2 = (OCIDescribe *)0,
		*dschndl3 = (OCIDescribe *)0;

	OCISession *authp = (OCISession *)0;	/* 用户会话句柄 */
	OCIServer *srvhp;	/* 服务器句柄 */
	OCISvcCtx *svchp;	/* 服务句柄 */
	OCIStmt   *inserthp, *stmthp;




	OCIDefine *defnp = (OCIDefine *)0;
	OCIBind  *bnd1p = (OCIBind *)0;             /* the first bind handle */
	OCIBind  *bnd2p = (OCIBind *)0;             /* the second bind handle */
	OCIBind  *bnd3p = (OCIBind *)0;             /* the third bind handle */
	OCIBind  *bnd4p = (OCIBind *)0;             /* the fourth bind handle */
	OCIBind  *bnd5p = (OCIBind *)0;             /* the fifth bind handle */
	OCIBind  *bnd6p = (OCIBind *)0;             /* the sixth bind handle */

	sword errcode = 0;
	/* 将模式初始化为线程和对象环境 */
	errcode = OCIEnvCreate((OCIEnv **)&envhp, (ub4)OCI_DEFAULT,
		(dvoid *)0, (dvoid * (*)(dvoid *, size_t)) 0,
		(dvoid * (*)(dvoid *, dvoid *, size_t)) 0,
		(void(*)(dvoid *, dvoid *)) 0, (size_t)0, (dvoid **)0);

	if (errcode != 0) {
		(void)printf("OCIEnvCreate failed with errcode = %d.\n", errcode);
		exit(1);
	}

	/* 分配一个错误句柄 */
	(void)OCIHandleAlloc((dvoid *)envhp, (dvoid **)&errhp, OCI_HTYPE_ERROR,
		(size_t)0, (dvoid **)0);
	/* 分配一个服务器句柄 */
	(void)OCIHandleAlloc((dvoid *)envhp, (dvoid **)&srvhp, OCI_HTYPE_SERVER,
		(size_t)0, (dvoid **)0);
	/* 分配一个服务句柄 */
	(void)OCIHandleAlloc((dvoid *)envhp, (dvoid **)&svchp, OCI_HTYPE_SVCCTX,
		(size_t)0, (dvoid **)0);

	(void)OCIServerAttach(srvhp, errhp, (text *)"", strlen(""), 0);
	//连接远程服务器
	//(void)OCIServerAttach(srvhp, errhp, (text *)"82.156.213.852:1521/oracle", strlen("82.156.213.852:1521/oracle"), 0);



	/* 在服务上下文句柄中设置服务器属性*/
	(void)OCIAttrSet((dvoid *)svchp, OCI_HTYPE_SVCCTX, (dvoid *)srvhp,
		(ub4)0, OCI_ATTR_SERVER, (OCIError *)errhp);
	/* 分配一个用户会话句柄 */
	(void)OCIHandleAlloc((dvoid *)envhp, (dvoid **)&authp,
		(ub4)OCI_HTYPE_SESSION, (size_t)0, (dvoid **)0);
	/* 在用户会话句柄中设置用户名属性 */
	(void)OCIAttrSet((dvoid *)authp, (ub4)OCI_HTYPE_SESSION,
		(dvoid *)username, (ub4)strlen((char *)username),
		(ub4)OCI_ATTR_USERNAME, errhp);
	/* 在用户会话句柄中设置密码属性 */
	(void)OCIAttrSet((dvoid *)authp, (ub4)OCI_HTYPE_SESSION,
		(dvoid *)password, (ub4)strlen((char *)password), (ub4)OCI_ATTR_PASSWORD, errhp);

	checkerr(errhp, OCISessionBegin(svchp, errhp, authp, OCI_CRED_RDBMS, (ub4)OCI_DEFAULT));
	/* 在服务上下文句柄中设置用户会话属性*/
	(void)OCIAttrSet((dvoid *)svchp, (ub4)OCI_HTYPE_SVCCTX,
		(dvoid *)authp, (ub4)0,
		(ub4)OCI_ATTR_SESSION, errhp);

	checkerr(errhp, OCIHandleAlloc((dvoid *)envhp, (dvoid **)&inserthp,
		OCI_HTYPE_STMT, (size_t)0, (dvoid **)0));


	/**当我们绑定insert语句时，我们还需要分配存储空间
	因此将在分配语句句柄时分配它；这将在语句消失且内容减少时获得释放碎片化。
+2，以允许\\n和\\0**/

	//insert的字段
	sword   insert_type;
	text     *insert_id, *insert_content;
	sb4      idlen = 32;
	sb4      typelen = 11;
	sb4      contentlen = 50;
	checkerr(errhp, OCIHandleAlloc((dvoid *)envhp, (dvoid **)&inserthp,
		OCI_HTYPE_STMT, (size_t)idlen + 2,
		(dvoid **)&insert_id));
	insert_id = "asdww1111";
	insert_content = "asdadffff2";
	insert_type = 2;

	//准备insert sql语句
	checkerr(errhp, OCIStmtPrepare(inserthp, errhp, insertsql,
		(ub4)strlen((char *)insertsql),
		(ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT));


	sb2      sal_ind, job_ind;
	sword    empno, sal, deptno;


	/*  Bind the placeholders in the INSERT statement. */
	if ((status = OCIBindByName(inserthp, &bnd1p, errhp, (text *) ":logId",
		-1, (dvoid *)insert_id,
		idlen + 1, SQLT_STR, (dvoid *)0,
		(ub2 *)0, (ub2 *)0, (ub4)0, (ub4 *)0, OCI_DEFAULT)) ||
		(status = OCIBindByName(inserthp, &bnd2p, errhp, (text *) ":logContent",
			-1, (dvoid *)insert_content,
			contentlen + 1, SQLT_STR, (dvoid *)0,
			(ub2 *)0, (ub2 *)0, (ub4)0, (ub4 *)0, OCI_DEFAULT)) ||
			(status = OCIBindByName(inserthp, &bnd3p, errhp, (text *) ":logType",
				-1, &insert_type,
				(sword) sizeof(insert_type), SQLT_INT, (dvoid *)0,
				(ub2 *)0, (ub2 *)0, (ub4)0, (ub4 *)0, OCI_DEFAULT)))
	{
		checkerr(errhp, status);
		cleanup();
		return OCI_ERROR;
	}





	//执行insert语句
	if ((status = OCIStmtExecute(svchp, inserthp, errhp, (ub4)1, (ub4)0,
		(CONST OCISnapshot *) NULL, (OCISnapshot *)NULL, OCI_DEFAULT))
		&& status != 1)
	{
		checkerr(errhp, status);
		cleanup();
		return OCI_ERROR;
	}


	/*



	//定义变量的类型 ,LOG_TYPE,LOG_CONTENT
	text logId[50];
	text logContent[100];
	int logType;

	//准备select sql语句
	checkerr(errhp, OCIStmtPrepare(stmthp, errhp, selectlogbytype,
		(ub4)strlen((char *)selectlogbytype),
		(ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT));



	//绑定输出列
	checkerr(errhp, OCIDefineByPos(stmthp, &defnp, errhp, 1, (ub1*)logId,
		sizeof(logId), SQLT_STR, &ind[0], (ub2 *)0, (ub2 *)0, OCI_DEFAULT));
	checkerr(errhp, OCIDefineByPos(stmthp, &defnp, errhp, 2, (ub1*)logContent,
		sizeof(logContent), SQLT_STR, &ind[1], (ub2 *)0, (ub2 *)0, OCI_DEFAULT));
	checkerr(errhp, OCIDefineByPos(stmthp, &defnp, errhp, 3, (dvoid *)&logType,
		(sb4)sizeof(int), SQLT_INT, &ind[2], (ub2 *)0, (ub2 *)0, OCI_DEFAULT));

	if ((status = OCIStmtExecute(svchp, stmthp, errhp, (ub4)1, (ub4)0,
		(CONST OCISnapshot *) NULL, (OCISnapshot *)NULL, OCI_DEFAULT))
		)
	{
		checkerr(errhp, status);
		cleanup();
		return OCI_ERROR;
	}
	else {
		//用do while是因为 先执行一次
		do
		{
			printf("logId=%s,logContent=%s,logType=%d\n", logId, logContent, logType);
		} while ((OCIStmtFetch(stmthp, errhp, 1, OCI_FETCH_NEXT, OCI_DEFAULT)) != OCI_NO_DATA);


	}
	*/
	/* Commit the change. */
	if (status = OCITransCommit(svchp, errhp, 0))
	{
		checkerr(errhp, status);
		cleanup();
		return OCI_ERROR;
	}

}


void checkerr(errhp, status)
OCIError *errhp;
sword status;
{
	text errbuf[512];
	sb4 errcode = 0;

	switch (status)
	{
	case OCI_SUCCESS:
		break;
	case OCI_SUCCESS_WITH_INFO:
		(void)printf("Error - OCI_SUCCESS_WITH_INFO\n");
		break;
	case OCI_NEED_DATA:
		(void)printf("Error - OCI_NEED_DATA\n");
		break;
	case OCI_NO_DATA:
		(void)printf("Error - OCI_NODATA\n");
		break;
	case OCI_ERROR:
		(void)OCIErrorGet((dvoid *)errhp, (ub4)1, (text *)NULL, &errcode,
			errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
		(void)printf("Error - %.*s\n", 512, errbuf);
		break;
	case OCI_INVALID_HANDLE:
		(void)printf("Error - OCI_INVALID_HANDLE\n");
		break;
	case OCI_STILL_EXECUTING:
		(void)printf("Error - OCI_STILL_EXECUTE\n");
		break;
	case OCI_CONTINUE:
		(void)printf("Error - OCI_CONTINUE\n");
		break;
	default:
		break;
	}
}


/*
 *  Exit program with an exit code.
 */
void cleanup()
{
	if (envhp)
		(void)OCIHandleFree((dvoid *)envhp, OCI_HTYPE_ENV);
	return;
}


void myfflush()
{
	eb1 buf[50];

	fgets((char *)buf, 50, stdin);
}


/* end of file cdemo81.c */