刚开始学存储过程,根据自己需求,照着网上的例子写了一个,可是测试以后发现没有执行,我已经提交了,而且我也肯定没有异常导致回滚,但是为什么我查了一下目标表,结果还是没有任何数据呢?想请高手帮忙看看:CREATE OR REPLACE PROCEDURE "PROC_ICS" is
CURSOR cur_ics IS
select t.passenger_id,
t.flt_nbr,
t.lcl_dpt_time,
t.lcl_arr_time,
t.dpt_airpt_cd,
t.arrv_airpt_cd,
t.sub_cls_cd,
t.sub_cls_type,
t.pnr_num,
t.tkt_status
from ics_realtime t; passengerid varchar(20);
fltnbr varchar(6);
dpttime varchar(20);
arrtime varchar(20);
dptcd varchar(5);
arrvcd varchar(5);
clscd varchar(10);
clstype varchar(10);
pnr varchar(10);
status varchar(10);begin
open cur_ics;
LOOP
fetch cur_ics
into passengerid,
fltnbr,
dpttime,
arrtime,
dptcd,
arrvcd,
pnr,
clscd,
clstype,
status;
if (cur_ics%FOUND) THEN
IF (status not in ('0', '1')) THEN
insert into ics_bak
select t.passenger_id,
nvl(t.airln_cd, ' '),
t.flt_nbr,
t.lcl_dpt_time,
nvl(t.lcl_arr_time, ' '),
t.dpt_airpt_cd,
t.arrv_airpt_cd,
t.sub_cls_cd,
t.sub_cls_type,
nvl(t.assgn_st, ' '),
nvl(t.tkt_status, '-1'),
t.pnr_num
from ics t
where t.passenger_id = passengerid
and t.flt_nbr = fltnbr
and t.lcl_dpt_time = dpttime
and t.dpt_airpt_cd = dptcd
and t.arrv_airpt_cd = arrvcd
and t.pnr_num = pnr;
delete from ics t
where t.passenger_id = passengerid
and t.flt_nbr = fltnbr
and t.lcl_dpt_time = dpttime
and t.dpt_airpt_cd = dptcd
and t.arrv_airpt_cd = arrvcd
and t.pnr_num = pnr;
commit;
ELSIF (status = '1') THEN
insert into ics_bak
select t.passenger_id,
nvl(t.airln_cd, ' '),
t.flt_nbr,
t.lcl_dpt_time,
nvl(t.lcl_arr_time, ' '),
t.dpt_airpt_cd,
t.arrv_airpt_cd,
t.sub_cls_cd,
t.sub_cls_type,
nvl(t.assgn_st, ' '),
nvl(t.tkt_status, '-1'),
t.pnr_num
from ics t
where t.passenger_id = passengerid
and t.pnr_num = pnr;
delete from ics t
where t.passenger_id = passengerid
and t.pnr_num = pnr;
commit;
END IF;
ELSE
EXIT;
END if;
END LOOP;
close cur_ics;
end proc_ics;
不知道我是不是提交的位置不对,照猫画虎可能写的比较烂,还请各位高手帮忙
CURSOR cur_ics IS
select t.passenger_id,
t.flt_nbr,
t.lcl_dpt_time,
t.lcl_arr_time,
t.dpt_airpt_cd,
t.arrv_airpt_cd,
t.sub_cls_cd,
t.sub_cls_type,
t.pnr_num,
t.tkt_status
from ics_realtime t; passengerid varchar(20);
fltnbr varchar(6);
dpttime varchar(20);
arrtime varchar(20);
dptcd varchar(5);
arrvcd varchar(5);
clscd varchar(10);
clstype varchar(10);
pnr varchar(10);
status varchar(10);begin
open cur_ics;
LOOP
fetch cur_ics
into passengerid,
fltnbr,
dpttime,
arrtime,
dptcd,
arrvcd,
pnr,
clscd,
clstype,
status;
if (cur_ics%FOUND) THEN
IF (status not in ('0', '1')) THEN
insert into ics_bak
select t.passenger_id,
nvl(t.airln_cd, ' '),
t.flt_nbr,
t.lcl_dpt_time,
nvl(t.lcl_arr_time, ' '),
t.dpt_airpt_cd,
t.arrv_airpt_cd,
t.sub_cls_cd,
t.sub_cls_type,
nvl(t.assgn_st, ' '),
nvl(t.tkt_status, '-1'),
t.pnr_num
from ics t
where t.passenger_id = passengerid
and t.flt_nbr = fltnbr
and t.lcl_dpt_time = dpttime
and t.dpt_airpt_cd = dptcd
and t.arrv_airpt_cd = arrvcd
and t.pnr_num = pnr;
delete from ics t
where t.passenger_id = passengerid
and t.flt_nbr = fltnbr
and t.lcl_dpt_time = dpttime
and t.dpt_airpt_cd = dptcd
and t.arrv_airpt_cd = arrvcd
and t.pnr_num = pnr;
commit;
ELSIF (status = '1') THEN
insert into ics_bak
select t.passenger_id,
nvl(t.airln_cd, ' '),
t.flt_nbr,
t.lcl_dpt_time,
nvl(t.lcl_arr_time, ' '),
t.dpt_airpt_cd,
t.arrv_airpt_cd,
t.sub_cls_cd,
t.sub_cls_type,
nvl(t.assgn_st, ' '),
nvl(t.tkt_status, '-1'),
t.pnr_num
from ics t
where t.passenger_id = passengerid
and t.pnr_num = pnr;
delete from ics t
where t.passenger_id = passengerid
and t.pnr_num = pnr;
commit;
END IF;
ELSE
EXIT;
END if;
END LOOP;
close cur_ics;
end proc_ics;
不知道我是不是提交的位置不对,照猫画虎可能写的比较烂,还请各位高手帮忙
where t.passenger_id = passengerid
and t.pnr_num = pnr;
你把刚刚insert的数据又删除了
select t.passenger_id,
nvl(t.airln_cd, ' '),
我是在这个表的插入
select t.passenger_id,
nvl(t.airln_cd, ' '),
t.flt_nbr,
t.lcl_dpt_time,
nvl(t.lcl_arr_time, ' '),
t.dpt_airpt_cd,
t.arrv_airpt_cd,
t.sub_cls_cd,
t.sub_cls_type,
nvl(t.assgn_st, ' '),
nvl(t.tkt_status, '-1'),
t.pnr_num
from ics t
where t.passenger_id = passengerid
and t.flt_nbr = fltnbr
and t.lcl_dpt_time = dpttime
and t.dpt_airpt_cd = dptcd
and t.arrv_airpt_cd = arrvcd
and t.pnr_num = pnr;
这个语句有数据没?
--把end if; end loop;改成下面的END if;fetch cur_ics
into passengerid,
fltnbr,
dpttime,
arrtime,
dptcd,
arrvcd,
pnr,
clscd,
clstype,
status; END LOOP;---把删除的先去掉在试试delete from ics t
where t.passenger_id = passengerid
and t.flt_nbr = fltnbr
and t.lcl_dpt_time = dpttime
and t.dpt_airpt_cd = dptcd
and t.arrv_airpt_cd = arrvcd
and t.pnr_num = pnr;
delete from ics t
where t.passenger_id = passengerid
and t.pnr_num = pnr;