一直用sqlserver,没有用过oracle,现在写了个存储过程,用两层游标,语法有问题?还是oracle不能这么写?
代码;
create or replace procedure updatetime is v_jtdid NUMBER;
v_audittaskid VARCHAR2(10);cursor cursor1 is select * from (select jd.jtdid from tz_jobtypedetail jd where jd.jtid = 1001 order by jd.orderno ) where rownum < 5 ;
beginopen cursor1;
fetch cursor1 into v_jtdid;
while cursor1%found loop
begin
CURSOR cursor2 is select * from ( select a.audittaskid,a.creattime,a.orderdate,a.releasedate from tz_audittaskrelease a where a.categoryid = v_jtdid order by trunc(dbms_random.value(0, 1000))) where rownum < 13;begin
open cursor2;
fetch cursor2 into v_audittaskid;
while cursor2%found loop
begin
update tz_audittaskrelease set creattime = sysdate ,orderdate = sysdate ,releasedate = sysdate where audittaskid = v_audittaskid;
end
fetch cursor2 into v_audittaskid;
end loop;
close cursor2;
end;
fetch cursor1 into v_jtdid;
end;
end loop;close cursor1;end;
代码;
create or replace procedure updatetime is v_jtdid NUMBER;
v_audittaskid VARCHAR2(10);cursor cursor1 is select * from (select jd.jtdid from tz_jobtypedetail jd where jd.jtid = 1001 order by jd.orderno ) where rownum < 5 ;
beginopen cursor1;
fetch cursor1 into v_jtdid;
while cursor1%found loop
begin
CURSOR cursor2 is select * from ( select a.audittaskid,a.creattime,a.orderdate,a.releasedate from tz_audittaskrelease a where a.categoryid = v_jtdid order by trunc(dbms_random.value(0, 1000))) where rownum < 13;begin
open cursor2;
fetch cursor2 into v_audittaskid;
while cursor2%found loop
begin
update tz_audittaskrelease set creattime = sysdate ,orderdate = sysdate ,releasedate = sysdate where audittaskid = v_audittaskid;
end
fetch cursor2 into v_audittaskid;
end loop;
close cursor2;
end;
fetch cursor1 into v_jtdid;
end;
end loop;close cursor1;end;
把CURSOR cursor2 is select * from 的定义拿到Cursor1和begin之间定义。
create or replace procedure updatetime is v_jtdid NUMBER;
v_audittaskid VARCHAR2(10); cursor cursor1 is
select *
from (select jd.jtdid
from tz_jobtypedetail jd
where jd.jtid = 1001
order by jd.orderno)
where rownum < 5;
CURSOR cursor2(in_jtdid varchar2) is
select *
from (select a.audittaskid
from tz_audittaskrelease a
where a.categoryid = in_jtdid
order by trunc(dbms_random.value(0, 1000)))
where rownum < 13;begin open cursor1;
fetch cursor1
into v_jtdid;
while cursor1%found loop
begin
begin
open cursor2(v_jtdid);
fetch cursor2
into v_audittaskid;
while cursor2%found loop
begin
update tz_audittaskrelease
set creattime = sysdate,
orderdate = sysdate,
releasedate = sysdate
where audittaskid = v_audittaskid;
commit;
end;
fetch cursor2 into v_audittaskid;
end loop;
close cursor2;
end;
fetch cursor1
into v_jtdid;
end;
end loop;
close cursor1;
end;
这样做了还是报错了呀,
报错行
fetch cursor2 into v_audittaskid;
第二个cursor的fetch都报错呀
错误:PLS-00394: 在 FETCH 语句的 INTO 列表中值数量出现错误
行:17
文本:fetch cursor2 into v_audittaskid;错误:PL/SQL: SQL Statement ignored
行:17
文本:fetch cursor2 into v_audittaskid;错误:PLS-00394: 在 FETCH 语句的 INTO 列表中值数量出现错误
行:22