代码如下:
create or replace procedure Proc_update_paging_ciid
(
setstarttime in date,
setendtime in date
)
as
begin
declare
cursor LCID_cursor is select lacid, ciid,starttime,imsi from t_detail_cs_paging
where starttime>=setstarttime
and starttime<setendtime
and ciid = 0 order by starttime desc;lac_ci_id_cursor LCID_cursor%rowtype;
v_ciid t_detail_cs_pagingmo.ciid%type;
v_ciid1 t_detail_cs_pagingmo.ciid%type;
v_ciid2 t_detail_cs_pagingmo.ciid%type;
v_pagingmotime date;
v_pagingtime date;
v_number number(10);
begin
open LCID_cursor;
loop
begin
fetch LCID_cursor into lac_ci_id_cursor;select ciid,starttime into v_ciid ,v_pagingmotime from t_detail_cs_pagingmo
where imsi = lac_ci_id_cursor.imsi and starttime >= to_char(lac_ci_id_cursor.starttime-1/24)
and starttime<to_date(lac_ci_id_cursor.starttime,'YYYY/MM/DD HH24:MI:SS')
and rownum =1 order by starttime desc;
select ciid,starttime into v_ciid2 ,v_pagingtime from t_detail_cs_pagingmo
where imsi = lac_ci_id_cursor.imsi and starttime <= to_char(lac_ci_id_cursor.starttime+1/24)
and starttime>to_date(lac_ci_id_cursor.starttime,'YYYY/MM/DD HH24:MI:SS')
and rownum =1 order by starttime asc;
if to_number(lac_ci_id_cursor.starttime-v_pagingmotime)*86400>=to_number(v_pagingtime-lac_ci_id_cursor.starttime)*86400 then
v_ciid:=v_ciid1;
else
v_ciid:=v_ciid2;
end if;
update t_detail_cs_paging set ciid=v_ciid
where imsi=lac_ci_id_cursor.imsi
and starttime=lac_ci_id_cursor.starttime;
v_number:=v_number+1; exit when LCID_cursor%notfound;exception
when others then
begin
commit;
dbms_output.put_line('本次修改了'||v_number||'条数据!');
v_number:=0;
rollback;
end;
end;
end loop;
close LCID_cursor;
end;
end;
我想做的就是把第一个select时没有数据就跳到第二个select,如果还是没有就跳到下一次循环
create or replace procedure Proc_update_paging_ciid
(
setstarttime in date,
setendtime in date
)
as
begin
declare
cursor LCID_cursor is select lacid, ciid,starttime,imsi from t_detail_cs_paging
where starttime>=setstarttime
and starttime<setendtime
and ciid = 0 order by starttime desc;lac_ci_id_cursor LCID_cursor%rowtype;
v_ciid t_detail_cs_pagingmo.ciid%type;
v_ciid1 t_detail_cs_pagingmo.ciid%type;
v_ciid2 t_detail_cs_pagingmo.ciid%type;
v_pagingmotime date;
v_pagingtime date;
v_number number(10);
begin
open LCID_cursor;
loop
begin
fetch LCID_cursor into lac_ci_id_cursor;select ciid,starttime into v_ciid ,v_pagingmotime from t_detail_cs_pagingmo
where imsi = lac_ci_id_cursor.imsi and starttime >= to_char(lac_ci_id_cursor.starttime-1/24)
and starttime<to_date(lac_ci_id_cursor.starttime,'YYYY/MM/DD HH24:MI:SS')
and rownum =1 order by starttime desc;
select ciid,starttime into v_ciid2 ,v_pagingtime from t_detail_cs_pagingmo
where imsi = lac_ci_id_cursor.imsi and starttime <= to_char(lac_ci_id_cursor.starttime+1/24)
and starttime>to_date(lac_ci_id_cursor.starttime,'YYYY/MM/DD HH24:MI:SS')
and rownum =1 order by starttime asc;
if to_number(lac_ci_id_cursor.starttime-v_pagingmotime)*86400>=to_number(v_pagingtime-lac_ci_id_cursor.starttime)*86400 then
v_ciid:=v_ciid1;
else
v_ciid:=v_ciid2;
end if;
update t_detail_cs_paging set ciid=v_ciid
where imsi=lac_ci_id_cursor.imsi
and starttime=lac_ci_id_cursor.starttime;
v_number:=v_number+1; exit when LCID_cursor%notfound;exception
when others then
begin
commit;
dbms_output.put_line('本次修改了'||v_number||'条数据!');
v_number:=0;
rollback;
end;
end;
end loop;
close LCID_cursor;
end;
end;
我想做的就是把第一个select时没有数据就跳到第二个select,如果还是没有就跳到下一次循环
cursor cur_lcid is select ...;
rec_lcid cur_lcid%rowtype;
v_ciid table.ciid%type;BEGIN
open cur_lcid;
loop
fetch cur_lcid into rec_lcid;
exit when cur_lcid%notfound;
--1.执行第一个select
select ciid into v_ciid where ...;
--2.判断第一个select是否有值,如果为空执行另一个SQL
if v_ciid is null then
--3. 执行第二个select
select ciid into v_ciid where ...;
if v_ciid is not null then
--5. 其它操作
end if;
else
--4.其它操作
end if;
end loop;
close cur_lcid;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
RAISE;
END Prc_Update;
cursor cur_lcid is select ...;
rec_lcid cur_lcid%rowtype;
v_ciid table.ciid%type;BEGIN
open cur_lcid;
loop
fetch cur_lcid into rec_lcid;
exit when cur_lcid%notfound;
--1.执行第一个select
select ciid into v_ciid where ...;
--2.判断第一个select是否有值,如果为空执行另一个SQL
if v_ciid is null then
--3. 执行第二个select
select ciid into v_ciid where ...;
if v_ciid is not null then
--5. 其它操作
end if;
else
--4.其它操作
end if;
end loop;
close cur_lcid;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
RAISE;
END Prc_Update;