oracle定时job、存储过程、函数、视图

--时间转期数函数 
create or replace function date_convert_period(d in date, productType in number) return varchar2 as
  /**
  * 根据传进来的产品类型把时间转期数
  * 例如：2015-01-27 如果产品类型是1，则转为 2015年01月下期(27大于15所以是下期)；如果产品类型是2，则转为 2015年01月全月
  */
  v_period varchar2(50);
  v_period_temp varchar2(50);
  n_date_number number;
begin
  v_period_temp := to_char(d,'yyyy-MM-dd');
  if productType = 1 then
    n_date_number := to_number(substr(v_period_temp,9,2));
    if n_date_number > 15 then
       v_period := substr(v_period_temp,0,4) || '年' || substr(v_period_temp,6,2) || '月下期';
    else
       v_period := substr(v_period_temp,0,4) || '年' || substr(v_period_temp,6,2) || '月上期';
    end if;
  else 
    v_period := substr(v_period_temp,0,4) || '年' || substr(v_period_temp,6,2) || '全月';
  end if;
  return v_period;
end;


--测试函数
select date_convert_period(sysdate,2) from dual;
--2015-01-27


---


--创建存储过程
create or replace procedure trace_table_bat as
  /**
  * 跟踪表
  * 把正式表插入到备份表
  */
begin


  insert into t_track_bak tb
    (TRACK_BAK_ID,
     CREATION_DATE,
     PERIOD,
     CHANNEL_ID,
     PRODUCT_TYPE_VAL,
     COLLECTORS_ID,
     ASSESSOR_ID,
     BAK_TIME)
    select sys_guid(),
           ccr.creation_time,
           date_convert_period(sysdate, ccr.product_type_val),
           ccr.channel_id,
           ccr.product_type_val,
           ccr.collector_id,
           ccr.assessor_id,
           sysdate
      from t_channel_charge_rel ccr;
      
      commit;


exception
  when others then
    rollback;
    dbms_output.put_line(SQLCODE || ',' || SQLERRM);
end;


----


/*--仁峰例子
begin
  sys.dbms_job.submit(job => :job,
                      what => 'SYNCHRODATA;',
                      --next_date => to_date('28-01-2015 03:00:00', 'dd-mm-yyyy hh24:mi:ss'),
                      interval => 'TRUNC(SYSDATE + 1) + (3*60)/(24*60)');
  commit;
end;*/


--创建JOB和运行
--每月10号00:00点
declare
  job number;
begin
  --sys.dbms_job.submit(job, 'trace_table_bat;', sysdate, 'sysdate+1/1440');  --每分钟
  sys.dbms_job.submit(job, 'trace_table_bat;', sysdate, 'TRUNC(LAST_DAY(SYSDATE)+1)');
  commit;
end;


--每月24号00:00点
declare
  job number;
begin
  sys.dbms_job.submit(job, 'trace_table_bat;', sysdate, 'sysdate+1/1440');  --每分钟
  --sys.dbms_job.submit(job, 'trace_table_bat;', sysdate, 'TRUNC(LAST_DAY(SYSDATE)+24)');
  commit;
end;




--删除JOB 
begin 
  dbms_job.remove(55); 
  commit;
end; 


--测试
--truncate table t_track_bak
select * from t_track_bak;
select * from t_channel_charge_rel;


--查看job信息
select * from user_jobs ; 
select * from all_jobs ;


---------------网上创建job例子----------------------start--------------
--创建测试表 
create table a(a date);


--创建存储过程 
create or replace procedure test as
begin
  insert into a values (sysdate);
end;


--创建JOB和运行
declare
  job number;
begin
  sys.dbms_job.submit(job, 'test;', sysdate, 'sysdate+1/1440');
end;


--删除JOB 
begin 
  dbms_job.remove(48); 
end; 


--PL/SQL 过程已成功完成。
--运行JOB 
/*begin 
  dbms_job.run(:job1); 
end; 
*/


--查看数据
select to_char(a,'yyyy/mm/dd hh24:mi:ss') 时间 from a;


1 2015/01/27 14:28:19
2 2015/01/27 14:29:19
3 2015/01/27 14:30:19


--查看job信息
select * from user_jobs ; 
select * from all_jobs ;


---------------网上创建job例子----------------------end--------------


--仁峰例子
begin
  sys.dbms_job.submit(job => :job,
                      what => 'SYNCHRODATA;',
                      --next_date => to_date('28-01-2015 03:00:00', 'dd-mm-yyyy hh24:mi:ss'),
                      interval => 'TRUNC(SYSDATE + 1) + (3*60)/(24*60)');
  commit;
end;


-------------------------------------------------------------------


create or replace procedure proc_trace_table_sync(resultCode out varchar2,rsMsg out varchar2) as
/**
* 跟踪表同步
*/
begin
  rsMsg      := '同步成功';
  resultCode := '1';


  --删除正式表所有数据
  --truncate table t_channel_charge_rel;
  delete from t_channel_charge_rel;
  --把维护表数据全量插入到正式表
  insert into t_channel_charge_rel
    select * from t_channel_charge_rel_main;


  commit;


exception
  when others then
    rollback;
    rsMsg      := '同步错误';
    resultCode := '0';
    dbms_output.put_line(SQLCODE || ',' || SQLERRM);
end;


-------------------------------------------------------------------


--收集员视图
create or replace view v_collector as
select u.user_id,u.user_name,r.role_id,r.role_name,r.role_code
  from cas.sys_user u
 inner join cas.sys_user_role ur on u.user_id = ur.user_id
 inner join cas.sys_role r on ur.role_id = r.role_id
 inner join cas.sys_app a on r.app_id = a.app_id
 where r.role_code = 'ROLE_DEALER_COLLECTOR' 
   and a.app_code = 'DLRS'
;
--审核员视图
create or replace view v_assessor as
select u.user_id,u.user_name,r.role_id,r.role_name,r.role_code
  from cas.sys_user u
 inner join cas.sys_user_role ur on u.user_id = ur.user_id
 inner join cas.sys_role r on ur.role_id = r.role_id
 inner join cas.sys_app a on r.app_id = a.app_id
 where r.role_code = 'ROLE_DEALER_ASSESSOR' 
   and a.app_code = 'DLRS'


-------------------------------------------------------------------