我是想把一张表里的多条记录的三个字段修改为另一张表里多条记录对应的三个字段,但是结果却是被修改的表三个字段多条记录中都是一模一样的。存储过程编译没有问题,游标却在运行时未有移向下条记录,为什么呢?程序如下:
create or replace procedure cust_update is
cursor v_cur is
select cust_id,cust_st_child_group_id,cust_st_group_id
from o_user_cons where cust_id not in
(select cust_id from o_user_cons group by cust_id having count(cust_id)>=2);
v_rownum number;
v_cust_id varchar(20);
v_cust_st_child_group_id number;
v_cust_st_group_id number;begin
v_rownum:=1;
open v_cur;
loop
fetch v_cur into v_cust_id,v_cust_st_child_group_id,v_cust_st_group_id;
v_cust_st_group_id:=v_cust_st_group_id/100;
if v_rownum<=300
then update cust
set cust_id=v_cust_id,stratagem_child_group_id=v_cust_st_child_group_id,
stratagem_group_id=v_cust_st_group_id;
commit;
v_rownum:=v_rownum+1;
else exit;
end if;
end loop;
close v_cur;
end cust_update;
create or replace procedure cust_update is
cursor v_cur is
select cust_id,cust_st_child_group_id,cust_st_group_id
from o_user_cons where cust_id not in
(select cust_id from o_user_cons group by cust_id having count(cust_id)>=2);
v_rownum number;
v_cust_id varchar(20);
v_cust_st_child_group_id number;
v_cust_st_group_id number;begin
v_rownum:=1;
open v_cur;
loop
fetch v_cur into v_cust_id,v_cust_st_child_group_id,v_cust_st_group_id;
v_cust_st_group_id:=v_cust_st_group_id/100;
if v_rownum<=300
then update cust
set cust_id=v_cust_id,stratagem_child_group_id=v_cust_st_child_group_id,
stratagem_group_id=v_cust_st_group_id;
commit;
v_rownum:=v_rownum+1;
else exit;
end if;
end loop;
close v_cur;
end cust_update;
解决方案 »
- 关于动态SQL的问题
- 高分求sql insert,update语句。分销存行业应用,插入销售数据并将退货数据改成销售,销售改退货。
- oracle 自动备份 究竟是怎么一回事
- 导出的dmp只有20M,导入后消耗了几十G硬盘,怎么处理?
- 怎样设置每天凌晨2:00执行job?
- 请教一个简单的问题.oralce中字符串截止函数substr的用法。因手头上没有这方面的资料,现要急用。知道后马上结贴!
- 如下过程在sql *plus里如何调用执行啊?谢谢!
- Oralce在P4上安装,需要修改那些文件?
- 如何在delphi程序中运行oracle的sql脚本文件?
- 大神请进,有关于oracle中||拼接字符不拼接问题请教
- oracle 数据库更改、插入数记录失败
- 如何drop掉指定用户下的某个表
--表cust所有记录都将被更新为最后一次fetch取出的值
UPDATE cust
SET cust_id = v_cust_id,
stratagem_child_group_id = v_cust_st_child_group_id,
stratagem_group_id = v_cust_st_group_id;
另外,可能一个sql就能实现,形如
update cust set (col1,col2,col3)=(select b.col1,b.col2,b.col3 from b where ...)
where ...
条件是
set cust_id=v_cust_id,stratagem_child_group_id=v_cust_st_child_group_id,
stratagem_group_id=v_cust_st_group_id
where rownum=v_rownum;看来用oracle自带的rownum不行
应该用什么方法来修改表,达到我所说的目的呢?谢谢了
loop
fetch v_cur into v_cust_id,v_cust_st_child_group_id,v_cust_st_group_id; --这里,如果游标没获取到时数据,该跳出循环吧?
--+: exit when v_cur%not_found; v_cust_st_group_id:=v_cust_st_group_id/100; --下面这段,我没看出来你想干啥。
if v_rownum<=300
then update cust
set cust_id=v_cust_id,stratagem_child_group_id=v_cust_st_child_group_id,
stratagem_group_id=v_cust_st_group_id;
commit;
v_rownum:=v_rownum+1;
else exit;
end if; end loop;
close v_cur;