machine_id load_item
1 1
2 2
3 3
4 4
5我想通过游标嵌套遍历删除(machine_id,load_item)中最近执行的指令
即分别删除(1,1),(1,2),(1,3)......(5,3),(5,4)共20个表中最近执行的指令我写的代码如下:
void sql()
{
EXEC SQL BEGIN DECLARE SECTION;
int a;
int b;
EXEC SQL END DECLARE SECTION;EXEC SQL DECLARE bssp_machine_cursor CURSOR FOR SELECT MACHINE_ID FROM bssp_machine;
EXEC SQL OPEN bssp_machine_cursor;
EXEC SQL WHENEVER NOT FOUND DO break;EXEC SQL DECLARE bssp_batch_load_config_cursor CURSOR FOR SELECT LOAD_ITEM FROM bssp_batch_load_config;
EXEC SQL OPEN bssp_batch_load_config_cursor;
EXEC SQL WHENEVER NOT FOUND DO break;
for(;;)
{
EXEC SQL FETCH bssp_machine_cursor INTO :a; for(;;)
{
EXEC SQL FETCH bssp_batch_load_config_cursor INTO :b;
printf("a的值:%d,b的值:%d\n",a,b);
EXEC SQL DELETE FROM bssp_batch_load_log WHERE MACHINE_ID=:a and LOAD_ITEM =:b and LAST_DONE_DATE=(SELECT max(LAST_DONE_DATE) FROM bssp_batch_load_log WHERE MACHINE_ID=:a and LOAD_ITEM =:b); if(sqlca.sqlcode==0)
cout<<"删除成功!"<<endl;
else
{
cout<<"删除失败!"<<endl;
EXEC SQL ROLLBACK;
cout<<"删除失败!"<<endl;
EXEC SQL CLOSE bssp_batch_load_config_cursor;
cout<<"删除失败!"<<endl;
} }
}
EXEC SQL CLOSE bssp_batch_load_config_cursor;
EXEC SQL CLOSE bssp_machine_cursor;
}可是执行的结果却是
a的值:1,b的值:1 删除成功!
a的值:2,b的值:2 删除成功!
a的值:3,b的值:3 删除成功!
a的值:4,b的值:4 删除成功!
只删了4个表的指令,没有完成遍历
不知道如何修改,所以来请教大家下
1 1
2 2
3 3
4 4
5我想通过游标嵌套遍历删除(machine_id,load_item)中最近执行的指令
即分别删除(1,1),(1,2),(1,3)......(5,3),(5,4)共20个表中最近执行的指令我写的代码如下:
void sql()
{
EXEC SQL BEGIN DECLARE SECTION;
int a;
int b;
EXEC SQL END DECLARE SECTION;EXEC SQL DECLARE bssp_machine_cursor CURSOR FOR SELECT MACHINE_ID FROM bssp_machine;
EXEC SQL OPEN bssp_machine_cursor;
EXEC SQL WHENEVER NOT FOUND DO break;EXEC SQL DECLARE bssp_batch_load_config_cursor CURSOR FOR SELECT LOAD_ITEM FROM bssp_batch_load_config;
EXEC SQL OPEN bssp_batch_load_config_cursor;
EXEC SQL WHENEVER NOT FOUND DO break;
for(;;)
{
EXEC SQL FETCH bssp_machine_cursor INTO :a; for(;;)
{
EXEC SQL FETCH bssp_batch_load_config_cursor INTO :b;
printf("a的值:%d,b的值:%d\n",a,b);
EXEC SQL DELETE FROM bssp_batch_load_log WHERE MACHINE_ID=:a and LOAD_ITEM =:b and LAST_DONE_DATE=(SELECT max(LAST_DONE_DATE) FROM bssp_batch_load_log WHERE MACHINE_ID=:a and LOAD_ITEM =:b); if(sqlca.sqlcode==0)
cout<<"删除成功!"<<endl;
else
{
cout<<"删除失败!"<<endl;
EXEC SQL ROLLBACK;
cout<<"删除失败!"<<endl;
EXEC SQL CLOSE bssp_batch_load_config_cursor;
cout<<"删除失败!"<<endl;
} }
}
EXEC SQL CLOSE bssp_batch_load_config_cursor;
EXEC SQL CLOSE bssp_machine_cursor;
}可是执行的结果却是
a的值:1,b的值:1 删除成功!
a的值:2,b的值:2 删除成功!
a的值:3,b的值:3 删除成功!
a的值:4,b的值:4 删除成功!
只删了4个表的指令,没有完成遍历
不知道如何修改,所以来请教大家下
解决方案 »
- 求解释,这个select脚本,
- 请问:关于查询中exists与in 的效率。
- 在windows 2000 server 安装Oracle9i 企业版,但每次连接速度很慢,不知为什么?(我的机器配置很高,不应该是性能问题)
- 如何实现oracle中自增序列的重排序?
- ORA-12560: TNS: 协议适配器错误
- 英文版win2000server上可以安装简体中文版的oracle9i吗
- 怎么同时抽取不同价格的商品和收费的商品个数
- oracle初學者
- 紧急求救:有谁知道怎么样把8.0.5的数据库全库倒入8.1.7?
- Install In LInux
- 求一条sql查询,谢谢大家
- 求助 oracle 与access 实现数据互导 ~
for(;1;)//不停循环
{
EXEC SQL FETCH bssp_machine_cursor INTO :a;
when EXIT WHEN bssp_machine_cursor%NOTFOUND;//表示游标数据取完了,我不知道proc怎么写。
for(;1;)//不停循环
{
EXEC SQL FETCH bssp_batch_load_config_cursor INTO :b;
when EXIT WHEN bssp_batch_load_config_cursor%NOTFOUND;;//表示游标数据取完了,我不知道proc怎么写。 printf("a的值:%d,b的值:%d\n",a,b);
EXEC SQL DELETE FROM bssp_batch_load_log WHERE MACHINE_ID=:a and LOAD_ITEM =:b and LAST_DONE_DATE=(SELECT max(LAST_DONE_DATE) FROM bssp_batch_load_log WHERE MACHINE_ID=:a and LOAD_ITEM =:b); if(sqlca.sqlcode==0)
cout<<"删除成功!"<<endl;
else
{
cout<<"删除失败!"<<endl;
EXEC SQL ROLLBACK;
cout<<"删除失败!"<<endl;
EXEC SQL CLOSE bssp_batch_load_config_cursor;
cout<<"删除失败!"<<endl;
} }
}
if(sqlca.sqlcode == SQLNOTFOUND )
break;另外第二游标读到最后一条后需要重新打开或者让游标指针回到第一条
结果为:
a的值:1,b的值:1
删除成功!
a的值:1,b的值:2
删除成功!
a的值:1,b的值:3
删除成功!
a的值:1,b的值:4
删除成功!
a的值:2,b的值:4
删除成功!
a的值:3,b的值:4
删除成功!
a的值:4,b的值:4
删除成功!
a的值:5,b的值:4
删除成功!
for(;1;)//不停循环
{
EXEC SQL FETCH bssp_machine_cursor INTO :a;
//重新打开load_item游标
for(;1;)//不停循环
{
EXEC SQL FETCH bssp_batch_load_config_cursor INTO :b;
when EXIT WHEN bssp_batch_load_config_cursor%NOTFOUND;;//表示游标load_item数据取完了,我不知道proc怎么写。 printf("a的值:%d,b的值:%d\n",a,b);
EXEC SQL DELETE FROM bssp_batch_load_log WHERE MACHINE_ID=:a and LOAD_ITEM =:b and LAST_DONE_DATE=(SELECT max(LAST_DONE_DATE) FROM bssp_batch_load_log WHERE MACHINE_ID=:a and LOAD_ITEM =:b); if(sqlca.sqlcode==0)
cout<<"删除成功!"<<endl;
else
{
cout<<"删除失败!"<<endl;
EXEC SQL ROLLBACK;
cout<<"删除失败!"<<endl;
EXEC SQL CLOSE bssp_batch_load_config_cursor;
cout<<"删除失败!"<<endl;
}
//关闭load_item游标
}
}
for(;;)
{
EXEC SQL FETCH bssp_machine_cursor INTO :a;
EXEC SQL OPEN bssp_batch_load_config_cursor; for(;;)
{
EXEC SQL FETCH bssp_batch_load_config_cursor INTO :b;
EXEC SQL WHENEVER NOT FOUND DO break;
printf("a的值:%d,b的值:%d\n",a,b);
EXEC SQL DELETE FROM bssp_batch_load_log WHERE MACHINE_ID=:a and LOAD_ITEM =:b and LAST_DONE_DATE=(SELECT max(LAST_DONE_DATE) FROM bssp_batch_load_log WHERE MACHINE_ID=:a and LOAD_ITEM =:b); if(sqlca.sqlcode==0)
cout<<"删除成功!"<<endl;
else
{
cout<<"删除失败!"<<endl;
EXEC SQL ROLLBACK;
cout<<"删除失败!"<<endl;
EXEC SQL CLOSE bssp_batch_load_config_cursor;
cout<<"删除失败!"<<endl;
}
}
EXEC SQL CLOSE bssp_batch_load_config_cursor;
}结果为:a的值:1,b的值:1
删除成功!
a的值:1,b的值:2
删除成功!
a的值:1,b的值:3
删除成功!
a的值:1,b的值:4
删除成功!
a的值:2,b的值:1
删除成功!
a的值:3,b的值:1
删除成功!
a的值:4,b的值:1
删除成功!
a的值:5,b的值:1
删除成功!
楼主试试,希望听到你的好消息。~~~~~
for(;;)
{
EXEC SQL FETCH bssp_machine_cursor INTO :a;
EXEC SQL DECLARE bssp_batch_load_config_cursor CURSOR FOR SELECT LOAD_ITEM FROM bssp_batch_load_config;//漏了这个。
EXEC SQL OPEN bssp_batch_load_config_cursor; for(;;)
{
EXEC SQL FETCH bssp_batch_load_config_cursor INTO :b;
EXEC SQL WHENEVER NOT FOUND DO break;
printf("a的值:%d,b的值:%d\n",a,b);
EXEC SQL DELETE FROM bssp_batch_load_log WHERE MACHINE_ID=:a and LOAD_ITEM =:b and LAST_DONE_DATE=(SELECT max(LAST_DONE_DATE) FROM bssp_batch_load_log WHERE MACHINE_ID=:a and LOAD_ITEM =:b); if(sqlca.sqlcode==0)
cout<<"删除成功!"<<endl;
else
{
cout<<"删除失败!"<<endl;
EXEC SQL ROLLBACK;
cout<<"删除失败!"<<endl;
EXEC SQL CLOSE bssp_batch_load_config_cursor;
cout<<"删除失败!"<<endl;
}
}
EXEC SQL CLOSE bssp_batch_load_config_cursor;
}