存储过程如下:
create or replace procedure sp_tomonth_car_moneyis
----变量定义----
i_car_id number;
f_pay number(9, 2);
f_number number(9, 2);
f_number_now number(9, 2);
ch_re char(1);
ch_re_now number(9, 2);
cursor cu_car is
select a.car_id, b.pay_off_per_number, decode(b.pay_off_unit, '0', '1', '1', '0')
from SF_T_Car_Pay_Off a, SF_T_Pay_Off_Name b
where a.pay_off_id = b.id and a.end_date > sysdate;
begin
insert into SF_t_car_hire_history
select a.Car_id, to_char(sysdate, 'yyyymm'), b.hire_name, b.hire_pay,
a.Pay_OFF, a.Pay_re, a.Honor_money, a.Update_Man,
a.Pay_Off_time, b.min_back_time, b.pic_min_back_time, b.is_spe,
b.Temp_min_back_time, c.test_re
from SF_t_car_hire a, SF_T_hire_kind b, gk_t_carinfo c
where a.hire_id = b.hire_id and a.car_id = c.car_id
and c.delete_flag != '1' and c.Test_re = '1' and (c.first_back is not null)
and c.first_back < to_date(to_char(sysdate, 'yyyymm') || '16', 'yyyymmdd')
and c.first_back > to_date(to_char(sysdate, 'yyyymm') || '01', 'yyyymmdd'); insert into SF_t_car_hire_history
select a.Car_id, to_char(sysdate, 'yyyymm'), b.hire_name, b.hire_pay,
100, 0, a.Honor_money, a.Update_Man, a.Pay_Off_time, b.min_back_time,
b.pic_min_back_time, b.is_spe, b.Temp_min_back_time, c.test_re
from SF_t_car_hire a, SF_T_hire_kind b, gk_t_carinfo c
where a.hire_id = b.hire_id and a.car_id = c.car_id
and c.delete_flag != '1' and c.Test_re = '0' and (c.first_back is not null)
and c.first_back < to_date(to_char(sysdate, 'yyyymm') || '16', 'yyyymmdd')
and c.first_back > to_date(to_char(sysdate, 'yyyymm') || '01', 'yyyymmdd'); open cu_car;
loop
FETCH cu_car INTO i_car_id, f_number, ch_re;
EXIT WHEN cu_car%NOTFOUND; select Hire_pay, Pay_OFF, Pay_re into f_pay, f_number_now, ch_re_now
from SF_t_car_hire_history
where car_id = i_car_id and Car_month = to_char(sysdate, 'yyyymm'); if ch_re = ch_re_now then
if f_number_now < f_number then
update SF_t_car_hire_history
set Pay_OFF = f_number
where car_id = i_car_id and car_month = to_char(sysdate, 'yyyymm');
end if;
else
if ch_re_now = '0' then
if (f_pay * f_number_now / 100) < f_number then
update SF_t_car_hire_history
set Pay_OFF = f_number, Pay_re = ch_re
where car_id = i_car_id and car_month = to_char(sysdate, 'yyyymm');
end if;
else
if (f_pay * f_number / 100) > f_number_now then
update SF_t_car_hire_history
set Pay_OFF = f_number, Pay_re = ch_re
where car_id = i_car_id and car_month = to_char(sysdate, 'yyyymm');
end if;
end if;
end if;
end loop;
close cu_car; insert into SF_T_pay_other
select SF_s_pay_other.nextval, to_char(sysdate, 'yyyymm'), a.sim_phone_no, b.Hire_pay, '0', sysdate, 1, a.car_id
from gk_t_carinfo a, sf_t_mobil_hire b, SF_T_hire_kind c, SF_t_car_hire d
where a.car_id = d.car_id and d.Hire_id = c.Hire_ID and c.Mobile_hire = b.Hire_id and a.delete_flag != '1'
and a.first_back < to_date(to_char(sysdate, 'yyyymm') || '16', 'yyyymmdd')
and a.first_back > to_date(to_char(sysdate, 'yyyymm') || '01', 'yyyymmdd'); insert into sf_t_pay
select sf_s_pay.nextval, a.car_id, decode(a.Pay_re, '0', - a.Hire_pay * (100 - a.Pay_OFF) / 100, '1', -(a.Hire_pay - a.pay_off)),
sysdate, '', '', '9', '0', 1, '1', sysdate, null , null
from SF_t_car_hire_history a, gk_t_carinfo b
where a.car_id = b.car_id and a.car_month = to_char(sysdate, 'yyyymm')
and b.first_back < to_date(to_char(sysdate, 'yyyymm') || '16', 'yyyymmdd')
and b.first_back > to_date(to_char(sysdate, 'yyyymm') || '01', 'yyyymmdd'); commit;
exception
when others then
begin
rollback;
end;
end sp_tomonth_car_money;job如下:
begin
sys.dbms_job.submit(job => :job,
what => 'sp_stop_department;',
next_date => to_date('26-03-2008 00:00:01', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'to_date((to_char(add_months(sysdate, 1), ''yyyy-mm'') || ''-26 00:00:01''), ''yyyy-mm-dd hh24:mi:ss'')');
commit;
end;
/每次执行时抱错,大家帮我看看
create or replace procedure sp_tomonth_car_moneyis
----变量定义----
i_car_id number;
f_pay number(9, 2);
f_number number(9, 2);
f_number_now number(9, 2);
ch_re char(1);
ch_re_now number(9, 2);
cursor cu_car is
select a.car_id, b.pay_off_per_number, decode(b.pay_off_unit, '0', '1', '1', '0')
from SF_T_Car_Pay_Off a, SF_T_Pay_Off_Name b
where a.pay_off_id = b.id and a.end_date > sysdate;
begin
insert into SF_t_car_hire_history
select a.Car_id, to_char(sysdate, 'yyyymm'), b.hire_name, b.hire_pay,
a.Pay_OFF, a.Pay_re, a.Honor_money, a.Update_Man,
a.Pay_Off_time, b.min_back_time, b.pic_min_back_time, b.is_spe,
b.Temp_min_back_time, c.test_re
from SF_t_car_hire a, SF_T_hire_kind b, gk_t_carinfo c
where a.hire_id = b.hire_id and a.car_id = c.car_id
and c.delete_flag != '1' and c.Test_re = '1' and (c.first_back is not null)
and c.first_back < to_date(to_char(sysdate, 'yyyymm') || '16', 'yyyymmdd')
and c.first_back > to_date(to_char(sysdate, 'yyyymm') || '01', 'yyyymmdd'); insert into SF_t_car_hire_history
select a.Car_id, to_char(sysdate, 'yyyymm'), b.hire_name, b.hire_pay,
100, 0, a.Honor_money, a.Update_Man, a.Pay_Off_time, b.min_back_time,
b.pic_min_back_time, b.is_spe, b.Temp_min_back_time, c.test_re
from SF_t_car_hire a, SF_T_hire_kind b, gk_t_carinfo c
where a.hire_id = b.hire_id and a.car_id = c.car_id
and c.delete_flag != '1' and c.Test_re = '0' and (c.first_back is not null)
and c.first_back < to_date(to_char(sysdate, 'yyyymm') || '16', 'yyyymmdd')
and c.first_back > to_date(to_char(sysdate, 'yyyymm') || '01', 'yyyymmdd'); open cu_car;
loop
FETCH cu_car INTO i_car_id, f_number, ch_re;
EXIT WHEN cu_car%NOTFOUND; select Hire_pay, Pay_OFF, Pay_re into f_pay, f_number_now, ch_re_now
from SF_t_car_hire_history
where car_id = i_car_id and Car_month = to_char(sysdate, 'yyyymm'); if ch_re = ch_re_now then
if f_number_now < f_number then
update SF_t_car_hire_history
set Pay_OFF = f_number
where car_id = i_car_id and car_month = to_char(sysdate, 'yyyymm');
end if;
else
if ch_re_now = '0' then
if (f_pay * f_number_now / 100) < f_number then
update SF_t_car_hire_history
set Pay_OFF = f_number, Pay_re = ch_re
where car_id = i_car_id and car_month = to_char(sysdate, 'yyyymm');
end if;
else
if (f_pay * f_number / 100) > f_number_now then
update SF_t_car_hire_history
set Pay_OFF = f_number, Pay_re = ch_re
where car_id = i_car_id and car_month = to_char(sysdate, 'yyyymm');
end if;
end if;
end if;
end loop;
close cu_car; insert into SF_T_pay_other
select SF_s_pay_other.nextval, to_char(sysdate, 'yyyymm'), a.sim_phone_no, b.Hire_pay, '0', sysdate, 1, a.car_id
from gk_t_carinfo a, sf_t_mobil_hire b, SF_T_hire_kind c, SF_t_car_hire d
where a.car_id = d.car_id and d.Hire_id = c.Hire_ID and c.Mobile_hire = b.Hire_id and a.delete_flag != '1'
and a.first_back < to_date(to_char(sysdate, 'yyyymm') || '16', 'yyyymmdd')
and a.first_back > to_date(to_char(sysdate, 'yyyymm') || '01', 'yyyymmdd'); insert into sf_t_pay
select sf_s_pay.nextval, a.car_id, decode(a.Pay_re, '0', - a.Hire_pay * (100 - a.Pay_OFF) / 100, '1', -(a.Hire_pay - a.pay_off)),
sysdate, '', '', '9', '0', 1, '1', sysdate, null , null
from SF_t_car_hire_history a, gk_t_carinfo b
where a.car_id = b.car_id and a.car_month = to_char(sysdate, 'yyyymm')
and b.first_back < to_date(to_char(sysdate, 'yyyymm') || '16', 'yyyymmdd')
and b.first_back > to_date(to_char(sysdate, 'yyyymm') || '01', 'yyyymmdd'); commit;
exception
when others then
begin
rollback;
end;
end sp_tomonth_car_money;job如下:
begin
sys.dbms_job.submit(job => :job,
what => 'sp_stop_department;',
next_date => to_date('26-03-2008 00:00:01', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'to_date((to_char(add_months(sysdate, 1), ''yyyy-mm'') || ''-26 00:00:01''), ''yyyy-mm-dd hh24:mi:ss'')');
commit;
end;
/每次执行时抱错,大家帮我看看
解决方案 »
- 求一个计算小时的sql 语句。+ 急急急
- 今天取DMP文件时遇到一个不会解决的问题,大家帮我看看什么会事。
- 请教关于oracle查找一个表的问题?
- 初學:ORACLE幾個小問題
- ORA-00942: 表或视图不存在
- 在optimizer_mode=choose情况下,查询时为什么不会使用到索引!
- 一个很怪异的问题!
- 一个困惑的问题!
- Oracle重新配置数据库选件时最后一步出错,提示Could not find appropriate listener for this database.
- Oracle中的临时表
- 双向触发器更新表数据
- 救命啊,关于Oracle存储过程中判断表中的记录是否存在的问题
BEGIN
sp_stop_department;
END;',