贴出代码,希望各位牛人帮忙看一下,哪里有问题。另,这个东西怎么调试,可以单步执行或是断点调试啥的么create procedure procedure_test is
declare @rcode varchar2(8);
declare @rname varchar2(60);
declare @html blob;
declare @duty varchar2(240);begin declare cur1 cursor for
select * from table_test1;
open cur1;
fetch next from cur1 into @rcode, @rname, @html, @duty; WHILE @@FETCH_STATUS = 0
begin
if(select count(*) from table_test2 where rcode = @rcode)
update twriskdutyinfo set rname = cast((select rname from table_test2 a where a.rcode = @rcode) as varchar2(60)) where rcode = @rcode;
fetch next from cur2 into @rcode, @ranme, @html, @duty;
end
close cur1;
deallocate cur1;
end
说明,table_test1表中只有rcode varchar2(8),rname varchar2(60),html blob,duty varchar2(240)这四个字段;table_test2表中有rcode varchar2(8), rname varchar2(120)等其他字段。
declare @rcode varchar2(8);
declare @rname varchar2(60);
declare @html blob;
declare @duty varchar2(240);begin declare cur1 cursor for
select * from table_test1;
open cur1;
fetch next from cur1 into @rcode, @rname, @html, @duty; WHILE @@FETCH_STATUS = 0
begin
if(select count(*) from table_test2 where rcode = @rcode)
update twriskdutyinfo set rname = cast((select rname from table_test2 a where a.rcode = @rcode) as varchar2(60)) where rcode = @rcode;
fetch next from cur2 into @rcode, @ranme, @html, @duty;
end
close cur1;
deallocate cur1;
end
说明,table_test1表中只有rcode varchar2(8),rname varchar2(60),html blob,duty varchar2(240)这四个字段;table_test2表中有rcode varchar2(8), rname varchar2(120)等其他字段。
另。。存储过程可以调试,可以打断点,在pl/sql的工具中可以找到。。
plsql的语法应该怎么写……
v_rname varchar2(60);
begin
for i in (select rcode from table_test1) loop
select rname into v_rname from table_test2 a where a.rcode = i.rcode and rownum <=1;
update twriskdutyinfo set rname = NVL(v_rname,rname) where rcode = i.rcode;
end loop;
commit;
end;
为什么调用的时候总说那个select语句没有数据,我查表的时候有符合要求的对应项啊。
(我之前的东西贴错了,twriskdutyinfo 也应该是table_test1)
update test1 t set t.rname = (select rname from test2 where rcode = t.rcode)
where r.rame is not null and exists (select rname from test2 where rcode = t.rcode);