急!!OCI批更新没有得到想要的结果!请大仙们帮帮忙!!! 这里贴上oci/samples下的cdemodr1.c例程,我对demo_update稍微做了下修改,其他都不变,如有表情符号,请参考cdemodr1.c源程序 1.先贴上结果 在demo_insert后得到的结果是: in4 = 400.555 + (float)i;//c4 value
结果:
C1 C4
---- ----------
1 400.554993
2 401.554993
3 402.554993
4 403.554993
5 404.554993
6 405.554993
7 406.554993
8 407.554993
9 408.554993
10 409.554993 我希望在demo_update后得到的结果是: in4 = 1401.555 + (float)i; C1 C4
---- ----------
1 1400.554993
2 1401.554993
3 1402.554993
4 1403.554993
5 1404.554993
6 1405.554993
7 1406.554993
8 1407.554993
9 1408.554993
10 1409.554993 但实际上在执行完我修改过的demo_update后,得到的结果:
C1 C4
---- ----------
1 1403.55505
2 1403.55505
3 1403.55505
4 1403.55505
5 1403.55505
6 1403.55505
7 1403.55505
8 1403.55505
9 1403.55505
10 1403.55505 2.建表脚本如下:
create table TAB1
(
c1 integer not null
c2 char(40),
c3 varchar2(40),
c4 float,
c5 decimal,
c6 decimal(8,3),
c7 numeric,
c8 numeric(7,2),
c9 date,
c10 raw(40),
constraint ind_tab1
)
organization index;
结果:
C1 C4
---- ----------
1 400.554993
2 401.554993
3 402.554993
4 403.554993
5 404.554993
6 405.554993
7 406.554993
8 407.554993
9 408.554993
10 409.554993 我希望在demo_update后得到的结果是: in4 = 1401.555 + (float)i; C1 C4
---- ----------
1 1400.554993
2 1401.554993
3 1402.554993
4 1403.554993
5 1404.554993
6 1405.554993
7 1406.554993
8 1407.554993
9 1408.554993
10 1409.554993 但实际上在执行完我修改过的demo_update后,得到的结果:
C1 C4
---- ----------
1 1403.55505
2 1403.55505
3 1403.55505
4 1403.55505
5 1403.55505
6 1403.55505
7 1403.55505
8 1403.55505
9 1403.55505
10 1403.55505 2.建表脚本如下:
create table TAB1
(
c1 integer not null
c2 char(40),
c3 varchar2(40),
c4 float,
c5 decimal,
c6 decimal(8,3),
c7 numeric,
c8 numeric(7,2),
c9 date,
c10 raw(40),
constraint ind_tab1
)
organization index;
解决方案 »
- oracle一次(一个SQL)更新多条记录
- 求一存储过程sql
- 在建立外部表之后,使用SELECT语句查询数据时,出现错误?
- 哪位大侠帮忙看看这个SQL如何写??
- 急贴 ! Linux下的Oracle中如何在Linux排程中定时删除三天前(相对于现在时间)的归档文件 .
- 将本地oracle数据导出到另一台机子的oracle里怎么做?
- 想从oracle9.2.0.1 升级到 oracle9.2.0.4(windows下)
- 请各位老大详细描述windows下SID,Instance,Service的关系!多谢了
- 插入特殊字符的处理方法
- 在oracle中如何实现单表的自我更新,比如本月的数据根据上月的数据进行update? 如何写update语句?
- 又是一个关于输出格式的问题
- Oracle支持MSSQL的left outer join(左外聯接),right outer join(右外聯接)么?
static sword demo_update(OCISvcCtx *svchp, OCIStmt *stmthp,
OCIBind *bndhp[], OCIError *errhp)
{
int i, j;
int range_size = 3; /* iterations */
/* The Update Statement with RETURNING clause */ text *sqlstmt = (text *)
"UPDATE TAB1 SET C2 = :2, C3 = :3, \
C4 = :4, C5 = :5, C6 = :6, \
C7 = :7, C8 = :8, C9 = :9, C10 = :10 \
WHERE C1= :1"; /* Prepare the statement */
if (OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4)strlen((char *)sqlstmt),
(ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT))
{
(void) printf("FAILED: OCIStmtPrepare() update\n");
report_error(errhp);
return OCI_ERROR;
} /* Initialise the buffers for insertion */
for (i = 0; i < MAXITER; i++)
{
in1 = 300 + i;
memset((void *)in2, (int) 'a'+i%26, (size_t) 40);
memset((void *)in3, (int) 'A'+i%26, (size_t) 40);
in4 = 1401.555 + (float)i;
in5 = 500 + i;
in6 = 600.280 + (float)i;
in7 = 700 + i;
in8 = 800.620 + (float)i;
in9[0] = 119;
in9[1] = 185 - (ub1)i%10;
in9[2] = (ub1)i%12 + 1;
in9[3] = (ub1)i%25 + 1;
in9[4] = 0;
in9[5] = 0;
in9[6] = 0;
for (j = 0; j < 40; j++)
in10[j] = (ub1) (i%0x08); rowsret =0;
} /* Bind all the input buffers to place holders (:1, :2. :3, etc ) */
//if (bind_input(stmthp, bndhp, errhp))
// return OCI_ERROR;
if (OCIBindByPos(stmthp, &bndhp[0], errhp, (ub4) 10,
(dvoid *) &in1[0], (sb4) sizeof(in1[0]), SQLT_INT,
(dvoid *) 0, (ub2 *)0, (ub2 *)0,
(ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)
|| OCIBindByPos(stmthp, &bndhp[1], errhp, (ub4) 1,
(dvoid *) in2[0], (sb4) sizeof(in2[0]), SQLT_AFC,
(dvoid *) 0, (ub2 *)0, (ub2 *)0,
(ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)
|| OCIBindByPos(stmthp, &bndhp[2], errhp, (ub4) 2,
(dvoid *) in3[0], (sb4) sizeof(in3[0]), SQLT_CHR,
(dvoid *) 0, (ub2 *)0, (ub2 *)0,
(ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)
|| OCIBindByPos(stmthp, &bndhp[3], errhp, (ub4) 3,
(dvoid *) &in4[0], (sb4) sizeof(in4[0]), SQLT_FLT,
(dvoid *) 0, (ub2 *)0, (ub2 *)0,
(ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)
|| OCIBindByPos(stmthp, &bndhp[4], errhp, (ub4) 4,
(dvoid *) &in5[0], (sb4) sizeof(in5[0]), SQLT_INT,
(dvoid *) 0, (ub2 *)0, (ub2 *)0,
(ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)
|| OCIBindByPos(stmthp, &bndhp[5], errhp, (ub4) 5,
(dvoid *) &in6[0], (sb4) sizeof(in6[0]), SQLT_FLT,
(dvoid *) 0, (ub2 *)0, (ub2 *)0,
(ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)
|| OCIBindByPos(stmthp, &bndhp[6], errhp, (ub4) 6,
(dvoid *) &in7[0], (sb4) sizeof(in7[0]), SQLT_INT,
(dvoid *) 0, (ub2 *)0, (ub2 *)0,
(ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)
|| OCIBindByPos(stmthp, &bndhp[7], errhp, (ub4) 7,
(dvoid *) &in8[0], (sb4) sizeof(in8[0]), SQLT_FLT,
(dvoid *) 0, (ub2 *)0, (ub2 *)0,
(ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)
|| OCIBindByPos(stmthp, &bndhp[8], errhp, (ub4) 8,
(dvoid *) in9[0], (sb4) sizeof(in9[0]), SQLT_DAT,
(dvoid *) 0, (ub2 *)0, (ub2 *)0,
(ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)
|| OCIBindByPos(stmthp, &bndhp[9], errhp, (ub4) 9,
(dvoid *) in10[0], (sb4) sizeof(in10[0]), SQLT_BIN,
(dvoid *) 0, (ub2 *)0, (ub2 *)0,
(ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT))
{
(void) printf("FAILED: OCIBindByPos()\n");
report_error(errhp);
return OCI_ERROR;
} if (OCIBindArrayOfStruct(bndhp[0], errhp, s1, indsk[0], rlsk[0], rcsk[0])
|| OCIBindArrayOfStruct(bndhp[1], errhp, s2, indsk[1], rlsk[1], rcsk[1])
|| OCIBindArrayOfStruct(bndhp[2], errhp, s3, indsk[2], rlsk[2], rcsk[2])
|| OCIBindArrayOfStruct(bndhp[3], errhp, s4, indsk[3], rlsk[3], rcsk[3])
|| OCIBindArrayOfStruct(bndhp[4], errhp, s5, indsk[4], rlsk[4], rcsk[4])
|| OCIBindArrayOfStruct(bndhp[5], errhp, s6, indsk[5], rlsk[5], rcsk[5])
|| OCIBindArrayOfStruct(bndhp[6], errhp, s7, indsk[6], rlsk[6], rcsk[6])
|| OCIBindArrayOfStruct(bndhp[7], errhp, s8, indsk[7], rlsk[7], rcsk[7])
|| OCIBindArrayOfStruct(bndhp[8], errhp, s9, indsk[8], rlsk[8], rcsk[8])
|| OCIBindArrayOfStruct(bndhp[9], errhp, s10, indsk[9], rlsk[9], rcsk[9]))
{
(void) printf("FAILED: OCIBindArrayOfStruct()\n");
report_error(errhp);
return OCI_ERROR;
}
(void) printf("\n\n DEMONSTRATING UPDATE....RETURNING \n");
if (OCIStmtExecute(svchp, stmthp, errhp, (ub4) range_size, (ub4) 0,
(CONST OCISnapshot*) 0, (OCISnapshot*) 0,
(ub4) OCI_DEFAULT))
{
(void) printf("FAILED: OCIStmtExecute() update\n");
report_error(errhp);
return OCI_ERROR;
} /* Commit the changes */
(void) OCITransCommit(svchp, errhp, (ub4) 0); return OCI_SUCCESS;
}
ub4 mode;
OCIServer *srvhp;
OCIError *errhp;
OCISvcCtx *svchp;
{
text *cstring = (text *)"dbsrv1";
把cstring数据库服务名修改本地的服务名即可调试
源程序是ora92/oci/samples/cdemodr1.c我已经自己调出来了
发现变量绑定次序要跟sql语句中域的选择次序一致才行
现把demo_update贴出来供有需求的参考static sword demo_update(OCISvcCtx *svchp, OCIStmt *stmthp,
OCIBind *bndhp[], OCIError *errhp)
{
int i, j;
int range_size = 1; /* iterations */
/* The Update Statement with RETURNING clause */
text *sqlstmt = (text *)
"UPDATE TAB1 SET C1 = C1 + :1, C2 = :2, C3 = :3, \
C4 = C4 + :4, C5 = C5 + :5, C6 = C6 + :6, \
C7 = C7 + :7, C8 = C8 + :8, C9 = :9, C10 = :10";
/* Prepare the statement */
if (OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4)strlen((char *)sqlstmt),
(ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT))
{
(void) printf("FAILED: OCIStmtPrepare() update\n");
report_error(errhp);
return OCI_ERROR;
} /* Initialise the buffers for insertion */
for (i = 0; i < MAXITER; i++)
{
in1[i] = 300 + i;
memset((void *)in2[i], (int) 'a'+i%26, (size_t) 40);
memset((void *)in3[i], (int) 'A'+i%26, (size_t) 40);
in4[i] = 400.555 + (float)i;
in5[i] = 500 + i;
in6[i] = 600.280 + (float)i;
in7[i] = 700 + i;
in8[i] = 800.620 + (float)i;
in9[i][0] = 119;
in9[i][1] = 185 - (ub1)i%10;
in9[i][2] = (ub1)i%12 + 1;
in9[i][3] = (ub1)i%25 + 1;
in9[i][4] = 0;
in9[i][5] = 0;
in9[i][6] = 0;
for (j = 0; j < 40; j++)
in10[i][j] = (ub1) (i%0x08); rowsret[i] =0;
} /* Bind all the input buffers to place holders (:1, :2. :3, etc ) */
if (bind_input(stmthp, bndhp, errhp))
return OCI_ERROR; (void) printf("\n\n DEMONSTRATING UPDATE....RETURNING \n");
if (OCIStmtExecute(svchp, stmthp, errhp, (ub4) range_size, (ub4) 0,
(CONST OCISnapshot*) 0, (OCISnapshot*) 0,
(ub4) OCI_DEFAULT))
{
(void) printf("FAILED: OCIStmtExecute() update\n");
report_error(errhp);
return OCI_ERROR;
} /* Commit the changes */
(void) OCITransCommit(svchp, errhp, (ub4) 0); /* Print out the values in the return rows */
(void) print_return_data(range_size); return OCI_SUCCESS;
}
---- ----------
1 400.554993
2 401.554993
更新为
C1 C4
---------- ----------
301 801.109985
302 802.109985