?? vc_oracle2.txt
字號:
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();
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -