vc_oracle2.txt

在vc中创建数据库

								strcpy(updatefeild[updnum][0][1],"key"); 

								strcpy(updatefeild[updnum][1][1],tempField);

								q=2;

								for(int h=1;h<20;h++)
								{
									fkFieldName = fkSign[i][h];

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

									for(int i=0;i<pknum;i++)   
									{						
										pkname = pkSign[i];

										tempField = pkColumnValue[i];

										if(strcmp(fkFieldName,pkname)==0)
										{
											strcpy(updatefeild[updnum][0][q],pkname);                      //updnum表示有多少条记录要改，此处为一

											strcpy(updatefeild[updnum][1][q],tempField);

											q++;

										}
									}
								}											

								updnum++;
							}
						}
					}								
				}
				return true;
			}
		}		
	}
}

int tables::deleteF()                       //删除有缺陷，只能对没有外键关联的数据进行删除，有关联的不能删
{
	bool delreturn;

	string sqlDelete;

	const char *chSqlDelete;
	
	char *key_value; 

	delreturn = delcheck();

	if(delreturn ==true)
	{
		for(int i=0;i<1024;i++)
		{
			key_value = strdup(keyValue[i]);

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

			sqlDelete="delete from ";

			sqlDelete.append(tableName);

			sqlDelete.append(" where key = \'");

			sqlDelete.append(key_value);

			sqlDelete.append("\'");

			chSqlDelete = sqlDelete.c_str();

			cn = OCI_ConnPoolGetConnection(pool);

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

				OCI_Commit(cn);	

				rs = OCI_GetResultset(st);
			}
		}

		printf("有%d条记录被成功删除\n",i);

		return 0;
	}
	else
	{
		printf("删除失败！");

		return -1;
	}
}	

bool tables::delcheck()
{
	string sqlDeleteSelect,sqlPkSelect,sqlDelSelect;

	const char *chSqlDeleteSelect,*chSqlPkSelect,*chSqlDelSelect;

	char deleteKey[1024][20];

	char key_value[1024][20];

	int flag = 0,n = 0,num = 0;

	int get_num = fieldDeleteCount - 1;

	char *tempTableName,*tempFieldName,*tempFkColum;

	cn = OCI_ConnPoolGetConnection(pool);

	memset( key_value, '\0', sizeof(key_value));

	memset( pkvalue, '\0', sizeof(pkvalue));

	memset( deleteKey, '\0', sizeof(deleteKey));

	memset( keyValue, '\0', sizeof(keyValue));

	tableName = fieldDelete_array[0].getFieldValue(); 

	sqlDeleteSelect = "select key from ";              //搜索表中有无可删除的记录

	sqlDeleteSelect.append(tableName);

	sqlDeleteSelect.append(" where ");

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

		fieldValue = fieldDelete_array[i].getFieldValue();

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

		sqlDeleteSelect.append(fieldName);

		sqlDeleteSelect.append(" = \'");

		sqlDeleteSelect.append(fieldValue);

		sqlDeleteSelect.append("\'");
	}

	chSqlDeleteSelect = sqlDeleteSelect.c_str();
		
	if (cn)
	{
		st = OCI_StatementCreate(cn);
			
		OCI_ExecuteStmt(st,MT(chSqlDeleteSelect));

		OCI_Commit(cn);	

		rs = OCI_GetResultset(st);

		while (OCI_FetchNext(rs))                    //循环存储表中主键纪录的值
		{
				const char *value;

				value = OCI_GetString(rs,1);                  

				strcpy(key_value[trueKeyNum],value);

				trueKeyNum++;
		}

		n  = OCI_GetRowCount(rs);
	}

	if(n==0)
	{
		return false;
	}
	else                                                         //有可删除的记录时
	{		
		int fkSignNum = 0,pkSignNum = 0,addKeyNum = 0,selectNum = 0;
		
		fkSignNum = get_FK(tableName);                          //要删除的表 在fkSignNum个表中作为外键

		pkSignNum = get_PK(tableName);                          //要删除的表中存在pkSignNum个主键

		//colNum=fieldDeleteCount - 1;                         //colNum为除去表名列的总列数

		addKeyNum = pkSignNum+1;

		if(fkSignNum == 0)                        //该表不存在字段为他表的外键时返回可直接删除
		{
			for(int i=0;i<1024;i++)
			{
				char *keyV;

				keyV=strdup(key_value[i]);

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

				strcpy(keyValue[i],keyV);

			}
			return true;
		}
		else                                                        //该表存在字段为他表的外键时需要进行判断
		{
			char *pkname,*fkname;

			int truepknum = 0;

			flag = 0;

			for(int p=1;p<fieldDeleteCount;p++) 
			{
				fieldName = fieldDelete_array[p].getFieldName();

				for(int q=0;q<pkSignNum;q++)
				{
					pkname = strdup(pkSign[q]);

					if(strcmp(fieldName,pkname)==0)
					{
						truepknum++;
					}
				}
			}
			if(truepknum==pkSignNum)                                          //当主键都给齐全了时,即只存在一条需要删除的记录时
			{
				for(int j=0;j<fkSignNum;j++)                          
				{
					tempTableName=fkSign[j][0];

					sqlDeleteSelect = "select * from ";

					sqlDeleteSelect.append(tempTableName);

					sqlDeleteSelect.append(" where ");

					for(int k=1;k<20;k++)
					{
						fkname = strdup(fkSign[j][k]);

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

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

							fieldValue = fieldDelete_array[i].getFieldValue();

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

								sqlDeleteSelect.append(" =\'");

								sqlDeleteSelect.append(fieldValue);

								sqlDeleteSelect.append("\'");
							}
						}
					}
					chSqlDeleteSelect = sqlDeleteSelect.c_str();
		
					cn = OCI_ConnPoolGetConnection(pool);

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

						OCI_Commit(cn);	

						rs = OCI_GetResultset(st);

						while (OCI_FetchNext(rs))
						{
						}

						n  = OCI_GetRowCount(rs);

						flag=0;

						if(n!=0)
						{
							return false;
						}
					}				
				}

				for(int i=0;i<1024;i++)
				{
					char *keyV;

					keyV=strdup(key_value[i]);

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

					strcpy(keyValue[i],keyV);
				}
				return true;
			}

			if(truepknum<pkSignNum)                                            //当没有给齐全pk键时,即可能存在多条需要删除的记录时
			{
				sqlPkSelect = "select key,";

				for(int y=0;y<pkSignNum;y++)
				{
					tempFieldName = pkSign[y];

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

					sqlPkSelect.append(tempFieldName);

				}

				sqlPkSelect.append(" from ");

				sqlPkSelect.append(tableName);

				sqlPkSelect.append(" where ");

				flag = 0;

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

					fieldValue = fieldDelete_array[i].getFieldValue();

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

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

					sqlPkSelect.append(fieldName);

					sqlPkSelect.append(" = \'");

					sqlPkSelect.append(fieldValue);

					sqlPkSelect.append("\'");					
				}

				chSqlPkSelect = sqlPkSelect.c_str();
			
				cn = OCI_ConnPoolGetConnection(pool);

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

					OCI_Commit(cn);	

					rs = OCI_GetResultset(st);

					int c=0;

					while (OCI_FetchNext(rs))                    //循环存储表中主键纪录的值
					{
						const char *value;

						int d=0;

						for(int b=1;b<=addKeyNum;b++)
						{
							value = OCI_GetString(rs,b);                  

							d = b-1;

							strcpy(pkvalue[c][d],value);
						}

						c++;
					}

					selectNum  = OCI_GetRowCount(rs);				
				}					

				for(int f=0;f<selectNum;f++)
				{					
					char *pkTrueValue,*del_key;

					int add_num;

					int nn = 0;

					for(int j=0;j<fkSignNum;j++)
					{
						sqlDelSelect = "select * from ";             //搜寻出关联的外键，并将关联外键的对应本表key存入为不可删除的值

						flag = 0;

						tempTableName=fkSign[j][0];

						del_key = strdup(pkvalue[f][0]);

						sqlDelSelect.append(tempTableName);

						sqlDelSelect.append(" where ");

						for(int x=1;x<20;x++)
						{
							tempFkColum = fkSign[j][x];

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

							for(int y=0;y<pkSignNum;y++)
							{
								add_num = y+1;

								tempFieldName = pkSign[y];

								pkTrueValue=strdup(pkvalue[f][add_num]);

								if(strcmp(tempFieldName,tempFkColum) == 0)
								{
									if(flag==1)
									{
										sqlDelSelect.append(" and ");
									}
									else
									{
										flag=1;
									}

									sqlDelSelect.append(tempFieldName);

									sqlDelSelect.append(" = \'");

									sqlDelSelect.append(pkTrueValue);

									sqlDelSelect.append("\'");	
								}				
							}						
						}
						chSqlDelSelect = sqlDelSelect.c_str();
			
						cn = OCI_ConnPoolGetConnection(pool);

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

							OCI_Commit(cn);	

							rs = OCI_GetResultset(st);

							while (OCI_FetchNext(rs))
							{
							}

							nn  = OCI_GetRowCount(rs);

							if(nn!=0)
							{	
								strcpy(deleteKey[deleteNum],del_key);                     //将不可以删除的纪录的key放入数组中

								deleteNum++;	
							}
						}
					}
				}
				int def = 0,q=0;

				char *delvalues,*keyvalues;	

				int response;

				if(deleteNum!=0)                                               //不可删除的数据存在     
				{
					for(int p=0;p<trueKeyNum;p++)                              //不进行级联删除时，取出两个数组中的不同值对比在进行删除。
					{
						printf("您确定要全部删除该数据吗？（包括它表关联引用）。请返回1/0确认,按回车键结束！yes--1,no--0\n");

						scanf("%d",&response);

						if (response == 1)                                         //不管外键关联删除
						{
							char *del_key;

							int q = 0;

							for(int f=0;f<selectNum;f++)
							{
								del_key = strdup(pkvalue[f][0]);

								strcpy(keyValue[q],del_key);

								q++;
							}

							return true;
						}

						if(response == 0)                                     //只删除可删除的数据，外键关联数据不进行删除
						{
							for(int g=0;g<deleteNum;g++)                           //？还有问题
							{
								delvalues = strdup(deleteKey[g]);

								keyvalues = strdup(key_value[p]);

								if(strcmp(delvalues,keyvalues)!=0)
								{
									def = 0;
								}
								else
								{
									def = 1;

									break;
								}

								def =def || 0;
							}
							if(def==0)
							{
								strcpy(keyValue[q],keyvalues);
								
								q++;
							}
							def = 0;
																				
						}
						
						return true;

					}
				}
				else                                                   //不可删除的数据不存在
				{
					char *del_key;

					int q = 0;

					for(int f=0;f<selectNum;f++)
					{
						del_key = strdup(pkvalue[f][0]);

						strcpy(keyValue[q],del_key);

						q++;
					}

					return true;
				}				
			}				
		}
	}
}

int tables::get_FK(char *tableN)
{
	int fkSignNum = 0;

	memset( fkSign, '\0', sizeof(fkSign));

	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,tableN) != 0)
		{
			for(int k=1;k<20;k++)              
			{
				const char *fk_sign=table_array[j][0][k];

				if(strcmp(fk_sign,"") == 0)
				{
					break;
				}
				else
				{
					char *pre = 0;

					char *result = 0;								
								
					pre= strdup(fk_sign);

					char *temp = strstr(pre,".");

					result=temp+1;

					temp[0]='\0';

					if(strcmp(pre,tableN) == 0)
					{
						strcpy(fkSign[fkSignNum][0],Table_Name);	

						int k=1;
						
						while