报表分片运算调度控制台

CREATE OR REPLACE PACKAGE qms_rpt_operating_station_pub AS
/******************************************************************************
NAME: qms_rpt_operating_station_pub
PURPOSE: 报表分片运算调度控制台

REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 2015/7/25 zhangwq7 1. Created this package.
******************************************************************************/

g_slicing_cal_job_count NUMBER := 3; ---分片job数
g_sliced_data_cal_job_count NUMBER := 10; --分片数据运算job数
g_total_data_cal_job_count NUMBER := 5; --总体数据预算job数
g_report_job_remark_length NUMBER := 4000; --job备注的长度
g_slice_interval NUMBER := 1;
PROCEDURE log(p_report_job_id VARCHAR2
,p_report_id VARCHAR2
,p_silace_id VARCHAR2
,p_remark VARCHAR2) ;
/******************************************************************************
NAME: sliceing_cal_scheduling
PURPOSE: 把待运算报表JOB切片调度
******************************************************************************/
PROCEDURE slicing_cal_scheduling(p_report_job_id VARCHAR2
,p_mod_num NUMBER);

/******************************************************************************
NAME: sliced_data_cal_scheduling
PURPOSE: 把待运算的切片JOB调度（报表已经完成切片）
******************************************************************************/
PROCEDURE sliced_data_cal_scheduling(p_report_job_id VARCHAR2
,p_mod_num NUMBER);

/******************************************************************************
NAME: total_data_cal_scheduling
PURPOSE: 把待运算的总体报表结果JOB调度（报表已经完成切片运算）
******************************************************************************/
PROCEDURE total_data_cal_scheduling(p_report_job_id VARCHAR2
,p_mod_num NUMBER);

/******************************************************************************
NAME: common_slicing_cal
PURPOSE: 通用的报表切片逻辑
******************************************************************************/
PROCEDURE common_slicing_cal(p_report_id VARCHAR2);

/******************************************************************************
NAME: main
PURPOSE: 报表分片运算调度控制台入口
******************************************************************************/
PROCEDURE main;
END;
/
CREATE OR REPLACE PACKAGE BODY qms_rpt_operating_station_pub AS
/******************************************************************************
NAME: qms_rpt_operating_station_pub
PURPOSE: 报表分片运算调度控制台

REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 2015/7/25 zhangwq7 1. Created this package.
******************************************************************************/

/******************************************************************************
NAME: headquarters
PURPOSE: 司令部
******************************************************************************/
PROCEDURE log(p_report_job_id VARCHAR2
,p_report_id VARCHAR2
,p_silace_id VARCHAR2
,p_remark VARCHAR2) IS
BEGIN
INSERT INTO qms_report_job_log
(report_job_id
,report_id
,silace_id
,p_remark
,log_date)
VALUES
(p_report_job_id
,p_report_id
,p_silace_id
,substr(p_remark, 1, 4000)
,SYSDATE);
END;

/******************************************************************************
NAME: headquarters
PURPOSE: 司令部
******************************************************************************/
PROCEDURE headquarters IS
v_job_type VARCHAR2(200);
v_job_what VARCHAR2(500);
v_report_job_id VARCHAR2(32);
v_dbms_job_id NUMBER;
BEGIN
--把已经完成的job remove
UPDATE qms_report_job qrj
SET job_end = SYSDATE
,remark = substr('当前job不存于user_jobs,' || remark,
1,
g_report_job_remark_length)
WHERE job_end IS NULL
AND NOT EXISTS
(SELECT 1 FROM user_jobs WHERE job = qrj.job_id);

COMMIT;
/* 系统会自行移除job
FOR c IN (SELECT id
,job_id
,remark
FROM qms_report_job qrj
WHERE job_end IS NULL
AND EXISTS
(SELECT 1
FROM user_jobs
WHERE job = qrj.job_id
AND (broken = 'Y' OR last_date IS NULL))) LOOP
BEGIN
dbms_job.remove(c.job_id);

UPDATE qms_report_job
SET job_end = SYSDATE
,remark = substr('dbms_job.remove成功,' || remark,
1,
g_report_job_remark_length)
WHERE id = c.id;
EXCEPTION
WHEN OTHERS THEN
v_sqlerrm := substr(SQLERRM, 1, 200);

UPDATE qms_report_job
SET job_end = SYSDATE
,remark = substr('dbms_job.remove失败,' || v_sqlerrm || ',' ||
remark,
1,
g_report_job_remark_length)
WHERE id = c.id;
END;

COMMIT;
END LOOP;
*/

----报表切片job走起begin-------------------
v_job_type := 'qms_rpt_operating_station_pub.slicing_cal_scheduling';

--根据job配置数跑切片job
FOR c IN (SELECT row_num
FROM (SELECT rownum - 1 row_num
FROM user_col_comments
WHERE rownum <= g_slicing_cal_job_count) t /*待跑的mod*/
WHERE NOT EXISTS (SELECT 1
FROM qms_report_job
WHERE mod_num = t.row_num
AND job_end IS NULL
AND job_type = v_job_type) /*排除正在跑的mod*/
AND EXISTS
(SELECT 1
FROM qms_report_request_log2
WHERE progress_status = 'I' and state = 'A'
AND MOD(report_sequence, g_slicing_cal_job_count) =
t.row_num) /*待跑数据的余数落在待跑的mod里*/
ORDER BY row_num) LOOP
v_report_job_id := sys_guid();
v_job_what := v_job_type || '(''' || v_report_job_id || ''',' ||
c.row_num || ');';

BEGIN
dbms_job.submit(v_dbms_job_id, v_job_what, SYSDATE);

INSERT INTO qms_report_job
(id
,mod_num
,job_type
,job_what
,job_id
,job_start)
VALUES
(v_report_job_id
,c.row_num
,v_job_type
,v_job_what
,v_dbms_job_id
,SYSDATE);

COMMIT;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;

----报表切片job end-------------------

----报表切片数据计算job走起begin-------------------
v_job_type := 'qms_rpt_operating_station_pub.sliced_data_cal_scheduling';

--根据job配置数跑切片job
FOR c IN (SELECT row_num
FROM (SELECT rownum - 1 row_num
FROM user_col_comments
WHERE rownum <= g_sliced_data_cal_job_count) t /*待跑的mod*/
WHERE NOT EXISTS (SELECT 1
FROM qms_report_job
WHERE mod_num = t.row_num
AND job_end IS NULL
AND job_type = v_job_type) /*排除正在跑的mod*/
AND EXISTS
(SELECT 1
FROM qms_calculate_criteria_slice2 qccs
,qms_report_request_log2 qrrl
,qms_report_type qrt
WHERE qrt.report_type = qrrl.report_type
AND qccs.qms_report_request_log_id = qrrl.id
AND qccs.progress_status = 'I'
AND qrrl.progress_status IN ('R', 'S')
AND qccs.status = '1'
AND qccs.progress_cur_qty = 0
AND MOD(qccs.report_sequence,
g_sliced_data_cal_job_count) = t.row_num) /*待跑数据的余数落在待跑的mod里*/
ORDER BY row_num) LOOP
v_report_job_id := sys_guid();
v_job_what := v_job_type || '(''' || v_report_job_id || ''',' ||
c.row_num || ');';

BEGIN
dbms_job.submit(v_dbms_job_id, v_job_what, SYSDATE);

INSERT INTO qms_report_job
(id
,mod_num
,job_type
,job_what
,job_id
,job_start)
VALUES
(v_report_job_id
,c.row_num
,v_job_type
,v_job_what
,v_dbms_job_id
,SYSDATE);

COMMIT;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;

----报表切片数据计算job end-------------------

----报表总体数据计算job走起begin-------------------
v_job_type := 'qms_rpt_operating_station_pub.total_data_cal_scheduling';

--根据job配置数跑切片job

FOR c IN (SELECT row_num
FROM (SELECT rownum - 1 row_num
FROM user_col_comments
WHERE rownum <= g_total_data_cal_job_count) t /*待跑的mod*/
WHERE NOT EXISTS (SELECT 1
FROM qms_report_job
WHERE mod_num = t.row_num
AND job_end IS NULL
AND job_type = v_job_type) /*排除正在跑的mod*/
AND EXISTS
(SELECT 1
FROM qms_report_request_log2 qrrl
,qms_report_type qrt
WHERE qrt.report_type = qrrl.report_type
AND qrrl.progress_status IN ('S', 'R')
AND MOD(report_sequence, g_total_data_cal_job_count) =
t.row_num
AND NOT EXISTS
(SELECT 1
FROM qms_calculate_criteria_slice2 qccs
WHERE qccs.qms_report_request_log_id = qrrl.id
AND qccs.progress_status NOT IN ('E', 'D'))) /*待跑数据的余数落在待跑的mod里*/
ORDER BY row_num) LOOP
v_report_job_id := sys_guid();
v_job_what := v_job_type || '(''' || v_report_job_id || ''',' ||
c.row_num || ');';

BEGIN
dbms_job.submit(v_dbms_job_id, v_job_what, SYSDATE);

INSERT INTO qms_report_job
(id
,mod_num
,job_type
,job_what
,job_id
,job_start)
VALUES
(v_report_job_id
,c.row_num
,v_job_type
,v_job_what
,v_dbms_job_id
,SYSDATE);

COMMIT;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
----报表总体数据计算job end-------------------
END;

/******************************************************************************
NAME: sliceing_cal_scheduling
PURPOSE: 把待运算报表JOB切片调度
******************************************************************************/
PROCEDURE slicing_cal_scheduling(p_report_job_id VARCHAR2
,p_mod_num NUMBER) IS
v_execute_what VARCHAR2(500);
v_sqlerrm VARCHAR2(500);
v_progress_status VARCHAR2(50);
v_sqlcount NUMBER;
BEGIN
--把失效的报表排除掉
UPDATE qms_report_request_log2 qrrl
SET report_created_date = SYSDATE
,progress_status = 'C'
,progress_desc = 'qms_report_type配置了此类报表无效.'
WHERE report_type IN
(SELECT report_type
FROM qms_report_type qrt
WHERE qrt.report_type = qrrl.report_type
AND nvl(qrt.state, 'X') != 'A')
AND qrrl.progress_status = 'I';

COMMIT;

FOR c IN (SELECT qrt.slicing_proc
,qrrl.id
FROM qms_report_request_log2 qrrl
,qms_report_type qrt
WHERE qrt.report_type = qrrl.report_type
AND qrrl.progress_status = 'I'
AND MOD(report_sequence, g_slicing_cal_job_count) =
p_mod_num
AND NOT EXISTS
(SELECT 1
FROM qms_calculate_criteria_slice2 qccs
WHERE qccs.qms_report_request_log_id = qrrl.id)
ORDER BY qrrl.datetime_created) LOOP
--锁定数据
SELECT progress_status
INTO v_progress_status
FROM qms_report_request_log2
WHERE id = c.id
FOR UPDATE;
log(p_report_job_id,c.id,'1. slicing_status','progress_status = S');
UPDATE qms_report_request_log2
SET progress_status = 'S'
WHERE id = c.id
AND progress_status = 'I';

v_sqlcount := SQL%ROWCOUNT;
COMMIT;

IF v_sqlcount > 0 THEN
v_execute_what := 'begin ' || c.slicing_proc || '(''' || c.id ||
''');end;';
dbms_output.put_line(v_execute_what);
v_sqlerrm := '切片调度';

log(p_report_job_id,c.id,'2. slicing_what',v_execute_what);
BEGIN
EXECUTE IMMEDIATE v_execute_what;

log(p_report_job_id,c.id,'3. slicing_executed',v_execute_what);
EXCEPTION
WHEN OTHERS THEN
v_sqlerrm := '切片调度失败,' || substr(SQLERRM, 1, 150);

UPDATE qms_report_request_log2
SET progress_status = 'E'
,progress_desc = v_sqlerrm
WHERE id = c.id;
END;

log(p_report_job_id,
c.id,
NULL,
v_execute_what || ',' || v_sqlerrm);

COMMIT;
END IF;
END LOOP;
END;

/******************************************************************************
NAME: sliced_data_cal_scheduling
PURPOSE: 把待运算的切片JOB调度（报表已经完成切片）
******************************************************************************/
PROCEDURE sliced_data_cal_scheduling(p_report_job_id VARCHAR2
,p_mod_num NUMBER) IS
v_execute_what VARCHAR2(500);
v_sqlerrm VARCHAR2(500);
v_progress_status VARCHAR(50);
v_sqlcount NUMBER;
BEGIN
--报表运行过程中只要有一片是错误的,则此报表就是错误的
UPDATE qms_report_request_log2 rrl
SET rrl.progress_status = 'E'
WHERE rrl.id IN
(SELECT DISTINCT qrrl.id
FROM qms_calculate_criteria_slice2 qccs
,qms_report_request_log2 qrrl
WHERE qccs.qms_report_request_log_id = qrrl.id
AND qrrl.progress_status IN ('R', 'S')
AND qccs.progress_status = 'E'
AND MOD(qccs.report_sequence, g_sliced_data_cal_job_count) =
p_mod_num);

UPDATE qms_calculate_criteria_slice2 ccs
SET ccs.progress_status = 'C'
WHERE ccs.id IN
(SELECT DISTINCT qccs.id
FROM qms_calculate_criteria_slice2 qccs
,qms_report_request_log2 qrrl
WHERE qccs.qms_report_request_log_id = qrrl.id
AND qrrl.progress_status = 'E'
AND qccs.progress_status = 'I'
AND MOD(qccs.report_sequence, g_sliced_data_cal_job_count) =
p_mod_num);

COMMIT;

--片数据运算
FOR c IN (SELECT qrt.sliced_data_proc
,qccs.id
,qrrl.id AS report_id
FROM qms_calculate_criteria_slice2 qccs
,qms_report_request_log2 qrrl
,qms_report_type qrt
WHERE qrt.report_type = qrrl.report_type
AND qccs.qms_report_request_log_id = qrrl.id
AND qccs.progress_status = 'I'
AND qrrl.progress_status IN ('R', 'S')
AND qccs.status = '1'
AND qccs.progress_cur_qty = 0
AND MOD(qccs.report_sequence, g_sliced_data_cal_job_count) =
p_mod_num
ORDER BY qrrl.datetime_created
,qccs.qms_report_request_log_id) LOOP
--锁定资料
SELECT progress_status
INTO v_progress_status
FROM qms_calculate_criteria_slice2
WHERE id = c.id
FOR UPDATE;

UPDATE qms_calculate_criteria_slice2
SET progress_status = 'S'
WHERE id = c.id
AND progress_status = 'I';

v_sqlcount := SQL%ROWCOUNT;
COMMIT;

IF v_sqlcount > 0 THEN
v_sqlerrm := '切片运算';

BEGIN
v_execute_what := 'begin ' || c.sliced_data_proc || '(''' || c.id ||
''');end;';

EXECUTE IMMEDIATE v_execute_what;
EXCEPTION
WHEN OTHERS THEN
v_sqlerrm := '切片运算调度失败,' || substr(SQLERRM, 1, 150);

UPDATE qms_calculate_criteria_slice2
SET progress_status = 'E'
,progress_desc = v_sqlerrm
WHERE id = c.id;
END;

log(p_report_job_id,
c.report_id,
c.id,
v_execute_what || ',' || v_sqlerrm);
COMMIT;
END IF;
END LOOP;
END;

/******************************************************************************
NAME: total_data_cal_scheduling
PURPOSE: 把待运算的总体报表结果JOB调度（报表已经完成切片运算）
******************************************************************************/
PROCEDURE total_data_cal_scheduling(p_report_job_id VARCHAR2
,p_mod_num NUMBER) IS
v_execute_what VARCHAR2(500);
v_sqlerrm VARCHAR2(200);
v_progress_status VARCHAR(50);
v_sqlcount NUMBER;
BEGIN
FOR c IN (SELECT qrt.total_data_proc
,qrrl.id
FROM qms_report_request_log2 qrrl
,qms_report_type qrt
WHERE qrt.report_type = qrrl.report_type
AND qrrl.progress_status IN ( 'R')
AND MOD(report_sequence, g_total_data_cal_job_count) =
p_mod_num
AND NOT EXISTS
(SELECT 1
FROM qms_calculate_criteria_slice2 qccs
WHERE qccs.qms_report_request_log_id = qrrl.id
AND qccs.progress_status NOT IN ('E', 'D'))
ORDER BY qrrl.datetime_created) LOOP
--锁定资料
SELECT progress_status
INTO v_progress_status
FROM qms_report_request_log2
WHERE id = c.id
FOR UPDATE;

UPDATE qms_report_request_log2
SET progress_status = 'R'
WHERE id = c.id
AND progress_status IN ('S', 'R');

v_sqlcount := SQL%ROWCOUNT;
COMMIT;

IF v_sqlcount > 0 THEN
v_sqlerrm := '总体运算.';

BEGIN
v_execute_what := 'begin ' || c.total_data_proc || '(''' || c.id ||
''');end;';

EXECUTE IMMEDIATE v_execute_what;
EXCEPTION
WHEN OTHERS THEN
v_sqlerrm := '总体运算失败,' || substr(SQLERRM, 1, 150);

UPDATE qms_report_request_log2
SET progress_status = 'E'
,progress_desc = v_sqlerrm
WHERE id = c.id;
END;

log(p_report_job_id,
c.id,
NULL,
v_execute_what || ',' || v_sqlerrm);
COMMIT;
END IF;
END LOOP;
END;

/******************************************************************************
NAME: common_slicing_cal
PURPOSE: 通用的报表切片逻辑
******************************************************************************/
PROCEDURE common_slicing_cal(p_report_id VARCHAR2) IS
v_fault_date_from DATE;
v_fault_date_to DATE;
v_temp_date_from DATE;
v_temp_date_to DATE;
v_days NUMBER;
v_days2 NUMBER;
v_times NUMBER := 0;
i NUMBER;
x_ret_msg VARCHAR(4000);
v_guid VARCHAR2(32);
v_total_days NUMBER := 0;
v_criteria_slice_row qms_calculate_criteria_slice2%ROWTYPE;
vv_temp_date VARCHAR2(20);
v_slice_interval NUMBER := g_slice_interval;

CURSOR cur_c IS
SELECT *
FROM qms_report_request_log2
WHERE progress_status = 'S'
AND id = p_report_id;
BEGIN
FOR cur IN cur_c LOOP
BEGIN
IF cur.report_type IN ('KEY_PARTS_FAILURE_DIST') THEN
v_slice_interval := 30;
END IF;

BEGIN
v_fault_date_from := to_date(qms_rpt_utl.get_criteria_by_key(cur.id,
'SETUP_DATE_FROM'),
'yyyy-MM-dd hh24:mi:ss');

v_fault_date_to := to_date(qms_rpt_utl.get_criteria_by_key(cur.id,
'SETUP_DATE_TO'),
'yyyy-MM-dd hh24:mi:ss');

IF v_fault_date_from IS NULL THEN
v_fault_date_from := to_date(qms_rpt_utl.get_criteria_by_key(cur.id,
'PRODUCE_DATE_FROM'),
'yyyy-MM-dd hh24:mi:ss');

v_fault_date_to := to_date(qms_rpt_utl.get_criteria_by_key(cur.id,
'PRODUCE_DATE_TO'),
'yyyy-MM-dd hh24:mi:ss');
END IF;

qms_rpt_utl.calc_day_interval(p_begin_date => v_fault_date_from,
p_end_date => v_fault_date_to,
p_interval => v_slice_interval,
x_day_interval => v_days,
x_trunc => v_times);

v_temp_date_from := v_fault_date_from;

FOR i IN 1 .. v_times LOOP
SELECT v_temp_date_from + v_slice_interval
INTO v_temp_date_to
FROM dual;

IF (v_temp_date_to > v_fault_date_to) THEN
v_temp_date_to := v_fault_date_to;
END IF;

SELECT sys_guid() INTO v_guid FROM dual;

SELECT round(to_number(v_temp_date_to - v_temp_date_from))
INTO v_days2
FROM dual;

--切片按日期分段查询，格式化日期格式-----------------------------------
vv_temp_date := to_char(v_temp_date_from, 'yyyy/MM/DD') ||
' 00:00:00';
v_temp_date_from := to_date(vv_temp_date,
'yyyy/MM/DD hh24:mi:ss');

vv_temp_date := to_char(v_temp_date_to, 'yyyy/MM/DD') ||
' 23:59:59';
v_temp_date_to := to_date(vv_temp_date, 'yyyy/MM/DD hh24:mi:ss');

IF v_days2 >= 0 THEN
v_criteria_slice_row.id := v_guid;
v_criteria_slice_row.qms_report_request_log_id := cur.id;
v_criteria_slice_row.slice_date_from := v_temp_date_from;
v_criteria_slice_row.slice_date_to := v_temp_date_to;
v_criteria_slice_row.progress_status := 'I';
v_criteria_slice_row.progress_cur_qty := 0;
v_criteria_slice_row.progress_total_qty := v_days2 + 1;
v_criteria_slice_row.status := '1';
v_criteria_slice_row.datetime_created := SYSDATE;

SELECT seq_report.nextval
INTO v_criteria_slice_row.report_sequence
FROM dual;

INSERT INTO qms_calculate_criteria_slice2
VALUES v_criteria_slice_row;

v_total_days := v_total_days +
v_criteria_slice_row.progress_total_qty;
END IF;

v_temp_date_from := v_temp_date_to + 1;
END LOOP;

UPDATE qms_report_request_log2
SET progress_status = 'S'
,progress_total_qty = v_total_days
WHERE id = cur.id;

COMMIT;
EXCEPTION
WHEN OTHERS THEN
x_ret_msg := SQLERRM;

UPDATE qms_report_request_log2
SET progress_status = 'E'
,progress_desc = x_ret_msg
WHERE id = cur.id;
END;
END;
END LOOP;

COMMIT;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;

/******************************************************************************
NAME: main
PURPOSE: 报表分片运算调度控制台入口
******************************************************************************/
PROCEDURE main IS
BEGIN
headquarters;
END;
END;
/

　　

-- Create table
create table QMS_REPORT_REQUEST_LOG2
(
  id                     VARCHAR2(32) not null,
  evaluate_no            NVARCHAR2(80) not null,
  report_type            NVARCHAR2(400) not null,
  report_created_date    DATE default SYSDATE not null,
  request_user           VARCHAR2(200),
  state                  CHAR(1) default 'A' not null,
  user_created           VARCHAR2(60) default 'SYS' not null,
  datetime_created       DATE default SYSDATE not null,
  user_modified          VARCHAR2(60),
  datetime_modified      DATE,
  report_desc            VARCHAR2(400),
  progress_status        VARCHAR2(20),
  progress_desc          VARCHAR2(180),
  progress_cur_qty       NUMBER,
  progress_total_qty     NUMBER,
  report_sequence        NUMBER,
  total_records          NUMBER,
  datetime_calc_finished DATE,
  config_id              VARCHAR2(32)
)
tablespace QMSD
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the columns 
comment on column QMS_REPORT_REQUEST_LOG2.config_id
  is '推送配置ID';

　　

-- Create table
create table QMS_CALCULATE_CRITERIA
(
  id                        VARCHAR2(32) default SYS_GUID() not null,
  qms_report_request_log_id NVARCHAR2(80) not null,
  parameter_key             VARCHAR2(80) not null,
  parameter_value           NVARCHAR2(400) not null,
  state                     CHAR(1) default 'A' not null,
  user_created              VARCHAR2(60) default 'SYS' not null,
  datetime_created          DATE default SYSDATE not null,
  user_modified             VARCHAR2(60),
  datetime_modified         DATE,
  order_by                  NUMBER
)
tablespace QMSD
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the columns 
comment on column QMS_CALCULATE_CRITERIA.qms_report_request_log_id
  is '评估编号';
comment on column QMS_CALCULATE_CRITERIA.parameter_key
  is '参数名';
comment on column QMS_CALCULATE_CRITERIA.parameter_value
  is '参数值';
-- Create/Recreate indexes 
create index QMS_CALCULATE_CRITERIA_IX1 on QMS_CALCULATE_CRITERIA (QMS_REPORT_REQUEST_LOG_ID)
  tablespace QMSD
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table QMS_CALCULATE_CRITERIA
  add constraint QMS_CALCULATE_CRITERIA_PK primary key (ID)
  using index 
  tablespace QMSD
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Grant/Revoke object privileges 
grant select on QMS_CALCULATE_CRITERIA to QMS_READYONLY;

-- Create table
create table QMS_CALCULATE_CRITERIA_SLICE2
(
  id                        VARCHAR2(32) not null,
  qms_report_request_log_id VARCHAR2(32) not null,
  slice_date_from           DATE,
  slice_date_to             DATE,
  progress_status           CHAR(1),
  user_created              VARCHAR2(60),
  datetime_created          DATE,
  user_modified             VARCHAR2(60),
  datetime_modified         DATE,
  order_by                  NUMBER,
  progress_desc             VARCHAR2(180),
  progress_cur_qty          NUMBER,
  progress_total_qty        NUMBER,
  exec_group                VARCHAR2(80),
  report_sequence           NUMBER,
  status                    VARCHAR2(20),
  start_date                DATE,
  end_date                  DATE,
  state                     CHAR(1),
  slice_group               VARCHAR2(32)
)
tablespace QMSD
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

　　

-- Create table
create table QMS_REPORT_TYPE
(
  report_type      NVARCHAR2(400) not null,
  report_title     VARCHAR2(400),
  slicing_proc     VARCHAR2(100),
  sliced_data_proc VARCHAR2(100),
  total_data_proc  VARCHAR2(100),
  state            CHAR(1) default 'A' not null
)
tablespace QMSD
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the columns 
comment on column QMS_REPORT_TYPE.report_type
  is '报表代码';
comment on column QMS_REPORT_TYPE.report_title
  is '报表描述';
comment on column QMS_REPORT_TYPE.slicing_proc
  is '可调用的报表切片的Procedure';
comment on column QMS_REPORT_TYPE.sliced_data_proc
  is '可调用的每片运算Procedure';
comment on column QMS_REPORT_TYPE.total_data_proc
  is '可调用的总体运算Procedure';
comment on column QMS_REPORT_TYPE.state
  is 'A,有效;其他,无效';
-- Create/Recreate primary, unique and foreign key constraints 
alter table QMS_REPORT_TYPE
  add constraint QMS_REPORT_TYPE_PK primary key (REPORT_TYPE)
  using index 
  tablespace QMSD
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );