vc_oracle2.txt

在vc中创建数据库

	int ftopNum,recordNum;

	const char *chSqlInsert,*chSqlInsertSelect;

	char *tempTableName,*keyName,*keyValue;                               //！ 删除了一个未用定义*pkName

	tableName = fieldInsert_array[0].getFieldValue();

	keyName = fieldInsert_array[1].getFieldName();

	keyValue = fieldInsert_array[1].getFieldValue();

	pknum = get_PK(tableName);                //得出表中原本该有的主键数，并且把主键字段名存入pkSign
	
	for(int j=0;j<20;j++)                       //判断输入数据是否允许为空，是否溢出，并统计其中具备主键个数
	{
		const char *table_name=table_array[j][0][0];

		if(strcmp(table_name,tableName) == 0)
		{
			for(int i=1;i<fieldInsertCount;i++)                                  
			{		
				fieldName = fieldInsert_array[i].getFieldName();

				fieldValue = fieldInsert_array[i].getFieldValue();
		
				for(int k=1;k<20;k++)
				{
					const char *field_column=table_array[j][k][0];

					const char *field_length=table_array[j][k][2];

					const char *field_null=table_array[j][k][3];

					const char *field_pk=table_array[j][k][5];

					const char *field_default=table_array[j][k][4];
					
					if(strcmp(field_column,"") == 0)
					{
						break;
					}
					
					if(strcmp(field_column,fieldName) == 0)
					{
						if((strcmp(fieldValue,"") == 0)&&(strcmp(field_null,"NOT NULL") == 0))
						{
							if(strcmp(field_default,"") == 0)
							{
								printf("%s 不能为空，插入失败！",fieldName);
							
								return -1;
							}
						}
						
						if((int)strlen(fieldValue)>atoi(field_length))
						{
							printf("%s 长度溢出，插入失败！",fieldValue);

							return -1;
						}
						
						if(strcmp(field_pk,"1") == 0)
						{
							num++;
						}							

					}
				}

			}

		}

		if(strcmp(table_name,"") == 0)
		{
			break;
		}
		
	}

	sqlInsertSelect = "select * from ";

	sqlInsertSelect.append(tableName);

	sqlInsertSelect.append(" where ");

	sqlInsertSelect.append(keyName);

	sqlInsertSelect.append(" = \'");

	sqlInsertSelect.append(keyValue);

	sqlInsertSelect.append("\'");

	chSqlInsertSelect=sqlInsertSelect.c_str();

	cn = OCI_ConnPoolGetConnection(pool);

	if (cn)
	{
		st = OCI_StatementCreate(cn);
								
		OCI_ExecuteStmt(st,MT(chSqlInsertSelect));

		OCI_Commit(cn);

		rs = OCI_GetResultset(st);

		while (OCI_FetchNext(rs))
		{
		}

		n = OCI_GetRowCount(rs);

		if(n!=0)
		{
			printf("唯一键key重复，插入失败！");

			return -1;
		}

		OCI_StatementFree(st);  //释放

		OCI_ConnectionFree(cn);
	}
	
	if(pknum==num)              //主键数符合要求,全部给予时
	{
		flag = 0;	

		sqlInsertSelect = "select * from ";

		sqlInsertSelect.append(tableName);

		sqlInsertSelect.append(" where ");

		for(int i=1;i<fieldInsertCount;i++)
		{
			fieldName = fieldInsert_array[i].getFieldName();

			fieldValue = fieldInsert_array[i].getFieldValue();

			if(flag==1)
			{
				sqlInsertSelect.append(" and ");
			}
			else
			{
				flag=1;
			}
			sqlInsertSelect.append(fieldName);

			sqlInsertSelect.append(" = \'");

			sqlInsertSelect.append(fieldValue);

			sqlInsertSelect.append("\'");
		}

		chSqlInsertSelect=sqlInsertSelect.c_str();

		cn = OCI_ConnPoolGetConnection(pool);

		if (cn)
		{
			st = OCI_StatementCreate(cn);
									
			OCI_ExecuteStmt(st,MT(chSqlInsertSelect));

			OCI_Commit(cn);

			rs = OCI_GetResultset(st);

			while (OCI_FetchNext(rs))
			{
			}

			recordNum = OCI_GetRowCount(rs);

			OCI_StatementFree(st);  //释放

			OCI_ConnectionFree(cn);
		}

		if(recordNum!=0)
		{
			printf("数据库中已经存在相同的值，插入失败！");

			return -1;
		}
		else
		{
			ftopNum = get_FtoP(tableName);

			if(ftopNum==0)                                //没有关联表，可以直接进行插入操作
			{
				for(int i=1;i<fieldInsertCount;i++)
				{
					fieldName = fieldInsert_array[i].getFieldName();

					fieldValue = fieldInsert_array[i].getFieldValue();

					for(int j=0;j<20;j++)
					{
						const char *table_name=table_array[j][0][0];

						if(strcmp(table_name,"") == 0)
						{
							break;
						}

						if(strcmp(table_name,tableName) == 0)
						{
							flag=0;

							sqlInsert="insert into ";

							sqlInsert.append(tableName);

							sqlInsert.append("(");

							for(int k=1;k<fieldInsertCount;k++)
							{
								fieldName = fieldInsert_array[k].getFieldName();

								if(flag==1)
								{
									sqlInsert.append(",");
								}

								sqlInsert.append(fieldName);

								if(flag==0)
								{
									flag=1;
								}
							}

							flag=0;

							sqlInsert.append(") values (");

							for( int k=1;k<fieldInsertCount;k++)
							{
								fieldValue = fieldInsert_array[k].getFieldValue();

								if(flag==1)
								{
									sqlInsert.append(",");
								}
								else
								{
									flag=1;
								}

								sqlInsert.append("\'");

								sqlInsert.append(fieldValue);

								sqlInsert.append("\'");
												
							}
							flag=0;

							sqlInsert.append(")");

							chSqlInsert=sqlInsert.c_str();

							cn = OCI_ConnPoolGetConnection(pool);

							if (cn)
							{
								st = OCI_StatementCreate(cn);
												
								OCI_ExecuteStmt(st,MT(chSqlInsert));

								OCI_Commit(cn);

								OCI_StatementFree(st);  //释放

								fieldInsertCount = 0;

							}

							printf("成功插入一条记录！\n");

							return 0;
						}

						OCI_ConnectionFree(cn);
					}
				}
			}			

			else
			{
				char *tempField;

				flag = 0;

				for(int q=0;q<ftopNum;q++)               //查询主表中是否存在该记录值，有则可以进行插入
				{
					tempTableName = fkToPk[q][0];

					sqlInsertSelect="select * from ";

					sqlInsertSelect.append(tempTableName);

					sqlInsertSelect.append(" where ");

					for(int g=1;g<20;g++)
					{
						tempField = fkToPk[q][g];

						if(strcmp(tempField,"")==0)
						{
							break;
						}
						for(int i=1;i<fieldInsertCount;i++)
						{
							fieldName = fieldInsert_array[i].getFieldName();

							fieldValue = fieldInsert_array[i].getFieldValue();

							if(strcmp(tempField,fieldName)==0)
							{
								if(flag==1)
								{
									sqlInsertSelect.append(" and ");
								}
								else
								{
									flag=1;
								}
								sqlInsertSelect.append(fieldName);

								sqlInsertSelect.append(" = \'");

								sqlInsertSelect.append(fieldValue);

								sqlInsertSelect.append("\'");
							}
						}
					}
					chSqlInsertSelect=sqlInsertSelect.c_str();

					cn = OCI_ConnPoolGetConnection(pool);

					flag = 0;

					if(cn)
					{
						st = OCI_StatementCreate(cn);
													
						OCI_ExecuteStmt(st,MT(chSqlInsertSelect));

						OCI_Commit(cn);

						rs = OCI_GetResultset(st);

						while (OCI_FetchNext(rs))
						{
						}

						n = OCI_GetRowCount(rs);

						OCI_StatementFree(st);  //释放

						OCI_ConnectionFree(cn);
					}

					if(n==0)
					{
						printf("所要插入的值在关联表中不存在，插入失败！");

						return -1;
					}
				}
				for(int i=1;i<fieldInsertCount;i++)
				{
					fieldName = fieldInsert_array[i].getFieldName();

					fieldValue = fieldInsert_array[i].getFieldValue();

					for(int j=0;j<20;j++)
					{
						const char *table_name=table_array[j][0][0];

						if(strcmp(table_name,"") == 0)
						{
							break;
						}

						if(strcmp(table_name,tableName) == 0)
						{
							flag=0;

							sqlInsert="insert into ";

							sqlInsert.append(tableName);

							sqlInsert.append("(");

							for(int k=1;k<fieldInsertCount;k++)
							{
								fieldName = fieldInsert_array[k].getFieldName();

								if(flag==1)
								{
									sqlInsert.append(",");
								}

								sqlInsert.append(fieldName);

								if(flag==0)
								{
									flag=1;
								}
							}

							flag=0;

							sqlInsert.append(") values (");

							for( int k=1;k<fieldInsertCount;k++)
							{
								fieldValue = fieldInsert_array[k].getFieldValue();

								if(flag==1)
								{
									sqlInsert.append(",");
								}
								else
								{
									flag=1;
								}

								sqlInsert.append("\'");

								sqlInsert.append(fieldValue);

								sqlInsert.append("\'");
												
							}
							flag=0;

							sqlInsert.append(")");

							chSqlInsert=sqlInsert.c_str();

							cn = OCI_ConnPoolGetConnection(pool);

							if (cn)
							{
								st = OCI_StatementCreate(cn);
												
								OCI_ExecuteStmt(st,MT(chSqlInsert));

								OCI_Commit(cn);

								OCI_StatementFree(st);  //释放

								fieldInsertCount = 0;

							}

							printf("成功插入一条记录！\n");

							return 0;
						}

						OCI_ConnectionFree(cn);
					}
				}
			}
		}
	}
	else
	{
		printf("所要插入的信息不完整，插入失败！");
			
		return -1;
	}
}

int tables::updateF()              //如果外键引用了该记录，那么也不能进行修改
{
	bool updateReturn;

	char *tableV,*keyV,*keyN,*fieldN,*fieldV;             //！ 删除了一个未用定义*tableN

	string sqlUpdate;

	const char *chSqlUpdate;

	updateReturn = updatecheck();

	int flag = 0;

	if(updateReturn==true)
	{
		sqlUpdate = "begin ";

		for(int i=0;i<20;i++)
		{
			tableV = updatefeild[i][1][0];

			keyN = updatefeild[i][0][1];

			keyV = updatefeild[i][1][1];

			if(strcmp(tableV,"")==0)
			{
				break;
			}

			sqlUpdate.append("execute immediate \'update ");

			sqlUpdate.append(tableV);

			sqlUpdate.append(" set ");
											
			flag=0;

			for(int j=2;j<20;j++)
			{
				fieldN = updatefeild[i][0][j];

				fieldV = updatefeild[i][1][j];

				if(strcmp(fieldN,"")==0)
				{
					break;
				}

				if(flag==1)
				{
					sqlUpdate.append(",");
				}
				else
				{
					flag=1;
				}
						
				sqlUpdate.append(fieldN);

				sqlUpdate.append("=\'\'");

				sqlUpdate.append(fieldV);

				sqlUpdate.append("\'\'");			
			}

			sqlUpdate.append(" where ");

			sqlUpdate.append(keyN);

			sqlUpdate.append(" = \'\'");

			sqlUpdate.append(keyV);

			sqlUpdate.append("\'\'\';");

		}

		sqlUpdate.append(" end;");

		chSqlUpdate=sqlUpdate.c_str();

		//printf("%s",chSqlUpdate);

		cn = OCI_ConnPoolGetConnection(pool);

		if (cn)
		{
			st = OCI_StatementCreate(cn);
				
			OCI_ExecuteStmt(st,MT(chSqlUpdate));

			OCI_Commit(cn);

			OCI_StatementFree(st);  //释放

		}

		printf("成功修改了%d条记录！\n",i);

		return 0;
	}
	else
	{
		return -1;
	}

	return 0;
}


bool tables::updatecheck()
{
	string sqlUpdateSelect;                //剪切  ,sqlUpdate

	const char *chSqlUpdateSelect,*value,*pkvalue;                    //剪切 *chSqlUpdate,

	char *keyName,*keyValue,*pkname,*tempTableN,*tempField,*tempFieldValue;

	int n=0,flag,nn;

	int truePknum=0,pknum=0,fknum=0,fkToPknum=0,Unum=0,tempNum=0;

	char temp_value[128][20][20];

	char temp_key[20][128][20];

	char fkField[10][20][20];        

	char pkColumn[10][20];            //存储所有主键名称

	char pkColumnValue[10][20];       //存储所有主键值

	tableName = fieldUpdate_array[0].getFieldValue();

	keyName = fieldUpdate_array[1].getFieldName();

	keyValue = fieldUpdate_array[1].getFieldValue();

	pknum = get_PK(tableName);                    //得出表中原本该有的主键数,并且将主键名称存入pkSign数组中

	fknum = get_FK(tableName);                    //得出是否有其它表引用该表主键为外键，并存入fkSign数组中     

	fkToPknum = get_FtoP(tableName);              //得出该表是否引用其它表主键为外键，并存入fkToPk数组中

	for(int i=2;i<fieldUpdateCount;i++)
	{
		fieldName = fieldUpdate_array[i].getFieldName();

		if(strcmp(fieldName,"key")==0)
		{
			printf("key为标识，不允许进行修改，修改失败！");

			return false;
		}
	}

	flag = 0;

	sqlUpdateSelect = "select * from ";           //查询所要修改的值是否已经存在,存在即不需要进行修改

	sqlUpdateSelect.append(tableName);

	sqlUpdateSelect.append(" where ");

	for(int i=1;i<fieldUpdateCount;i++)                              
	{
		fieldName = fieldUpdate_array[i].getFieldName();

		fieldValue = fieldUpdate_array[i].getFieldValue();