以下是语句:
cursor checkmeal(s_date date, e_date date) is
select to_number(c."card_sequ"),
c."card_id",
c."sign_time",
c."clock_id",
c."emp_id",
c."card_consume",
c."kind",
c."mealtype",
c."card_balance",
c."card_times"
into t_card_sequ,
t_card_id,
t_sign_time,
t_clock_id,
t_emp_id,
t_card_consume,
t_kind,
t_mealtype,
t_card_balance,
t_card_times
FROM MealRecords@eastriver C,
employee@eastriver E,
Departs@eastriver D
where E."depart_id" = D."depart_id"(+)
and E."emp_id" = C."emp_id"(+)
and c."mealtype" = '0'
and (c."flag" = 0 or c."flag" = 7)
And E."depart_id" Like '%'
And E."emp_id" Like '82%'
and ("transfer_flag" is null or "transfer_flag" = 0)
and c."op_ymd" <= e_date
and C."op_ymd" >= s_date;
begin
for c1 in checkmeal(i_sdate, i_edate) loop
--check this meal has downloaded or not
select count(sn)
into v_cnt
from con_record
where rdatetime = t_sign_time
and sn = t_card_sequ
and cardno = t_card_id
and card_times = t_card_times
and balance = t_card_balance;
if v_cnt = 0 then
begin
--select sysdate into v_sysdate from dual;
insert into con_record
(sn,
cardno,
rdatetime,
clock_id,
personnelid,
conmoney,
kind,
mealtype,
creation_date,
balance,
card_times)
values
(t_card_sequ,
t_card_id,
t_sign_time,
t_clock_id,
t_emp_id,
t_card_consume * 100,
t_kind,
t_mealtype,
sysdate,
t_card_balance,
t_card_times);
exception
when others then
v_except := 2;
end;
v_download_count := v_download_count + 1;
end if;
end loop;
commit;