create or replace procedure Test
is
outticket varchar2(30000);
ooid varchar2(30000);
cursor cur is
select o.flightdate,o.id into outticket,ooid from order o;
begin
savepoint point1;
for curr_i in cur loop update order o set o.orderstate='17' where ROUND(TO_NUMBER(sysdate - to_date(curr_i.flightdate,'yyyy-MM-dd'))) > 1 and o.orderstate='1 ' and o.id=curr_i.ooid;
end loop;
EXCEPTION
WHEN others THEN
ROLLBACK to savepoint point1;
RETURN;
end;呃,初学不知道select into两个字段怎么弄。。
is
outticket varchar2(30000);
ooid varchar2(30000);
cursor cur is
select o.flightdate,o.id into outticket,ooid from order o;
begin
savepoint point1;
for curr_i in cur loop update order o set o.orderstate='17' where ROUND(TO_NUMBER(sysdate - to_date(curr_i.flightdate,'yyyy-MM-dd'))) > 1 and o.orderstate='1 ' and o.id=curr_i.ooid;
end loop;
EXCEPTION
WHEN others THEN
ROLLBACK to savepoint point1;
RETURN;
end;呃,初学不知道select into两个字段怎么弄。。
declare
v_yhbh varchar2(10);
v_yhmc varchar2(60);
i number:=1;
cursor bh_list is select distinct yhbh,yhmc from dbuser_manager order by yhmc ;
begin
open bh_list;
fetch bh_list into v_yhbh,v_yhmc;
while bh_list %found loop
你要执行的操作
fetch bh_list into v_yhbh,v_yhmc;
end loop;
close bh_list;
end;
语法修改后是这样的create or replace procedure Test is
outticket varchar2(30000);
ooid varchar2(30000);
cursor cur is
select o.flightdate, o.id from order o;
begin
savepoint point1;
for curr_i in cur loop
update order o
set o.orderstate = '17'
where ROUND(TO_NUMBER(sysdate -
to_date(curr_i.flightdate, 'yyyy-MM-dd'))) > 1
and o.orderstate = '1 '
and o.id = curr_i.id;
end loop;
EXCEPTION
WHEN others THEN
ROLLBACK to savepoint point1;
RETURN;
end;
o.id=curr_i.ooid; -->curr_i.id