vc_oracle2.txt

在vc中创建数据库

#include "tables.h"

#include "string.h"

extern OCI_ConnPool *pool ;


static OCI_Connection *cn = NULL;
static OCI_Statement  *st = NULL;
static OCI_Resultset  *rs = NULL;


tables::tables(void)
{
	deleteNum = 0;

	trueKeyNum = 0;

	tableName = 0;

	fieldInsertCount = 0;

	fieldUpdateCount = 0;

	fieldDeleteCount = 0;
}

tables::~tables(void)
{
}

int tables::setTableName(char * tName)
{
	if(!tName || !*tName) return -1;

	if(tableName)
	{
		free(tableName);
	}

	tableName = strdup(tName);

	printf("tableName=%s\n",tableName);

	return 0;

}

char tables::getTableName()
{
	return *tableName;
}

int tables::creates()
{
	OCI_Statement  *st1 = NULL;

	OCI_Statement  *st2 = NULL;

	OCI_Statement  *st3 = NULL;

	OCI_Statement  *st4 = NULL;

	char sqlc[2048] = {0};

	char sqlpk[2048] = {0};

	char sqlfk[2048] = {0};

	char sqluk[2048] = {0};

	string sqlIndex;

	const char *chSqlIndex;

	int flag = 0,tablenum = 0,pknum = 0,fknum = 0,uniquenum = 0;

	strcat(sqlc,"begin ");

    for(int x=0;x<20;x++)                                //新建表，并给出数据类型、数据大小和是否为空的信息
	{
        if(table_array[x][0][0])
		{
			const char *table_name=table_array[x][0][0];

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

			strcat(sqlc,"execute immediate \'create table ");

			strcat(sqlc,table_name);

			strcat(sqlc,"(");

			for(int y=1;y<20;y++)
			{
				const char *table_column=table_array[x][y][0];

				if(strcmp(table_column,"") == 0)
				{
					strcat(sqlc,")");

					break;
				}
				else
				{
					if(flag==1)
					{
						strcat(sqlc,",");
					}

					strcat(sqlc,table_column);
				}

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

                for(int i=1;i<5;i++)
				{	
					if(table_array[x][y][i])
					{
						const char *table_feild=table_array[x][y][i];

						if(strcmp(table_feild," ") == 0)
						{
							continue;
						}

						if(i==2)
						{
							strcat(sqlc,"(");

							strcat(sqlc,table_feild);

							strcat(sqlc,")");

							continue;
						}

						if(strcmp(table_feild,"NOT NULL")==0)
						{
							const char *table_default=table_array[x][y][4];

							if(strcmp(table_default,"")!=0)
							{
								continue;
							}

						}

						if(strcmp(table_feild,"NULL")==0)
						{
							const char *table_default=table_array[x][y][4];

							if(strcmp(table_default,"")!=0)
							{
								printf("创建失败,设置默认值的字段必须为非空！");

								return -1;
							}

						}

						if(i==4)
						{
							if(strcmp(table_feild,"")!=0)
							{
								strcat(sqlc," default \''");

								strcat(sqlc,table_feild);

								strcat(sqlc,"\''");

								continue;
							}
						}

						strcat(sqlc," ");

						strcat(sqlc,table_feild);
						
					}
				}
			}
		}
		strcat(sqlc,"\'; ");

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

	strcat(sqlc,"end;");

	tablenum = x;

	cn = OCI_ConnPoolGetConnection(pool);
   
	strcat(sqlpk,"begin ");

	int y;

    for(x=0;x<20;x++)                   //为表添加主键约束
	{
		if(table_array[x][0][0])
		{
			const char *table_name=table_array[x][0][0];

			if(strcmp(table_array[x][0][0],"") == 0)
			{
				break;
			}
			strcat(sqlpk,"execute immediate 'alter table ");

			strcat(sqlpk,table_name);

			strcat(sqlpk," ");

			for(y=1;y<20;y++)
			{
				const char *table_column=table_array[x][y][0];

				const char *table_feild=table_array[x][y][5];

				if(strcmp(table_feild,"") == 0)
				{
					continue;

				}

				if(strcmp(table_feild,"1") == 0)
				{
					strcat(sqlpk,"add constraint ");

					strcat(sqlpk,"\"PK_");

					strcat(sqlpk,table_name);

					strcat(sqlpk,"\" primary key(");

					strcat(sqlpk,table_column);

					int k;

					for(k=y+1;k<20;k++)
					{
						if(strcmp(table_array[x][k][5],"1") == 0)
						{
							strcat(sqlpk,",");

							strcat(sqlpk,table_array[x][k][0]);

						}

					}

					strcat(sqlpk,")");

					break;
				}
			}

				strcat(sqlpk,"\'; ");

		}
	}

	strcat(sqlpk,"end;");

	pknum = x;
	
	strcat(sqlfk,"begin ");

    for(x=0;x<20;x++)                 //*为表添加外键约束
	{
		if(table_array[x][0][0])
		{
			const char *table_name=table_array[x][0][0];

			char *pre=NULL;

			char fk[20][20];

			char *last=NULL;

			char pos[200];

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

			for(int r=1;r<=20;r++)
			{
				const char *fk_name=table_array[x][0][r];

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

				int k=0;

				strcpy(pos,fk_name);

				pre= strtok(pos,".");

				while(pre!= NULL)
				{
					strcpy(fk[k],pre);
					
					k++;

					pre = strtok( NULL, "." );

				}

				for(int j=0;j<20;j++)
				{
					int len;

					const char *t_name=table_array[j][0][0];

					len=k-1;

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

					int n;

					if(strcmp(t_name,fk[0]) == 0)       //表名相同时，对字段进行比照。
					{
						for(n=len;n>0;n--)
						{
							for(int i=1;i<20;i++)
							{
								const char *t_column=table_array[j][i][0];

								if(strcmp(t_column,fk[n]) == 0)
								{
									len--;

									break;
								}
							}
						}

						if(len==0)             //所有字段比照相同时，添加SQL语句。
						{
							strcat(sqlfk,"execute immediate \'alter table ");

							strcat(sqlfk,table_name);

							strcat(sqlfk," ");

							strcat(sqlfk,"add constraint ");

							strcat(sqlfk,"\"FK_");

							strcat(sqlfk,table_name);

							strcat(sqlfk,"_");

							strcat(sqlfk,fk[0]);

							strcat(sqlfk,"\" foreign key(");

							flag=0;

							for(n=k-1;n>0;n--)
							{
								if(flag==1)
								{
									strcat(sqlfk,",");
								}

								strcat(sqlfk,fk[n]);

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

							flag=0;

							strcat(sqlfk,")");

							strcat(sqlfk," references ");

							strcat(sqlfk,fk[0]);

							strcat(sqlfk,"(");

							for(n=k-1;n>0;n--)
							{
								if(flag==1)
								{
									strcat(sqlfk,",");
								}

								strcat(sqlfk,fk[n]);

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

							}
							strcat(sqlfk,")");            //剪切了disable——strcat(sqlfk,") disable\'; "); 

							strcat(sqlfk," on delete cascade ");                     //添加级联删除

							strcat(sqlfk," initially deferred deferrable ");         //添加提交延迟，用于级联修改

							strcat(sqlfk,"\'; "); 

						}
						else 
							printf("连接表中没有相应的列，外键创建失败！");
					}
				}
			}				
		}	
	}	

	strcat(sqlfk,"end;");

	fknum = x;

	//printf("SQL->%s\n", sqlfk);

	//printf("\n");

	strcat(sqluk,"begin ");

	for(x=0;x<20;x++)                   //为表添加唯一键约束
	{
		if(table_array[x][0][0])
		{
			const char *table_name=table_array[x][0][0];

			if(strcmp(table_array[x][0][0],"") == 0)
			{
				break;
			}
			strcat(sqluk,"execute immediate 'alter table ");

			strcat(sqluk,table_name);

			strcat(sqluk," ");

			for(y=1;y<20;y++)
			{
				const char *table_column=table_array[x][y][0];

				const char *table_feild=table_array[x][y][5];

				if(strcmp(table_feild,"") == 0)
				{
					continue;

				}

				if(strcmp(table_feild,"3") == 0)
				{
					strcat(sqluk,"add constraint ");

					strcat(sqluk,"\"UK_");

					strcat(sqluk,table_name);

					strcat(sqluk,"\" unique (");

					strcat(sqluk,table_column);

					int k;

					for(k=y+1;k<20;k++)
					{
						if(strcmp(table_array[x][k][5],"3") == 0)
						{
							strcat(sqluk,",");

							strcat(sqluk,table_array[x][k][0]);
						}

					}

					strcat(sqluk,")");

					break;
				}
			}

				strcat(sqluk,"\'; ");

		}
	}

	strcat(sqluk,"end;");

	uniquenum = x;
	
	//printf("SQL->%s\n", sqlc);

	printf("\n");   

	if (cn)
    {
        st1 = OCI_StatementCreate(cn);

		OCI_ExecuteStmt(st1,MT(sqlc));

		OCI_Commit(cn);

		OCI_StatementFree(st1);  //释放

		printf("有%d个表成功创建！\n",tablenum);

    }

	if (cn)
    {
        st2 = OCI_StatementCreate(cn);

		OCI_ExecuteStmt(st2,MT(sqlpk));

		OCI_Commit(cn);

		OCI_StatementFree(st2);  //释放

		printf("有%d个主键成功创建！\n",pknum);
    }

	if (cn)
    {
        st3 = OCI_StatementCreate(cn);

		OCI_ExecuteStmt(st3,MT(sqlfk));

		OCI_Commit(cn);

		OCI_StatementFree(st3);  //释放

		printf("有%d个外键键成功创建！\n",pknum);

    }

	if (cn)
    {
        st4 = OCI_StatementCreate(cn);

		OCI_ExecuteStmt(st4,MT(sqluk));

		OCI_Commit(cn);

		OCI_StatementFree(st4);  //释放

		printf("有%d个唯一键键键成功创建！\n",pknum);

    }
    
	for(x=0;x<20;x++)                               //创建索引
	{
		const char *table_name=table_array[x][0][0];

		if(strcmp(table_name,"")==0)
		{
			break;
		}
		
		for(int y=1;y<20;y++)
		{
			const char *field_column=table_array[x][y][0];

			const char *field_key=table_array[x][y][5];

			const char *field_index=table_array[x][y][6];

			if(strcmp(field_column,"")==0)
			{
				break;
			}
			if((strcmp(field_index,"1")==0)||(strcmp(field_index,"0")==0))
			{
				if((strcmp(field_key,"1")==0)||(strcmp(field_key,"3")==0))
				{
					printf("%s表的%s字段索引创建失败,该字段为主键或唯一键，索引冲突！\n",table_name,field_column); 

					dropTable();                                  //添加一个删除所建表的函数  外键关系删除

					return -1;
				}

				sqlIndex = "create index "; 

				sqlIndex.append("IDX_");

				sqlIndex.append(table_name);

				sqlIndex.append("_");

				sqlIndex.append(field_column);

				sqlIndex.append(" on ");

				sqlIndex.append(table_name);

				sqlIndex.append("(");

				sqlIndex.append(field_column);

				if(strcmp(field_index,"0")==0)
				{
					sqlIndex.append(" desc");
				}

				sqlIndex.append(")");

				chSqlIndex=sqlIndex.c_str();

				cn = OCI_ConnPoolGetConnection(pool);

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

					OCI_Commit(cn);

					OCI_StatementFree(st);  //释放

				}
			}			
		}
	}

	printf("索引成功创建！\n");

	OCI_ConnectionFree(cn);

	return EXIT_SUCCESS;

}

int tables::insertF()     //插入操作！
{
	string sqlInsertSelect,sqlInsert;

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