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两个字段怎么弄。。
解决方案 »
- sql 行转列
- 特别难题!!!!
- 请教各位大虾:log_checkpoint_interval及log_checkpoint_timeout是如何对checkpoints起作用的
- 大家帮我看看这个问题,关于decode()的sql语句?
- oracle日期函数
- 如何增加oracle连接最大进程数
- 关于数据库导入慢的问题~!!100分!急
- oracle使用imp报错。请问这个要怎么解决呢 我是菜鸟·
- 这个语句效率太低,各位大神有更好的建议吗?取得一个日期区间内最大日期的值和最小日期的值,用于相减
- 关于ORACLE数据库视图的问题
- PL/SQL函数变量定义困惑!
- 这个SQL应该怎么写啊,给100分了
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