⭐ 欢迎来到虫虫下载站！ | 📦 资源下载 📁 资源专辑 ℹ️ 关于我们
⭐ 虫虫下载站
📤 上传资源

📄 db.pc

📁 oracle pro*c编程示例
💻 PC
字号:
🔍 
#define __PROC_C__
#include "db.h"
#undef __PROC_C__



/* 包含SQL通讯区变量 */
EXEC SQL INCLUDE sqlca;

/* RELEASE_CURSOR=YES 使PROC 在执行完后释放与嵌入SQL有关资源 */ 
EXEC ORACLE OPTION (RELEASE_CURSOR = YES); 

/* 全局说明段 */
EXEC SQL BEGIN DECLARE SECTION;
	VARCHAR db_name[32 + 1];
	VARCHAR db_user[32 + 1];
	VARCHAR db_pass[32 + 1];
	
	VARCHAR db_name2[32 + 1];
	VARCHAR db_user2[32 + 1];
	VARCHAR db_pass2[32 + 1];
	VARCHAR db_link2[32 + 1];
EXEC SQL END DECLARE SECTION;

/* 数据库是否连接成功的标志 */
int g_DBConnected = 0;
int g_DBConnected2 = 0;

/* 连接数据库 */
int DBConnect(const char *DBName, const char *DBUser, const char *DBPass)
{
	/* 局部sqlca说明 */
	struct sqlca sqlca;

	/* 返回值定义 */
	int ret;
	
	/* 将形式参数赋到全局说明段中相应变量中 */
	memset(db_name.arr, 0, sizeof(db_name.arr));
	strncpy(db_name.arr, DBName, sizeof(db_name.arr) - 1);
	db_name.len = strlen(db_name.arr);
	
	memset(db_user.arr, 0, sizeof(db_user.arr));
	strncpy(db_user.arr, DBUser, sizeof(db_user.arr) - 1);
	db_user.len = strlen(db_user.arr);
	
	memset(db_pass.arr, 0, sizeof(db_pass.arr));
	strncpy(db_pass.arr, DBPass, sizeof(db_pass.arr) - 1);
	db_pass.len = strlen(db_pass.arr);
	
	/* 执行连接数据库的SQL语句 */
	EXEC SQL CONNECT :db_user IDENTIFIED BY :db_pass USING :db_name;
	
	/* 判断SQL语句执行结果 */
	if (sqlca.sqlcode == 0)
	{	
		g_DBConnected = 1;
		ret = 0;
	}
	else if (sqlca.sqlcode > 0)
	{
		ret = -1;
	}
	else
	{
		ret = -1;
	}
	
	return ret;
}

/* 重连数据库 */
int DBReconnect()
{
	/* 局部sqlca说明 */
	struct sqlca sqlca;
	
	/* 返回值定义 */
	int ret;

	/* 执行连接数据库的SQL语句 */
	EXEC SQL CONNECT :db_user IDENTIFIED BY :db_pass USING :db_name;
	
	/* 判断SQL语句执行结果 */
	if (sqlca.sqlcode == 0)
	{	
		g_DBConnected = 1;
		ret = 0;
	}
	else if (sqlca.sqlcode > 0)
	{
		ret = -1;
	}
	else
	{
		ret = -1;
	}
	
	return ret;
}

/* 断开数据库连接 */
int DBDisconnect()
{
	/* 局部sqlca说明 */
	struct sqlca sqlca;
	
	/* 返回值定义 */
	int ret;
	
	/* 释放操作 */
	EXEC SQL COMMIT WORK RELEASE;
	
	/* 判断SQL语句执行结果 */
	if (sqlca.sqlcode == 0)
	{	
		ret = 0;
	}
	else if (sqlca.sqlcode > 0)
	{
		ret = -1;
	}
	else
	{
		ret = -1;
	}

	g_DBConnected = 0;
	
	return ret;
}

/* 连接数据库2 */
int DBConnect2(const char *DBName, const char *DBUser, const char *DBPass)
{
	/* 局部sqlca说明 */
	struct sqlca sqlca;

	/* 返回值定义 */
	int ret;
	
	/* 将形式参数赋到全局说明段中相应变量中 */
	memset(db_name2.arr, 0, sizeof(db_name2.arr));
	strncpy(db_name2.arr, DBName, sizeof(db_name2.arr) - 1);
	db_name2.len = strlen(db_name2.arr);
	
	memset(db_user2.arr, 0, sizeof(db_user2.arr));
	strncpy(db_user2.arr, DBUser, sizeof(db_user2.arr) - 1);
	db_user2.len = strlen(db_user2.arr);
	
	memset(db_pass2.arr, 0, sizeof(db_pass2.arr));
	strncpy(db_pass2.arr, DBPass, sizeof(db_pass2.arr) - 1);
	db_pass2.len = strlen(db_pass2.arr);
	
	memset(db_link2.arr, 0, sizeof(db_link2.arr));
	snprintf(db_link2.arr, sizeof(db_link2.arr) - 1, "%s_2", DBName);
	db_link2.len = strlen(db_link2.arr);
	
	/* 执行连接数据库的SQL语句 */
	EXEC SQL CONNECT :db_user2 IDENTIFIED BY :db_pass2 AT :db_link2 USING :db_name2;
	
	/* 判断SQL语句执行结果 */
	if (sqlca.sqlcode == 0)
	{	
		g_DBConnected2 = 1;
		ret = 0;
	}
	else if (sqlca.sqlcode > 0)
	{
		ret = -1;
	}
	else
	{
		ret = -1;
	}
	
	return ret;
}

/* 重连数据库2 */
int DBReconnect2()
{
	/* 局部sqlca说明 */
	struct sqlca sqlca;
	
	/* 返回值定义 */
	int ret;

	/* 执行连接数据库的SQL语句 */
	EXEC SQL CONNECT :db_user2 IDENTIFIED BY :db_pass2 AT :db_link2 USING :db_name2;
	
	/* 判断SQL语句执行结果 */
	if (sqlca.sqlcode == 0)
	{	
		g_DBConnected2 = 1;
		ret = 0;
	}
	else if (sqlca.sqlcode > 0)
	{
		ret = -1;
	}
	else
	{
		ret = -1;
	}
	
	return ret;
}

/* 断开数据库连接2 */
int DBDisconnect2()
{
	/* 局部sqlca说明 */
	struct sqlca sqlca;
	
	/* 返回值定义 */
	int ret;
	
	/* 释放操作 */
	EXEC SQL AT :db_link2 COMMIT WORK RELEASE;
	
	/* 判断SQL语句执行结果 */
	if (sqlca.sqlcode == 0)
	{	
		ret = 0;
	}
	else if (sqlca.sqlcode > 0)
	{
		ret = -1;
	}
	else
	{
		ret = -1;
	}

	g_DBConnected2 = 0;
	
	return ret;
}



int DBCreateMonBill(const char *TabName_IN, const char *DatePrefix_IN)
{
	struct sqlca sqlca;
	
	int ret;
	
	EXEC SQL BEGIN DECLARE SECTION;
		VARCHAR TabName[32];
		VARCHAR DatePrefix[16];
	EXEC SQL END DECLARE SECTION;
	
	memset(TabName.arr, 0, sizeof(TabName.arr));
	strncpy(TabName.arr, TabName_IN, sizeof(TabName.arr) - 1);
	TabName.len = strlen(TabName.arr);
	
	memset(DatePrefix.arr, 0, sizeof(DatePrefix.arr));
	strncpy(DatePrefix.arr, DatePrefix_IN, sizeof(DatePrefix.arr) - 1);
	DatePrefix.len = strlen(DatePrefix.arr);
	
	if(g_DBConnected != 1)
	{
		return -1;
	}
	
	EXEC SQL EXECUTE
		BEGIN
			writebill_proc(:TabName, :DatePrefix);
		END;
	END-EXEC;
	
	return 0;
}



int DBGetUserToBill(const char *TabName, BILL_ITEM *pbill_item_out)
{
	struct sqlca sqlca;
	
	int ret, nCount;
	
	EXEC SQL BEGIN DECLARE SECTION;
		VARCHAR sqlstr[512];
		VARCHAR Tel[20 + 1];
		VARCHAR City[20 + 1];
		long FeeValue;
	EXEC SQL END DECLARE SECTION;
	
	if(g_DBConnected != 1)
	{
		return -1;
	}
	
	memset(sqlstr.arr, 0, sizeof(sqlstr.arr));
	snprintf(sqlstr.arr, sizeof(sqlstr.arr) - 1, "SELECT Tel, City, FeeValue FROM %s WHERE SendFlag = 0 and UserType = 0", TabName);
	sqlstr.len = strlen(sqlstr.arr);
	
	
	EXEC SQL PREPARE s1 FROM :sqlstr;
	
	EXEC SQL DECLARE DBGetUserToBill_cursor CURSOR FOR s1;
	
	EXEC SQL OPEN DBGetUserToBill_cursor;
	
	if (sqlca.sqlcode != 0)
	{
		return -1;
	} 
	
	nCount = 0;
	while (nCount < MAX_BILL_ITEM_NUM)
	{
		memset(Tel.arr, 0, sizeof(Tel.arr));
		Tel.len = 0;
		memset(City.arr, 0, sizeof(City.arr));
		City.len = 0;
		FeeValue = 0;
		
		EXEC SQL FETCH DBGetUserToBill_cursor INTO :Tel, :City, :FeeValue;
		
		if (sqlca.sqlcode == 1403)
		{
			break;
		}
		
		Tel.arr[Tel.len] = '\0';
		City.arr[City.len] = '\0';
		
		strncpy(pbill_item_out[nCount].Tel, Tel.arr, 20);
		strncpy(pbill_item_out[nCount].City, City.arr, 20);
		pbill_item_out[nCount].FeeValue = FeeValue;
		
		nCount++;
	}
	
	EXEC SQL CLOSE DBGetUserToBill_cursor;
	
	return nCount;
}

int DBSetMonthBillOnce(const char *TabName, const char *Tel_in, const unsigned long *Seq_in)
{
	struct sqlca sqlca;
	
	int ret;
	
	EXEC SQL BEGIN DECLARE SECTION;
		VARCHAR sqlstr[512];
		VARCHAR Tel[20 + 1];
		unsigned long Seq1;
		unsigned long Seq2;
		unsigned long Seq3;
	EXEC SQL END DECLARE SECTION;
	
	memset(Tel.arr, 0, sizeof(Tel.arr));
	strncpy(Tel.arr, Tel_in, sizeof(Tel.arr) - 1);
	Tel.len = strlen(Tel.arr);
	Seq1 = Seq_in[0];
	Seq2 = Seq_in[1];
	Seq3 = Seq_in[2];
	
	memset(sqlstr.arr, 0, sizeof(sqlstr.arr));
	snprintf(sqlstr.arr, sizeof(sqlstr.arr) - 1, "UPDATE %s SET SendFlag = 1, SPSeq1 = :v1, SPSeq2 = :v2, SPSeq3 = :v3 WHERE Tel = :v4", TabName);
	sqlstr.len = strlen(sqlstr.arr);
	
		
	if(g_DBConnected != 1)
	{
		return -1;
	}
	
	EXEC SQL PREPARE s2 FROM :sqlstr;
	
	EXEC SQL EXECUTE s2 USING :Seq1, :Seq2, :Seq3, :Tel;
	
	if(sqlca.sqlcode == 0)
	{
		EXEC SQL COMMIT WORK;
		ret = 0;
	}
	else if(sqlca.sqlcode > 0)
	{
		ret = -1;
	}
	else
	{
		if(sqlca.sqlcode == -3113 || sqlca.sqlcode == -3114)
		{
			g_DBConnected = 0;
		}
		
		EXEC SQL ROLLBACK WORK;
		ret = -1;
	}
	
	return ret;
}


int DBSetReportState(const char *TabName, const char *Tel_in, const int ReportState_in, const int ReportErrorCode_in)
{
	struct sqlca sqlca;
	
	int ret;
	
	EXEC SQL BEGIN DECLARE SECTION;
		VARCHAR sqlstr[512];
		VARCHAR Tel[20 + 1];
		int ReportState;
		int ReportErrorCode;
	EXEC SQL END DECLARE SECTION;
	
	memset(Tel.arr, 0, sizeof(Tel.arr));
	strncpy(Tel.arr, Tel_in, sizeof(Tel.arr) - 1);
	Tel.len = strlen(Tel.arr);
	
	ReportState = ReportState_in;
	ReportErrorCode = ReportErrorCode_in;
	
		
	memset(sqlstr.arr, 0, sizeof(sqlstr.arr));
	snprintf(sqlstr.arr, sizeof(sqlstr.arr) - 1, "UPDATE %s SET ReportState = :v1, ReportErrorCode = :v2 WHERE Tel = :v3", TabName);
	sqlstr.len = strlen(sqlstr.arr);
	
			
	if(g_DBConnected != 1)
	{
		return -1;
	}
	
	EXEC SQL PREPARE s3 FROM :sqlstr;
	
	EXEC SQL EXECUTE s3 USING :ReportState, :ReportErrorCode, :Tel;
	
	if(sqlca.sqlcode == 0)
	{
		EXEC SQL COMMIT WORK;
		ret = 0;
	}
	else if(sqlca.sqlcode > 0)
	{
		ret = -1;
	}
	else
	{
		if(sqlca.sqlcode == -3113 || sqlca.sqlcode == -3114)
		{
			g_DBConnected = 0;
		}
		
		EXEC SQL ROLLBACK WORK;
		ret = -1;
	}
	
	return ret;
}


int DBIsSzInfoUser(const char *Tel_in)
{
	struct sqlca sqlca;
	
	int ret;
	
	EXEC SQL BEGIN DECLARE SECTION;
		VARCHAR Tel[20 + 1];
		VARCHAR UserNo[20 + 1];
	EXEC SQL END DECLARE SECTION;
	
	memset(Tel.arr, 0, sizeof(Tel.arr));
	strncpy(Tel.arr, Tel_in, sizeof(Tel.arr) - 1);
	Tel.len = strlen(Tel.arr);
	
	memset(UserNo.arr, 0, sizeof(UserNo.arr));
	UserNo.len = 0;
		
	if(g_DBConnected != 1)
	{
		return -1;
	}
	/*EXEC ORACLE OPTION (SQLCHECK = NONE);*/

	EXEC SQL SELECT userno INTO :UserNo FROM infouser WHERE userno = :Tel AND applybusiness < 4 and (deleteflag IS NULL OR deleteflag = 'n' OR deleteflag = 'N') AND TerminalTypeId = 1;
	
	/*EXEC ORACLE OPTION (SQLCHECK = SEMANTICS);*/
	
	if(sqlca.sqlcode == 0)
	{
		ret = 0;
	}
	else
	{
		if (sqlca.sqlcode == -3113 || sqlca.sqlcode == -3114)
		{
			g_DBConnected = 0;
		}
		
		ret = -1;
	}
	
	return ret;
}


int DB2IsGdInfoUser(const char *Tel_in)
{
	struct sqlca sqlca;
	
	int ret;
	
	EXEC SQL BEGIN DECLARE SECTION;
		VARCHAR Tel[20 + 1];
		VARCHAR UserNo[20 + 1];
	EXEC SQL END DECLARE SECTION;
	
	memset(Tel.arr, 0, sizeof(Tel.arr));
	strncpy(Tel.arr, Tel_in, sizeof(Tel.arr) - 1);
	Tel.len = strlen(Tel.arr);
	
	memset(UserNo.arr, 0, sizeof(UserNo.arr));
	UserNo.len = 0;
		
	if(g_DBConnected != 1)
	{
		return -1;
	}
	
	EXEC SQL AT :db_link2 SELECT userno INTO :UserNo FROM infouser WHERE userno = :Tel AND applybusiness < 4 and (deleteflag IS NULL OR deleteflag = 'n' OR deleteflag = 'N') AND TerminalTypeId = 1;
	
	if(sqlca.sqlcode == 0)
	{
		ret = 0;
	}
	else
	{
		if (sqlca.sqlcode == -3113 || sqlca.sqlcode == -3114)
		{
			g_DBConnected2 = 0;
		}
		
		ret = -1;
	}
	
	return ret;
}


int DBSetIsInfoUser(const char *TabName, const char *Tel_in)
{
	struct sqlca sqlca;
	
	int ret;
	
	EXEC SQL BEGIN DECLARE SECTION;
		VARCHAR sqlstr[512];
		VARCHAR Tel[20 + 1];
	EXEC SQL END DECLARE SECTION;
	
	memset(Tel.arr, 0, sizeof(Tel.arr));
	strncpy(Tel.arr, Tel_in, sizeof(Tel.arr) - 1);
	Tel.len = strlen(Tel.arr);
		
	if(g_DBConnected != 1)
	{
		return -1;
	}
	
	memset(sqlstr.arr, 0, sizeof(sqlstr.arr));
	snprintf(sqlstr.arr, sizeof(sqlstr.arr) - 1, "UPDATE %s SET UserType = 2 WHERE Tel = :v1", TabName);
	sqlstr.len = strlen(sqlstr.arr);
	
	if(g_DBConnected != 1)
	{
		return -1;
	}
	
	EXEC SQL PREPARE s4 FROM :sqlstr;
	
	EXEC SQL EXECUTE s4 USING :Tel;
	
	if(sqlca.sqlcode == 0)
	{
		EXEC SQL COMMIT WORK;
		ret = 0;
	}
	else if(sqlca.sqlcode > 0)
	{
		ret = -1;
	}
	else
	{
		if(sqlca.sqlcode == -3113 || sqlca.sqlcode == -3114)
		{
			g_DBConnected = 0;
		}
		
		EXEC SQL ROLLBACK WORK;
		ret = -1;
	}
	
	return ret;	
}

⌨️ 快捷键说明

复制代码 Ctrl + C
搜索代码 Ctrl + F
全屏模式 F11
切换主题 Ctrl + Shift + D
显示快捷键 ?
增大字号 Ctrl + =
减小字号 Ctrl + -