CREATE TABLE car_speed_his ( carno VARCHAR2(20), id NUMBER(10), speed NUMBER(3) ) /CREATE TABLE t_log ( carno VARCHAR2(20), err_count NUMBER(5) ) /CREATE OR REPLACE procedure myProc IS cursor cur_carNo Is select distinct carNo from car_speed_his; cursor cur_speed(p_carNo varchar2) Is Select speed From car_speed_his Order by id; w_err_count number(3):=0; w_count number(3):=0; w_speed1 number(3):=0; w_speed2 number(3):=0;Begin for w_carNo in cur_carNo loop open cur_speed(w_carNo.carNo); fetch cur_speed into w_speed1; loop if cur_speed%notfound then if w_err_count > 0 then insert into t_log values (w_carNo.carNo,w_err_count); end if; exit; end if; fetch cur_speed into w_speed2; if cur_speed%notfound then if w_err_count > 0 then insert into t_log values (w_carNo.carNo,w_err_count); end if; exit; end if; if abs(w_speed1-w_speed2) >= 30 then w_count:= w_count + 1; else w_count:= 0; end if; if w_count = 3 then w_err_count := w_err_count + 1; w_count := 0; end if; w_speed1 := w_speed2; end loop; end loop; end; /
更规范化一点:CREATE TABLE car_speed_his ( carno VARCHAR2(20), id NUMBER(10), speed NUMBER(3) ) /CREATE TABLE t_log ( carno VARCHAR2(20), err_count NUMBER(5) ) /CREATE OR REPLACE procedure myProc IS cursor cur_carNo Is select distinct carNo from car_speed_his; cursor cur_speed(p_carNo varchar2) Is Select speed From car_speed_his Order by id; w_err_count number(3):=0; w_count number(3):=0; w_speed1 number(3):=0; w_speed2 number(3):=0; procedure log_err_count(p_carNo varchar2, p_err_count number) Is begin if p_err_count > 0 then insert into t_log values (p_carNo,p_err_count); end if; end;Begin for w_carNo in cur_carNo loop open cur_speed(w_carNo.carNo); fetch cur_speed into w_speed1; loop if cur_speed%notfound then log_err_count(w_carNo.carNo,w_err_count); exit; end if; fetch cur_speed into w_speed2; if cur_speed%notfound then log_err_count(w_carNo.carNo,w_err_count); exit; end if; if abs(w_speed1-w_speed2) >= 30 then w_count:= w_count + 1; else w_count:= 0; end if; if w_count = 3 then w_err_count := w_err_count + 1; w_count := 0; end if; w_speed1 := w_speed2; end loop; end loop; end; /
2、“第一行数据的速度与第二行数据的速度相比较,如果它大于30KM”
是 第一行数据减去第二行数据 还是 第二行数据减去第一行数据 ???
3、关于X加一,如果第二行减第一行大于30KM,那么 X:=X+1;
反过来,若第二行减第一行小于等于30KM呢?是不是 X的值不变???
4、下面这句话让人看了莫名其妙:
“如果它大于30KM,哪么X就3,这时这辆四就计着是一次“疲劳驾驶”了,
X=0,之后再从第五行数据开始,以此类推,” 到底是X=4 为疲劳驾车还是X=3为疲劳驾车?
我分析,好像是X=4就算疲劳驾车,然后X重新赋值为0,你是想要
计算“疲劳驾车”的次数!5、你说的情况是连续的比较结果都大于30KM
那么如果第二行减去第一行大于30KM,而第三行减去第二行
小于等于30KM,然后接下去第四行减去第三行大于30KM,
第五行减去第四行大于30KM,第六行减去第五行大于30KM,
得出X=4 ,这样算不算“疲劳驾车”呢???
你没有把问题陈诉清楚,别人怎么帮你?,只要把这些问题
搞清楚了,这个过程很EASY的!,我几分钟就能搞定,不过
你最好自己编这个过程,真的很简单的阿!
数据:
carNO speed id
粤A123456 80 1
粤A123456 60 2
粤A123456 90 3
粤A123456 60 4
粤A123456 95 5
粤A123456 80 6
粤B123456 70 7
粤B123456 100 8
粤B123456 60 9
粤B123456 95 10
粤B123456 65 11
粤B123456 100 12
粤B123456 70 13
粤B123456 80 14如“粤A123456“从第1至第6行数据是这样比较的,从第1行数据开始与第2行数据的速度相减,2-3,3-4,如果连续三次差值的绝对值都>=30,哪么算做一次”疲劳驾驶“了,接着5-6。等以此类推,但车牌号必须相同;“粤A123456”从2--6行算是一次”疲劳驾驶“,“粤B123456”的7--10行算是一次“疲劳驾驶”;
30KM是从另一表里取的参数,
这些数据是从一个表里一天的数据,即要用job实现一天执行一次,再把“车牌号”与“疲劳驾驶“的次数存放在另一日志表里,真惨,本人还没写过oracle的过程,请大家帮帮忙,
(
carno VARCHAR2(20),
id NUMBER(10),
speed NUMBER(3)
)
/CREATE TABLE t_log
(
carno VARCHAR2(20),
err_count NUMBER(5)
)
/CREATE OR REPLACE procedure myProc
IS
cursor cur_carNo Is
select distinct carNo
from car_speed_his; cursor cur_speed(p_carNo varchar2) Is
Select speed
From car_speed_his
Order by id; w_err_count number(3):=0;
w_count number(3):=0;
w_speed1 number(3):=0;
w_speed2 number(3):=0;Begin
for w_carNo in cur_carNo loop open cur_speed(w_carNo.carNo);
fetch cur_speed into w_speed1; loop
if cur_speed%notfound then
if w_err_count > 0 then
insert into t_log values (w_carNo.carNo,w_err_count);
end if;
exit;
end if; fetch cur_speed into w_speed2;
if cur_speed%notfound then
if w_err_count > 0 then
insert into t_log values (w_carNo.carNo,w_err_count);
end if;
exit;
end if; if abs(w_speed1-w_speed2) >= 30 then
w_count:= w_count + 1;
else
w_count:= 0;
end if; if w_count = 3 then
w_err_count := w_err_count + 1;
w_count := 0;
end if; w_speed1 := w_speed2;
end loop; end loop;
end;
/
(
carno VARCHAR2(20),
id NUMBER(10),
speed NUMBER(3)
)
/CREATE TABLE t_log
(
carno VARCHAR2(20),
err_count NUMBER(5)
)
/CREATE OR REPLACE procedure myProc
IS
cursor cur_carNo Is
select distinct carNo
from car_speed_his;
cursor cur_speed(p_carNo varchar2) Is Select speed
From car_speed_his
Order by id; w_err_count number(3):=0;
w_count number(3):=0;
w_speed1 number(3):=0;
w_speed2 number(3):=0; procedure log_err_count(p_carNo varchar2, p_err_count number) Is
begin
if p_err_count > 0 then
insert into t_log values (p_carNo,p_err_count);
end if;
end;Begin
for w_carNo in cur_carNo loop open cur_speed(w_carNo.carNo);
fetch cur_speed into w_speed1; loop
if cur_speed%notfound then
log_err_count(w_carNo.carNo,w_err_count);
exit;
end if; fetch cur_speed into w_speed2;
if cur_speed%notfound then
log_err_count(w_carNo.carNo,w_err_count);
exit;
end if; if abs(w_speed1-w_speed2) >= 30 then
w_count:= w_count + 1;
else
w_count:= 0;
end if; if w_count = 3 then
w_err_count := w_err_count + 1;
w_count := 0;
end if; w_speed1 := w_speed2;
end loop; end loop;
end;
/
我觉得这个问题还是用游标解决比较好,逻辑清晰,易于维护我也考虑过用分析函数,但是好像必须用到同一个表的几次join,效率肯定不高