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