我的存储过程是这样的:create or replace package body carinfo as
procedure car_run(in_car in number)
IS
i integer;
num integer;
s_time date;
e_time date;
w_time date;
begin
i :=1;
insert into a (car,btype,start_time,end_time,start_trackid,end_trackid)
select car ,btype,dateutil.int2date(start_time) start_time,
dateutil.int2date(end_time) end_time,
start_trackid , end_trackid
from car_behavior
where car=in_car and btype='R'
order by start_time;
select count(*) into num from a;
for i in 1..num-1 loop
select end_time into e_time from a where rownum =i;
select start_time into s_time from a where rownum =i+1;
w_time :=dateutil.int2date(dateutil.date2int(e_time)- dateutil.date2int(s_time));
update a set waiting_time=w_time where rownum=i;
dbms_output.put_line('end_time:'||e_time);
dbms_output.put_line('start_time:'||s_time);
dbms_output.put_line('waiting_time:'||w_time);
end loop;
end car_run;
end carinfo;结果我单步测试时,执行到 select start_time into s_time from a where rownum =i+1;老是提示说no data found,为什么会提示找不到数据,已经把所有符合条件的记录都插入到a表中了,哪位大侠帮帮忙,帮我看下哪里错了,是不是不能用rownum一次取两条记录啊
procedure car_run(in_car in number)
IS
i integer;
num integer;
s_time date;
e_time date;
w_time date;
begin
i :=1;
insert into a (car,btype,start_time,end_time,start_trackid,end_trackid)
select car ,btype,dateutil.int2date(start_time) start_time,
dateutil.int2date(end_time) end_time,
start_trackid , end_trackid
from car_behavior
where car=in_car and btype='R'
order by start_time;
select count(*) into num from a;
for i in 1..num-1 loop
select end_time into e_time from a where rownum =i;
select start_time into s_time from a where rownum =i+1;
w_time :=dateutil.int2date(dateutil.date2int(e_time)- dateutil.date2int(s_time));
update a set waiting_time=w_time where rownum=i;
dbms_output.put_line('end_time:'||e_time);
dbms_output.put_line('start_time:'||s_time);
dbms_output.put_line('waiting_time:'||w_time);
end loop;
end car_run;
end carinfo;结果我单步测试时,执行到 select start_time into s_time from a where rownum =i+1;老是提示说no data found,为什么会提示找不到数据,已经把所有符合条件的记录都插入到a表中了,哪位大侠帮帮忙,帮我看下哪里错了,是不是不能用rownum一次取两条记录啊
解决方案 »
- 关于存储过程,多少条提交一次的问题
- 关于机器名和IP变更引发oracledbconsole服务无法启动,很奇怪,如何解决?急!
- 获取ODBC的所有用户表失败,高手看看是不是.NET2的BUG?
- 求一sql语句,where条件中几个字段加起来等于一个值,怎么写?
- 简单问题?
- 在PL/SQL Developer中,如何单步debug触发器????
- 我的奔4电脑不能装oracle8。1。7
- oracle用jdbc连接时thin连接是什么意思?哪位大虾能给解释一下
- oracle dbms_profiler使用问题
- oracle同服务器分项目的问题
- 一个语名优化的问题
- 有谁用过 Oracle Essbase啊
不知道你写的sql是什么意思 .
from a where rownum =i+1这种用法是错的.
rownum 是记录返回后,才在所返回的行上加上的.
没有返回的记录,没有返回的行rownum 都是0所以要这样写
from a where rownum =i+1 一行也不会返回.
要这样写
select * from (select a.*,rownum num from a ) where num =i+1
先生成num 再做 where num =i+1
select * from a where rownum =2;肯定没有任何结果,你也可以换另外几张表,要查询的话只有在基础上多套一层,或者在a表转移过去的时候自动创建一个字段那样是比较好的,如果要套一层的话
SELECT end_time INTO e_time FROM(
select end_time,rownum rn into e_time from a)
WHERE rn = i;SELECT start_time INTO s_time FROM (
select start_time,rownum rn into s_time from a)
WHERE rn = i + 1;如果直接由一个字段在转移过去的时候将ROWNUM带上,比如那个字段名称就叫RN,那么就不用这么费劲了。
SELECT start_time INTO s_time FROM a WHERE rownum = i + 1;
rownum=x,其中x>1条件永远不成立,所有上面两个语句取不到数据
--试试这个
INSERT INTO a
(car, btype, start_time, end_time, start_trackid, end_trackid, waiting_time)
SELECT car,
btype,
dateutil.int2date(start_time) start_time,
dateutil.int2date(end_time) end_time,
start_trackid,
end_trackid,
dateutil.int2date(dateutil.date2int(end_time) -
dateutil.date2int(lead(start_time) over(ORDER BY start_time))) w_time
FROM (SELECT car,
btype,
dateutil.int2date(start_time) start_time,
dateutil.int2date(end_time) end_time,
start_trackid,
end_trackid
FROM car_behavior
WHERE car = in_car AND
btype = 'R'
ORDER BY start_time);