CREATE OR REPLACE PACKAGE BODY pkg_procedure_table_collect IS
/**************************************
System Sub Model：报表平台源表解析
Package Name：源表解析
Author：huxiaosi
CreateDate：2014-01-06
Description：
***************************************/
/***************************************
Procedure Name：存储过程收集
Author：huxiaosi
CreateDate：2014-01-06
Description：
***************************************/
PROCEDURE p_rpt_proedure_collect(report_name VARCHAR2) AS
-- 只获取定义job的存储过程
CURSOR cur_table IS
SELECT a.rpt_name_cn, a.table_name_result
FROM rpt_456028_proedure_collect_02 a;
v_cur_table cur_table%ROWTYPE;
v_proc_name VARCHAR2(1000);
v_sqlstate VARCHAR2(5000);
v_cost NUMBER(12); -- JOB 执行耗时
v_sysdate DATE; -- JOB开始时间
v_rowcount NUMBER(12); -- 受影响记录数
BEGIN
v_sysdate := SYSDATE;
v_sqlstate := '开始';
v_proc_name := upper('p_rpt_proedure_collect');
rpt_system.log(v_proc_name, 'OK', SYSDATE, NULL, v_sqlstate);
-- 1、获取指定报表名称对映的存储过程或包
v_sqlstate := '获取指定报表名称对映的存储过程!';
EXECUTE IMMEDIATE 'TRUNCATE TABLE rpt_456028_proedure_collect_01';
INSERT INTO rpt_456028_proedure_collect_01
SELECT a.rpt_id,
a.rpt_name_cn,
a.rpt_name_en,
a.rpt_type,
a.rpt_hierarchy_code,
a.active_flg,
a.script_cn,
a.script_tw,
a.script_en,
a.data_source
FROM udp.tm_report a
WHERE a.rpt_name_cn = report_name;
COMMIT;
--2、获取结果表名称
INSERT INTO rpt_456028_proedure_collect_02
SELECT a.rpt_id,
a.rpt_name_cn,
a.script_cn,
to_char(regexp_substr(upper(a.script_cn),
'RPT_+[A-Za-z0-9_]{1,100}')) AS table_name_result
FROM rpt_456028_proedure_collect_01 a
WHERE regexp_like(a.script_cn, 'RPT_+[A-Za-z0-9_]{1,100}', 'i');
COMMIT;
--3、获取对象名称(过程或包)
FOR v_cur_table IN cur_table LOOP
INSERT INTO rpt_456028_proedure_collect_03
SELECT a.owner, a.NAME, a.TYPE, a.text
FROM dba_source a
WHERE upper(a.text) LIKE
'%' || v_cur_table.table_name_result || '%';
COMMIT;
END LOOP;
v_rowcount := SQL%ROWCOUNT;
COMMIT;
v_cost := trunc((SYSDATE - v_sysdate) * 24 * 60);
rpt_system.log(v_proc_name,
' OK ',
SYSDATE,
v_rowcount || '@@' || v_cost || '@@RPT_OMP_EXCEPTION',
v_sqlstate);
-- 5、获取包中的存储过程
v_sqlstate := '获取包中的存储过程';
EXECUTE IMMEDIATE 'TRUNCATE TABLE rpt_456028_proedure_collect_04';
--获取包名
INSERT INTO rpt_456028_proedure_collect_04
SELECT t.owner, t.NAME, t.TYPE, t.text
FROM (SELECT a.owner,
a.NAME,
a.TYPE,
a.text,
row_number() over(PARTITION BY a.owner, a.NAME ORDER BY a.owner) rn
FROM exp5_rpt.rpt_456028_proedure_collect_03 a
WHERE a.TYPE = 'PACKAGE BODY') t
WHERE t.rn = 1;
v_rowcount := SQL%ROWCOUNT;
COMMIT;
v_cost := trunc((SYSDATE - v_sysdate) * 24 * 60);
rpt_system.log(v_proc_name,
' OK ',
SYSDATE,
v_rowcount || '@@' || v_cost || '@@RPT_OMP_EXCEPTION',
v_sqlstate);
-- 6、获取所有存储过程
v_sqlstate := '获取所有存储过程';
EXECUTE IMMEDIATE 'TRUNCATE TABLE rpt_456028_proedure_collect_05';
INSERT INTO rpt_456028_proedure_collect_05
SELECT t.owner, t.NAME, t.TYPE, t.text
FROM (SELECT a.owner,
a.NAME,
a.TYPE,
a.text,
row_number() over(PARTITION BY a.owner, a.NAME ORDER BY a.owner) rn
FROM exp5_rpt.rpt_456028_proedure_collect_03 a
WHERE a.TYPE = 'PROCEDURE') t
WHERE t.rn = 1;
v_rowcount := SQL%ROWCOUNT;
COMMIT;
v_cost := trunc((SYSDATE - v_sysdate) * 24 * 60);
rpt_system.log(v_proc_name,
' OK ',
SYSDATE,
v_rowcount || '@@' || v_cost || '@@RPT_OMP_EXCEPTION',
v_sqlstate);
-- 7、获取包中的表
v_sqlstate := '获取包中的表';
EXECUTE IMMEDIATE 'TRUNCATE TABLE RPT_456028_PROEDURE_COLLECT_06';
MERGE INTO rpt_456028_proedure_collect_06 a
USING (SELECT a.owner,
a.NAME,
a.referenced_name AS table_name,
CASE
WHEN instr(a.referenced_name, 'RPT_') > 0 AND
instr(a.referenced_name, '_TEMP') = 0 AND
instr(a.referenced_name, '_TMP') = 0 AND
instr(a.referenced_name, 'TT_') = 0 AND
instr(a.referenced_name, 'TM_') = 0 THEN
1
WHEN instr(a.referenced_name, 'RPT_') > 0 AND
(instr(a.referenced_name, '_TMP') > 0 OR
instr(a.referenced_name, '_TEMP') > 0) AND
instr(a.referenced_name, 'TT_') = 0 AND
instr(a.referenced_name, 'TM_') = 0 THEN
3
WHEN instr(a.referenced_name, 'TT_') > 0 OR
instr(a.referenced_name, 'TM_') > 0 THEN
2
ELSE
4
END AS table_type,
'' AS remark,
SYSDATE AS execdate
FROM dba_dependencies a, rpt_456028_proedure_collect_04 b
WHERE a.TYPE = b.TYPE
AND a.NAME = b.NAME
AND a.owner = b.owner
AND a.referenced_type IN ('TABLE', 'SYNONYM')) b
ON (a.owner = b.owner AND a.pkg_name = b.NAME AND a.table_name = b.table_name)
WHEN MATCHED THEN
UPDATE SET execdate = SYSDATE
WHEN NOT MATCHED THEN
INSERT
(owner, pkg_name, table_name, table_tyle, remark, execdate)
VALUES
(b.owner, b.NAME, b.table_name, b.table_type, b.remark, b.execdate);
v_rowcount := SQL%ROWCOUNT;
COMMIT;
v_cost := trunc((SYSDATE - v_sysdate) * 24 * 60);
rpt_system.log(v_proc_name,
' OK ',
SYSDATE,
v_rowcount || '@@' || v_cost || '@@RPT_OMP_EXCEPTION',
v_sqlstate);
-- 8、获取存储过程中的表
v_sqlstate := '获取存储过程中的表';
MERGE INTO rpt_456028_proedure_collect_06 a
USING (SELECT a.owner,
a.NAME,
a.referenced_name AS table_name,
CASE
WHEN instr(a.referenced_name, 'RPT_') > 0 AND
instr(a.referenced_name, '_TEMP') = 0 AND
instr(a.referenced_name, '_TMP') = 0 AND
instr(a.referenced_name, 'TT_') = 0 AND
instr(a.referenced_name, 'TM_') = 0 THEN
1
WHEN instr(a.referenced_name, 'RPT_') > 0 AND
(instr(a.referenced_name, '_TMP') > 0 OR
instr(a.referenced_name, '_TEMP') > 0) AND
instr(a.referenced_name, 'TT_') = 0 AND
instr(a.referenced_name, 'TM_') = 0 THEN
3
WHEN instr(a.referenced_name, 'TT_') > 0 OR
instr(a.referenced_name, 'TM_') > 0 THEN
2
ELSE
4
END AS table_type,
'' AS remark,
SYSDATE AS execdate
FROM dba_dependencies a, rpt_456028_proedure_collect_05 b
WHERE a.TYPE = b.TYPE
AND a.NAME = b.NAME
AND a.owner = b.owner
AND a.referenced_type IN ('TABLE', 'SYNONYM')) b
ON (a.owner = b.owner AND a.proc_name = b.NAME AND a.table_name = b.table_name)
WHEN MATCHED THEN
UPDATE SET execdate = SYSDATE
WHEN NOT MATCHED THEN
INSERT
(owner, proc_name, table_name, table_tyle, remark, execdate)
VALUES
(b.owner, b.NAME, b.table_name, b.table_type, b.remark, b.execdate);
v_rowcount := SQL%ROWCOUNT;
COMMIT;
v_cost := trunc((SYSDATE - v_sysdate) * 24 * 60);
rpt_system.log(v_proc_name,
' OK ',
SYSDATE,
v_rowcount || '@@' || v_cost || '@@RPT_OMP_EXCEPTION',
v_sqlstate);
-- 9、汇总存储过程
v_sqlstate := '汇总存储过程';
EXECUTE IMMEDIATE 'TRUNCATE TABLE RPT_456028_PROEDURE_COLLECT_07';
INSERT INTO rpt_456028_proedure_collect_07
(owner, pkg_name, proc_name, table_name, table_tyle, execdate)
SELECT t.owner,
t.pkg_name,
t.proc_name,
t.table_name,
t.table_tyle,
t.execdate
FROM (SELECT a.owner,
a.pkg_name,
a.proc_name,
a.table_name,
a.table_tyle,
a.execdate,
row_number() over(PARTITION BY a.owner, a.table_name ORDER BY a.table_name) rn
FROM rpt_456028_proedure_collect_06 a) t
WHERE t.rn = 1;
v_rowcount := SQL%ROWCOUNT;
COMMIT;
v_cost := trunc((SYSDATE - v_sysdate) * 24 * 60);
rpt_system.log(v_proc_name,
' OK ',
SYSDATE,
v_rowcount || '@@' || v_cost || '@@RPT_OMP_EXCEPTION',
v_sqlstate);
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
dbms_output.put_line(v_sqlstate || '-' || SQLERRM);
rpt_system.log(v_proc_name,
' ERROR - ' || v_sqlstate,
SYSDATE,
SQLCODE,
substr(SQLERRM, 1, 1000));
END p_rpt_proedure_collect;
/***************************************
Procedure Name：存储过程清除报表
Author：huxiaosi
Param：in_type 1、记录表
2、关联表
CreateDate：2014-01-06
Description：
***************************************/
PROCEDURE p_rpt_proedure_table_report(in_type NUMBER,
cur_result OUT cur_report) AS
BEGIN
IF in_type = 1 THEN
OPEN cur_result FOR
SELECT a.owner AS 拥有者,
a.pkg_name AS 包名称,
a.proc_name AS 过程名称,
a.table_name AS 表名称,
decode(a.table_tyle,
1,
'结果表',
2,
'源表',
3,
'临时表',
4,
'其它表') AS 类型名称,
a.execdate AS 执行时间
FROM rpt_456028_proedure_collect_07 a;
ELSE
OPEN cur_result FOR
SELECT a.owner AS 拥有者,
a.pkg_name AS 包名称,
a.proc_name AS 过程名称,
a.table_name AS 表名称,
decode(a.table_tyle,
1,
'结果表',
2,
'源表',
3,
'临时表',
4,
'其它表') AS 类型名称,
a.execdate AS 执行时间
FROM rpt_456028_proedure_collect_07 a;
END IF;
END p_rpt_proedure_table_report;
END pkg_procedure_table_collect;