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
);