create  table test_job(para_date date); 
commit; 

insert into test_job values(sysdate); 
commit; 

select  * from test_job;

create  or replace  procedure test_jobproce as 
begin 
insert into test_job values(sysdate); 
end test_jobproce; 

declare test_job_really  number; 
begin 
 dbms_job.submit(test_job_really,\'test_jobproce;\',sysdate,\'sysdate+1/1440\'); 
commit; 
end; 

select  * from sys.user_jobs 

begin 
 dbms_job.run(25); 
commit; 
end; 

begin 
 dbms_job.broken(25,true);
 commit; 
end; 

begin 
 dbms_job.remove(25); 
commit; 
end; 

CREATE OR REPLACE PROCEDURE proc_fixed_pay_into_listofcost
 AS
 cursor cursor_checkin is select checkout.sys_uid,checkout.id,checkout.pid,checkout.create_date,checkout.fixPay,checkout.cost_fixed,checkout.thismonth from ( SELECT sys_guid() as sys_uid,c.id,c.pid ,c.create_date,c.out_time,(c.sum_cost-c.sum_discount) as fixPay,round((c.sum_cost-c.sum_discount)/(to_number(to_char(last_day(add_months(trunc(sysdate),-1)),\'dd\')))*
  (case      
  when to_char(c.Create_Date,\'yyyymm\') != to_char(add_months(trunc(sysdate),-1),\'yyyymm\') and to_char(decode(c.out_time,null,sysdate,c.out_time),\'yyyymm\') = to_char(add_months(trunc(sysdate),-1),\'yyyymm\') then  to_char(to_date(c.out_time),\'dd\')
  when to_char(c.Create_Date,\'yyyymm\') != to_char(add_months(trunc(sysdate),-1),\'yyyymm\') and to_char(decode(c.out_time,null,sysdate,c.out_time),\'yyyymm\') != to_char(sysdate,\'yyyymm\') then  to_char(last_day(to_date(add_months(trunc(sysdate),-1),\'yyyymm\')),\'dd\')
  when to_char(c.Create_Date,\'yyyymm\') = to_char(add_months(trunc(sysdate),-1),\'yyyymm\') and to_char(decode(c.out_time,null,sysdate,c.out_time),\'yyyymm\') = to_char(add_months(trunc(sysdate),-1),\'yyyymm\') then (to_number(to_char(to_date(c.out_time),\'dd\')) - to_number(to_char(c.Create_Date,\'dd\'))+1||\'\')
  when to_char(c.Create_Date,\'yyyymm\') = to_char(add_months(trunc(sysdate),-1),\'yyyymm\') and to_char(decode(c.out_time,null,sysdate,c.out_time),\'yyyymm\') != to_char(add_months(trunc(sysdate),-1),\'yyyymm\') then  (to_number(to_char(add_months(trunc(sysdate),-1),\'dd\')) - to_number(to_char(c.Create_Date,\'dd\'))+1||\'\')
  end),2) as cost_fixed,to_char(add_months(trunc(sysdate),-1),\'yyyymm\') as thisMonth,(case      
  when to_char(c.Create_Date,\'yyyymm\') != to_char(add_months(trunc(sysdate),-1),\'yyyymm\') and to_char(decode(c.out_time,null,sysdate,c.out_time),\'yyyymm\') = to_char(add_months(trunc(sysdate),-1),\'yyyymm\') then  to_char(to_date(c.out_time),\'dd\')
  when to_char(c.Create_Date,\'yyyymm\') != to_char(add_months(trunc(sysdate),-1),\'yyyymm\') and to_char(decode(c.out_time,null,sysdate,c.out_time),\'yyyymm\') != to_char(sysdate,\'yyyymm\') then  to_char(last_day(to_date(add_months(trunc(sysdate),-1),\'yyyymm\')),\'dd\')
  when to_char(c.Create_Date,\'yyyymm\') = to_char(add_months(trunc(sysdate),-1),\'yyyymm\') and to_char(decode(c.out_time,null,sysdate,c.out_time),\'yyyymm\') = to_char(add_months(trunc(sysdate),-1),\'yyyymm\') then (to_number(to_char(to_date(c.out_time),\'dd\')) - to_number(to_char(c.Create_Date,\'dd\'))+1||\'\')
  when to_char(c.Create_Date,\'yyyymm\') = to_char(add_months(trunc(sysdate),-1),\'yyyymm\') and to_char(decode(c.out_time,null,sysdate,c.out_time),\'yyyymm\') != to_char(add_months(trunc(sysdate),-1),\'yyyymm\') then  (to_number(to_char(add_months(trunc(sysdate),-1),\'dd\')) - to_number(to_char(c.Create_Date,\'dd\'))+1||\'\')
  end) as tt 
  FROM OLD_CHECKIN c,OLDPERSON op 
  where c.pid = op.id
  and c.chenk_status != \'04\'--已结算
  and  (c.out_time is null or to_char(c.out_time,\'yyyymm\') = to_char(add_months(trunc(sysdate),-1),\'yyyymm\'))  
  and to_char(c.Create_Date,\'yyyymm\') <=  to_char(add_months(trunc(sysdate),-1),\'yyyymm\') ) checkout where checkout.tt is not null;
 var_id VARCHAR2(32);--uuid
 var_pid VARCHAR2(32);--pid
 var_create_date date;--老人入住时间
 var_cost_fixed NUMBER(11,2);--老人上个月应扣固定费用
 var_cost NUMBER(11,2);--每月固定费用
 var_checkin_id VARCHAR2(32);--checkinId
 var_this_month VARCHAR2(10);--当前月份
 var_banlance VARCHAR2(20);--当前余额
 var_banlance_count NUMBER(20);--当前入住老人在费用清单列表中的记录数
 var_cost_count NUMBER(20);--当前入住老人在费用清单列表中的本月固定消费的记录数
 BEGIN
 
 --将上个月及上个月之前的入住老人的固定费用添加进费用清单列表
 
 -- 筛选条件  1、退住时间在当月  2、退住时间也不在当月  3、无退住时间
 -- 公共条件  入住时间不在当月
  open cursor_checkin;
  loop
    fetch cursor_checkin into var_id,var_checkin_id,var_pid,var_create_date,var_cost,var_cost_fixed,var_this_month;
    exit when cursor_checkin%notfound;
    
    --需要根据checkinId查询当前入住老人在费用清单中的余额
     select max(balance),count(balance) into var_banlance,var_banlance_count from 
     (select (to_number(decode(lo.banlance,null,\'0\',lo.banlance)) - to_number(var_cost_fixed))||\'\' as balance,rownum from list_of_cost lo left join old_checkin ck
             on lo.checkin_id = ck.id 
              where lo.checkin_id = \'\'||var_checkin_id
              order by lo.create_date desc) 
     where rownum = 1 ;
    
    --表里边如果没有记录，余额就是0减去本次固定费用
    if var_banlance_count=0 then
      var_banlance := \'-\'||var_cost_fixed;
    end if;

    --如果本月已经插入郭记录，就不再插入 
     select count(id) into var_cost_count from  list_of_cost 
              where checkin_id = \'\'||var_checkin_id
              and to_char(create_date,\'yyyymm\') = to_char(sysdate,\'yyyymm\')
              and fee_id = \'\'||var_this_month; 
    
    if var_banlance_count < 1 then
        insert into list_of_cost (id,PID,inex_type,wxpend,amount,banlance,remark,create_by,create_date,checkin_id,fee_id)
        values(var_id,var_pid,\'02\',\'01\',var_cost_fixed,var_banlance,var_this_month||\'月固定费用\',\'admin\',sysdate,var_checkin_id,var_this_month);
        dbms_output.put_line(\'var_id:\'||var_id||\',var_create_date:\'||var_create_date||\',var_cost:\'||var_cost||\',var_cost_fixed:\'||var_cost_fixed||\',var_banlance:\'||var_banlance||\',var_this_month:\'||var_this_month);
    end if;
    
  end loop;
  close cursor_checkin;
  dbms_output.put_line(\'---loop end---\');

COMMIT;
 EXCEPTION
 WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(\'发生错误，同步失败！\');
 ROLLBACK;
 END;

---创建job 每个月定时执行一次
declare job_fixed_pay_into_listofcost  number; 
begin 
 dbms_job.submit(job_fixed_pay_into_listofcost,\'proc_fixed_pay_into_listofcost;\',sysdate, \'TRUNC(LAST_DAY(SYSDATE ) + 1)\' ); 
commit; 
end; 

select * from dba_jobs;