-- Create sequence
create sequence P_STOCK_MONTH_SEQ
minvalue 1
maxvalue 99999999999
start with 37
increment by 1
cache 20;

-- Create table
create table P_STOCK_MONTH
(
  tid            INTEGER not null,
  id             NUMBER not null,
  type           NUMBER,
  cate_id        NUMBER,
  goods_id       NUMBER,
  amount         NUMBER,
  unit           NUMBER,
  price_base     NUMBER,
  total          NUMBER,
  amount_deliver NUMBER,
  amount_sell    NUMBER,
  amount_lock    NUMBER,
  house_id       NUMBER,
  house_no       VARCHAR2(100),
  status         NUMBER,
  delete_flag    NUMBER,
  remark         VARCHAR2(1000),
  industry_id    NUMBER,
  amount_loss    NUMBER,
  stock_month    VARCHAR2(8)
)
tablespace JXC
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints
alter table P_STOCK_MONTH
  add constraint PK_P_STOCK_MONTH primary key (TID)
  using index
  tablespace JXC
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

--创建同步数据存储过程
create or replace procedure proc_sync_stock as
/*
Date:2015-11-06
Author:huangxianglong
Description:每个月底23:59:59同步数据到p_stock_month中
Version:1.0
*/
begin
DECLARE  CURSOR cur IS
    SELECT p_stock_month_seq.nextval,
           m.*,
           to_char(sysdate, 'yyyy-mm')
      FROM p_stock m;
  TYPE rec IS TABLE OF p_stock_month%ROWTYPE;
  recs rec;
BEGIN
  OPEN cur;
  WHILE (TRUE) LOOP
    FETCH cur BULK COLLECT
      INTO recs LIMIT 100;
    FORALL i IN 1 .. recs.COUNT
      INSERT INTO p_stock_month VALUES recs (i);
    COMMIT;
    EXIT WHEN cur%NOTFOUND;
  END LOOP;
  CLOSE cur;
END;
end proc_sync_stock;


--创建JOB
begin
  sys.dbms_scheduler.create_job(job_name            => 'JXC.JOB_PROC_SYNC_STOCK',
                                job_type            => 'STORED_PROCEDURE',
                                job_action          => 'PROC_SYNC_STOCK',
                                start_date          => to_date('30-11-2015 23:59:59', 'dd-mm-yyyy hh24:mi:ss'),
                                repeat_interval     => 'Freq=Monthly;Interval=1;ByMonth=Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec',
                                end_date            => to_date(null),
                                job_class           => 'DEFAULT_JOB_CLASS',
                                enabled             => true,
                                auto_drop           => false,
                                comments            => '');
end;

/