create or replace package PKG_IPLAN_UTIL is
/******************************************************************************
$header$ PKG_IPLAN_UTIL 80325612 2021-07-16 V1.001
NAME: PKG_IPLAN_UTIL
PURPOSE: 工具包
REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.000 2021-03-12 80325612 Created this package.
1.001 2021-07-16 80325612 增加删分区过程
******************************************************************************/
gv_schema_name varchar2(20) := 'SOPMGR';
gv_module_name varchar2(20) := 'PKG_IPLAN_UTIL';
/**
* 根据字符串公式内容计算数值
* added by 80325612 2021/6/3
**/
FUNCTION char_to_computing(p_InputStr varchar2) RETURN NUMBER;
/**
* 查询表字段的默认值
* added by 80325612 2021/3/22
**/
FUNCTION f_get_column_default(p_table_name in VARCHAR,
p_column_name in VARCHAR2) RETURN VARCHAR;
/**
* 删除表
* added by 80325612 2021/2/7
**/
PROCEDURE drop_table(p_oflow_id in varchar2,
p_oflow_name in varchar2,
p_err_code out number,
p_table_name in varchar2);
/**
* 删除索引
* added by 80325612 2021/2/7
**/
PROCEDURE drop_index(p_oflow_id in varchar2,
p_oflow_name in varchar2,
p_err_code out number,
p_index_name in varchar2);
/**
* 删除序列
* added by 80325612 2021/2/7
**/
PROCEDURE drop_sequence(p_oflow_id in varchar2,
p_oflow_name in varchar2,
p_err_code out number,
p_sequence_name in varchar2);
/**
* 失效索引
* added by 80325612 2021/2/7
**/
PROCEDURE p_unusable_index(p_oflow_id in varchar2,
p_oflow_name in varchar2,
p_err_code out number,
p_index_name in varchar2);
/**
* 失效表索引
* added by 80325612 2021/2/7
**/
PROCEDURE p_unusable_table_indexes(p_oflow_id in varchar2,
p_oflow_name in varchar2,
p_err_code out number,
p_table_name in varchar2);
/**
* 重建索引
* added by 80325612 2021/2/7
**/
PROCEDURE p_rebuild_index(p_oflow_id in varchar2,
p_oflow_name in varchar2,
p_err_code out number,
p_index_name in varchar2,
p_degree in number);
/**
* 重建表索引
* added by 80325612 2021/2/7
**/
PROCEDURE p_rebuild_table_indexes(p_oflow_id in varchar2,
p_oflow_name in varchar2,
p_err_code out number,
p_table_name in varchar2,
p_degree in number);
/**
* 重建序列
* added by 80325612 2021/2/7
**/
PROCEDURE rebuild_sequence(p_oflow_id in varchar2,
p_oflow_name in varchar2,
p_err_code out number,
p_sequence_name in varchar2,
p_start_with in number,
p_increment_by in number);
/**
* 新增分区
* added by 80325612 2021/5/7
**/
PROCEDURE p_add_partition(p_oflow_id in varchar2,
p_oflow_name in varchar2,
p_err_code out number,
p_table_name in varchar2,
p_partition_name in varchar2,
p_partition_value in varchar2);
/**
* 清空表数据
* added by 80325612 2021/4/29
**/
PROCEDURE p_truncate_table(p_oflow_id in varchar2,
p_oflow_name in varchar2,
p_err_code out number,
p_table_name in varchar2);
/**
* 删除分区
* added by 80325612 2021/4/26
**/
PROCEDURE p_drop_partition(p_oflow_id in varchar2,
p_oflow_name in varchar2,
p_err_code out number,
p_table_name in varchar2,
p_partition_name in varchar2);
/**
* 清空分区数据
* added by 80325612 2021/4/26
**/
PROCEDURE p_truncate_partition(p_oflow_id in varchar2,
p_oflow_name in varchar2,
p_err_code out number,
p_table_name in varchar2,
p_partition_name in varchar2);
/**
* 备份历史
* added by 80325612 2021/4/26
**/
PROCEDURE p_backup_table(p_oflow_id in varchar2,
p_oflow_name in varchar2,
p_err_code out number,
p_table_name in varchar2,
p_his_table_name in varchar2,
p_backup_days in number default 180);
end PKG_IPLAN_UTIL;
create or replace package body PKG_IPLAN_UTIL is
/**
* 根据字符串公式内容计算数值
* added by 80325612 2021/6/3
**/
FUNCTION char_to_computing(p_InputStr varchar2) RETURN NUMBER AS
TYPE ArrayList_Type IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
TYPE VarList IS VARRAY(100) OF VARCHAR2(100);
temp ArrayList_Type;
st VarList := VarList('');
value VarList := VarList('');
n number;
cnt number;
tempResult number;
inputStr varchar2(1000);
tempStr varchar2(1000);
operatorStr varchar2(100);
ts varchar2(400);
output number;
operatorType varchar2(100);
begin
InputStr := p_InputStr;
tempStr := substr(inputStr, 1, 1);
n := 1;
cnt := 1;
operatorType := '[\*|/|+|-|(|)]';
--判断是否为可计算数据
if length(REGEXP_replace(REGEXP_replace(InputStr, operatorType, ''),
'\d|.',
'')) > 0 then
output := -999999;
return output;
end if;
--判断是否为计算式
if REGEXP_INSTR(InputStr, operatorType) = 0 then
output := to_number(InputStr);
return output;
end if;
--分解
loop
<<continue_loop_1>>
n := n + 1;
tempStr := tempStr || substr(inputStr, n, 1);
if n > length(inputStr) then
goto exit_loop_1;
end if;
if nvl(length(replace(translate(tempStr, '0123456789', '.'), '.')), 0) = 0 then
temp(cnt) := tempStr;
goto continue_loop_1;
else
if length(tempStr) > 1 then
n := n - 1;
temp(cnt) := substr(tempStr, 1, length(tempStr) - 1);
else
temp(cnt) := tempStr;
end if;
cnt := cnt + 1;
tempStr := '';
end if;
end loop;
<<exit_loop_1>>
--逆波兰式
n := 0;
loop
st.trim(1);
value.trim(1);
<<continue_loop_2>>
n := n + 1;
if n > temp.count then
goto exit_loop_2;
end if;
if temp(n) in ('+', '-', '*', '/', '(', ')') then
--5
if st.count > 0 then
operatorStr := st(st.count);
--4
if temp(n) in ('*', '/') then
--3
if operatorStr = '(' then
st.extend(1);
st(st.count) := temp(n);
goto continue_loop_2;
else
--2
if operatorStr in ('*', '/') then
value.extend(1);
value(value.count) := st(st.count);
st(st.count) := temp(n); --移除
goto continue_loop_2;
else
st.extend(1);
st(st.count) := temp(n);
goto continue_loop_2;
end if;
--2
end if;
--3
else
--3
if temp(n) in ('+', '-') then
--2
if operatorStr = '(' then
st.extend(1);
st(st.count) := temp(n);
goto continue_loop_2;
else
value.extend(1);
value(value.count) := st(st.count);
st.trim(1);
--1
if st.count > 0 then
if st(st.count) != '(' then
value.extend(1);
value(value.count) := st(st.count);
st.trim(1);
end if;
end if;
--1
st.extend(1);
st(st.count) := temp(n);
goto continue_loop_2;
end if;
--2
else
--2
if temp(n) = '(' then
st.extend(1);
st(st.count) := temp(n);
goto continue_loop_2;
else
--1
if n = temp.count then
value.extend(1);
value(value.count) := st(st.count);
st.trim(2);
while st.count > 0 loop
if st(st.count) not in ('(', ')') then
value.extend(1);
value(value.count) := st(st.count);
end if;
st.trim(1);
end loop;
goto exit_loop_2;
else
value.extend(1);
value(value.count) := st(st.count);
st.trim(2);
goto continue_loop_2;
end if;
--1
end if;
--2
end if;
--3
end if;
--4
else
st.extend(1);
st(st.count) := temp(n);
goto continue_loop_2;
end if;
--5
else
--5
if n = temp.count then
value.extend(1);
value(value.count) := temp(n);
value.extend(1);
value(value.count) := st(st.count);
st.trim(1);
while st.count > 0 loop
if st(st.count) not in ('(', ')') then
value.extend(1);
value(value.count) := st(st.count);
end if;
st.trim(1);
end loop;
goto exit_loop_2;
else
value.extend(1);
value(value.count) := temp(n);
goto continue_loop_2;
end if;
--5
end if;
end loop;
<<exit_loop_2>>
--计算
--result := 0;
tempResult := 0;
loop
<<continue_loop_3>>
if value.count < 2 then
goto exit_loop_3;
end if;
n := 0;
loop
<<continue_loop_4>>
n := n + 1;
if n > value.count then
goto continue_loop_3;
end if;
if value(n) = '+' then
tempResult := to_number(value(n - 2)) + to_number(value(n - 1));
else
if value(n) = '-' then
tempResult := to_number(value(n - 2)) - to_number(value(n - 1));
else
if value(n) = '*' then
tempResult := to_number(value(n - 2)) * to_number(value(n - 1));
else
if value(n) = '/' then
tempResult := to_number(value(n - 2)) /
to_number(value(n - 1));
else
goto continue_loop_4;
end if;
end if;
end if;
end if;
select cast(tempResult as varchar2(40)) into ts from dual;
value(n - 2) := ts;
for i in n - 1 .. value.count - 2 loop
value(i) := value(i + 2);
end loop;
value.trim(2);
goto continue_loop_3;
end loop;
end loop;
<<exit_loop_3>>
output := value(1);
return output;
EXCEPTION
WHEN OTHERS
THEN
return null;
end char_to_computing;
/**
* 查询表字段的默认值
* added by 80325612 2021/3/22
**/
FUNCTION f_get_column_default(p_table_name in VARCHAR,
p_column_name in VARCHAR2) RETURN VARCHAR AS
v_result VARCHAR2(32767);
v_sql VARCHAR2(2000);
begin
v_sql := 'SELECT T.DATA_DEFAULT FROM USER_TAB_COLUMNS T WHERE T.TABLE_NAME = '''||p_table_name||''' AND T.COLUMN_NAME='''||p_column_name||'''';
EXECUTE IMMEDIATE v_sql INTO v_result;
v_result := SUBSTR(v_result, 1, 4000);
RETURN v_result;
EXCEPTION
WHEN OTHERS
THEN
return null;
end f_get_column_default;
/**
* 删除表
* added by 80325612 2021/2/7
**/
PROCEDURE drop_table(p_oflow_id in varchar2,
p_oflow_name in varchar2,
p_err_code out number,
p_table_name in varchar2) is
v_task_name varchar2(200) := 'drop_table.'||p_table_name;
v_msg varchar2(4000);
v_count number;
v_sql varchar2(1000);
begin
sopmgr.pkg_iplan_log.write_task_log(gv_module_name, v_task_name, 'Start', v_msg, p_oflow_id, p_oflow_name);
select count(1)
into v_count
from user_tables t
where t.TABLE_NAME = upper(p_table_name);
if v_count > 0 then
v_sql := 'drop table '||gv_schema_name||'.'||p_table_name;
execute immediate v_sql;
end if;
v_msg := 'Success!';
p_err_code := 0;
sopmgr.pkg_iplan_log.write_task_log(gv_module_name, v_task_name, 'End', v_msg, p_oflow_id, p_oflow_name);
EXCEPTION
WHEN OTHERS
THEN
p_err_code := sqlcode;
v_msg := substr(dbms_utility.format_error_stack||dbms_utility.format_error_backtrace, 1, 4000);
sopmgr.pkg_iplan_log.write_task_log(gv_module_name, v_task_name, 'Failed', v_msg, p_oflow_id, p_oflow_name);
raise_application_error(-20001, v_msg);
end drop_table;
/**
* 删除索引
* added by 80325612 2021/2/7
**/
PROCEDURE drop_index(p_oflow_id in varchar2,
p_oflow_name in varchar2,
p_err_code out number,
p_index_name in varchar2) is
v_task_name varchar2(200) := 'drop_table.'||p_index_name;
v_msg varchar2(4000);
v_count number;
v_sql varchar2(1000);
begin
sopmgr.pkg_iplan_log.write_task_log(gv_module_name, v_task_name, 'Start', v_msg, p_oflow_id, p_oflow_name);
select count(1)
into v_count
from user_indexes t
where t.index_name = upper(p_index_name);
if v_count > 0 then
v_sql := 'drop index '||gv_schema_name||'.'||p_index_name;
execute immediate v_sql;
end if;
v_msg := 'Success!';
p_err_code := 0;
sopmgr.pkg_iplan_log.write_task_log(gv_module_name, v_task_name, 'End', v_msg, p_oflow_id, p_oflow_name);
EXCEPTION
WHEN OTHERS
THEN
p_err_code := sqlcode;
v_msg := substr(dbms_utility.format_error_stack||dbms_utility.format_error_backtrace, 1, 4000);
sopmgr.pkg_iplan_log.write_task_log(gv_module_name, v_task_name, 'Failed', v_msg, p_oflow_id, p_oflow_name);
raise_application_error(-20001, v_msg);
end drop_index;
/**
* 删除序列
* added by 80325612 2021/2/7
**/
PROCEDURE drop_sequence(p_oflow_id in varchar2,
p_oflow_name in varchar2,
p_err_code out number,
p_sequence_name in varchar2) is
v_task_name varchar2(200) := 'drop_sequence.'||p_sequence_name;
v_msg varchar2(4000);
v_count number;
v_sql varchar2(1000);
begin
sopmgr.pkg_iplan_log.write_task_log(gv_module_name, v_task_name, 'Start', v_msg, p_oflow_id, p_oflow_name);
select count(1)
into v_count
from user_sequences t
where t.sequence_name = upper(p_sequence_name);
if v_count > 0 then
v_sql := 'drop sequence '||gv_schema_name||'.'||p_sequence_name;
execute immediate v_sql;
end if;
v_msg := 'Success!';
p_err_code := 0;
sopmgr.pkg_iplan_log.write_task_log(gv_module_name, v_task_name, 'End', v_msg, p_oflow_id, p_oflow_name);
EXCEPTION
WHEN OTHERS
THEN
p_err_code := sqlcode;
v_msg := substr(dbms_utility.format_error_stack||dbms_utility.format_error_backtrace, 1, 4000);
sopmgr.pkg_iplan_log.write_task_log(gv_module_name, v_task_name, 'Failed', v_msg, p_oflow_id, p_oflow_name);
raise_application_error(-20001, v_msg);
end drop_sequence;
/**
* 失效索引
* added by 80325612 2021/2/7
**/
PROCEDURE p_unusable_index(p_oflow_id in varchar2,
p_oflow_name in varchar2,
p_err_code out number,
p_index_name in varchar2) is
v_task_name varchar2(200) := 'p_unusable_index.'||p_index_name;
v_msg varchar2(4000);
v_count number;
v_sql varchar2(1000);
begin
sopmgr.pkg_iplan_log.write_task_log(gv_module_name, v_task_name, 'Start', v_msg, p_oflow_id, p_oflow_name);
select count(1)
into v_count
from user_indexes t
where t.index_name = upper(p_index_name);
if v_count > 0 then
v_sql := 'ALTER INDEX '||p_index_name||' UNUSABLE';
execute immediate v_sql;
end if;
v_msg := 'Success!';
p_err_code := 0;
sopmgr.pkg_iplan_log.write_task_log(gv_module_name, v_task_name, 'End', v_msg, p_oflow_id, p_oflow_name);
EXCEPTION
WHEN OTHERS
THEN
p_err_code := sqlcode;
v_msg := substr(dbms_utility.format_error_stack||dbms_utility.format_error_backtrace, 1, 4000);
sopmgr.pkg_iplan_log.write_task_log(gv_module_name, v_task_name, 'Failed', v_msg, p_oflow_id, p_oflow_name);
raise_application_error(-20001, v_msg);
end p_unusable_index;
/**
* 失效表索引
* added by 80325612 2021/2/7
**/
PROCEDURE p_unusable_table_indexes(p_oflow_id in varchar2,
p_oflow_name in varchar2,
p_err_code out number,
p_table_name in varchar2) is
v_task_name varchar2(200) := 'p_unusable_table_indexes.'||p_table_name;
v_msg varchar2(4000);
v_count number;
v_sql varchar2(1000);
begin
sopmgr.pkg_iplan_log.write_task_log(gv_module_name, v_task_name, 'Start', v_msg, p_oflow_id, p_oflow_name);
for r in (select t.index_name
from user_indexes t
where t.table_name = upper(p_table_name)
and not exists (select 1
from user_constraints c
where c.table_name = upper(p_table_name)
and c.index_name = t.index_name
and c.constraint_type = 'P')) loop
sopmgr.pkg_iplan_util.p_unusable_index(p_oflow_id, p_oflow_name, p_err_code, r.index_name);
end loop;
v_msg := 'Success!';
p_err_code := 0;
sopmgr.pkg_iplan_log.write_task_log(gv_module_name, v_task_name, 'End', v_msg, p_oflow_id, p_oflow_name);
EXCEPTION
WHEN OTHERS
THEN
p_err_code := sqlcode;
v_msg := substr(dbms_utility.format_error_stack||dbms_utility.format_error_backtrace, 1, 4000);
sopmgr.pkg_iplan_log.write_task_log(gv_module_name, v_task_name, 'Failed', v_msg, p_oflow_id, p_oflow_name);
raise_application_error(-20001, v_msg);
end p_unusable_table_indexes;
/**
* 重建索引
* added by 80325612 2021/2/7
**/
PROCEDURE p_rebuild_index(p_oflow_id in varchar2,
p_oflow_name in varchar2,
p_err_code out number,
p_index_name in varchar2,
p_degree in number) is
v_task_name varchar2(200) := 'p_rebuild_index.'||p_index_name||'.'||p_degree;
v_msg varchar2(4000);
v_count number;
v_sql varchar2(1000);
begin
sopmgr.pkg_iplan_log.write_task_log(gv_module_name, v_task_name, 'Start', v_msg, p_oflow_id, p_oflow_name);
select count(1)
into v_count
from user_indexes t
where t.index_name = upper(p_index_name);
if v_count > 0 then
v_sql := 'ALTER INDEX '||p_index_name||' REBUILD';
if p_degree > 0 then
v_sql := v_sql||' PARALLEL(DEGREE '||p_degree||')';
end if;
execute immediate v_sql;
end if;
v_msg := 'Success!';
p_err_code := 0;
sopmgr.pkg_iplan_log.write_task_log(gv_module_name, v_task_name, 'End', v_msg, p_oflow_id, p_oflow_name);
EXCEPTION
WHEN OTHERS
THEN
p_err_code := sqlcode;
v_msg := substr(dbms_utility.format_error_stack||dbms_utility.format_error_backtrace, 1, 4000);
sopmgr.pkg_iplan_log.write_task_log(gv_module_name, v_task_name, 'Failed', v_msg, p_oflow_id, p_oflow_name);
raise_application_error(-20001, v_msg);
end p_rebuild_index;
/**
* 重建表索引
* added by 80325612 2021/2/7
**/
PROCEDURE p_rebuild_table_indexes(p_oflow_id in varchar2,
p_oflow_name in varchar2,
p_err_code out number,
p_table_name in varchar2,
p_degree in number) is
v_task_name varchar2(200) := 'p_rebuild_table_indexes.'||p_table_name||'.'||p_degree;
v_msg varchar2(4000);
v_count number;
v_sql varchar2(1000);
begin
sopmgr.pkg_iplan_log.write_task_log(gv_module_name, v_task_name, 'Start', v_msg, p_oflow_id, p_oflow_name);
for r in (select t.index_name
from user_indexes t
where t.table_name = upper(p_table_name)
and not exists (select 1
from user_constraints c
where c.table_name = upper(p_table_name)
and c.index_name = t.index_name
and c.constraint_type = 'P')) loop
sopmgr.pkg_iplan_util.p_rebuild_index(p_oflow_id, p_oflow_name, p_err_code, r.index_name, p_degree);
end loop;
v_msg := 'Success!';
p_err_code := 0;
sopmgr.pkg_iplan_log.write_task_log(gv_module_name, v_task_name, 'End', v_msg, p_oflow_id, p_oflow_name);
EXCEPTION
WHEN OTHERS
THEN
p_err_code := sqlcode;
v_msg := substr(dbms_utility.format_error_stack||dbms_utility.format_error_backtrace, 1, 4000);
sopmgr.pkg_iplan_log.write_task_log(gv_module_name, v_task_name, 'Failed', v_msg, p_oflow_id, p_oflow_name);
raise_application_error(-20001, v_msg);
end p_rebuild_table_indexes;
/**
* 重建序列
* added by 80325612 2021/2/7
**/
PROCEDURE rebuild_sequence(p_oflow_id in varchar2,
p_oflow_name in varchar2,
p_err_code out number,
p_sequence_name in varchar2,
p_start_with in number,
p_increment_by in number) is
v_task_name varchar2(200) := 'rebuild_sequence.'||p_sequence_name||'.'||p_start_with||'.'||p_increment_by;
v_msg varchar2(4000);
v_sql varchar2(1000);
v_curr_seq number;
v_increment number;
begin
sopmgr.pkg_iplan_log.write_task_log(gv_module_name,
v_task_name,
'Start',
v_msg,
p_oflow_id,
p_oflow_name);
--查询当前seq
v_sql := 'select ' || gv_schema_name || '.' || p_sequence_name ||
'.nextval from dual';
execute immediate v_sql
into v_curr_seq;
--修改值
v_increment := p_start_with - 1 - v_curr_seq;
v_sql := 'alter sequence ' || gv_schema_name || '.' || p_sequence_name ||
' increment by ' || v_increment;
execute immediate v_sql;
--获取一次 使其序列变更
v_sql := 'select ' || gv_schema_name || '.' || p_sequence_name ||
'.nextval from dual';
execute immediate v_sql
into v_curr_seq;
--修改回递增值
v_sql := 'alter sequence ' || gv_schema_name || '.' || p_sequence_name ||
' increment by ' || p_increment_by;
execute immediate v_sql;
v_msg := 'Success!';
p_err_code := 0;
sopmgr.pkg_iplan_log.write_task_log(gv_module_name,
v_task_name,
'End',
v_msg,
p_oflow_id,
p_oflow_name);
EXCEPTION
WHEN OTHERS THEN
p_err_code := sqlcode;
v_msg := substr(dbms_utility.format_error_stack ||
dbms_utility.format_error_backtrace,
1,
4000);
sopmgr.pkg_iplan_log.write_task_log(gv_module_name,
v_task_name,
'Failed',
v_msg,
p_oflow_id,
p_oflow_name);
raise_application_error(-20001, v_msg);
end rebuild_sequence;
/**
* 清空表数据
* added by 80325612 2021/4/29
**/
PROCEDURE p_truncate_table(p_oflow_id in varchar2,
p_oflow_name in varchar2,
p_err_code out number,
p_table_name in varchar2) is
v_task_name varchar2(200) := 'truncate_table.'||p_table_name;
v_msg varchar2(4000);
v_count number;
v_sql varchar2(1000);
begin
sopmgr.pkg_iplan_log.write_task_log(gv_module_name, v_task_name, 'Start', v_msg, p_oflow_id, p_oflow_name);
select count(1)
into v_count
from user_tables t
where t.TABLE_NAME = upper(p_table_name);
if v_count > 0 then
v_sql := 'truncate table '||gv_schema_name||'.'||p_table_name;
execute immediate v_sql;
end if;
v_msg := 'Success!';
p_err_code := 0;
sopmgr.pkg_iplan_log.write_task_log(gv_module_name, v_task_name, 'End', v_msg, p_oflow_id, p_oflow_name);
EXCEPTION
WHEN OTHERS
THEN
p_err_code := sqlcode;
v_msg := substr(dbms_utility.format_error_stack||dbms_utility.format_error_backtrace, 1, 4000);
sopmgr.pkg_iplan_log.write_task_log(gv_module_name, v_task_name, 'Failed', v_msg, p_oflow_id, p_oflow_name);
raise_application_error(-20001, v_msg);
end p_truncate_table;
/**
* 新增分区
* added by 80325612 2021/5/7
**/
PROCEDURE p_add_partition(p_oflow_id in varchar2,
p_oflow_name in varchar2,
p_err_code out number,
p_table_name in varchar2,
p_partition_name in varchar2,
p_partition_value in varchar2) is
v_task_name varchar2(200) := 'p_add_partition.'||p_table_name||'.'||p_partition_name||'.'||p_partition_value;
v_msg varchar2(4000);
v_count number;
v_sql varchar2(1000);
begin
sopmgr.pkg_iplan_log.write_task_log(gv_module_name, v_task_name, 'Start', v_msg, p_oflow_id, p_oflow_name);
select count(1)
into v_count
from user_tab_partitions t
where t.table_name = upper(p_table_name)
and t.partition_name = p_partition_name;
if v_count = 0 then
v_sql := 'alter table '||p_table_name||' add partition '||p_partition_name||' values ('''||p_partition_value||''')';
execute immediate v_sql;
elsif v_count > 0 then
sopmgr.pkg_iplan_util.p_truncate_partition(p_oflow_id, p_oflow_name, p_err_code, p_table_name, p_partition_name);
end if;
v_msg := 'Success!';
p_err_code := 0;
sopmgr.pkg_iplan_log.write_task_log(gv_module_name, v_task_name, 'End', v_msg, p_oflow_id, p_oflow_name);
EXCEPTION
WHEN OTHERS
THEN
p_err_code := sqlcode;
v_msg := substr(dbms_utility.format_error_stack||dbms_utility.format_error_backtrace, 1, 4000);
sopmgr.pkg_iplan_log.write_task_log(gv_module_name, v_task_name, 'Failed', v_msg, p_oflow_id, p_oflow_name);
raise_application_error(-20001, v_msg);
end p_add_partition;
/**
* 删除分区
* added by 80325612 2021/4/26
**/
PROCEDURE p_drop_partition(p_oflow_id in varchar2,
p_oflow_name in varchar2,
p_err_code out number,
p_table_name in varchar2,
p_partition_name in varchar2) is
v_task_name varchar2(200) := 'p_drop_partition.'||p_table_name||'.'||p_partition_name;
v_msg varchar2(4000);
v_count number;
v_sql varchar2(1000);
begin
sopmgr.pkg_iplan_log.write_task_log(gv_module_name, v_task_name, 'Start', v_msg, p_oflow_id, p_oflow_name);
select count(1)
into v_count
from user_tab_partitions t
where t.table_name = upper(p_table_name)
and t.partition_name = p_partition_name;
if v_count > 0 then
v_sql := 'alter table '||p_table_name||' drop partition '||p_partition_name||' update global indexes';
execute immediate v_sql;
end if;
v_msg := 'Success!';
p_err_code := 0;
sopmgr.pkg_iplan_log.write_task_log(gv_module_name, v_task_name, 'End', v_msg, p_oflow_id, p_oflow_name);
EXCEPTION
WHEN OTHERS
THEN
p_err_code := sqlcode;
v_msg := substr(dbms_utility.format_error_stack||dbms_utility.format_error_backtrace, 1, 4000);
sopmgr.pkg_iplan_log.write_task_log(gv_module_name, v_task_name, 'Failed', v_msg, p_oflow_id, p_oflow_name);
raise_application_error(-20001, v_msg);
end p_drop_partition;
/**
* 清空分区数据
* added by 80325612 2021/4/26
**/
PROCEDURE p_truncate_partition(p_oflow_id in varchar2,
p_oflow_name in varchar2,
p_err_code out number,
p_table_name in varchar2,
p_partition_name in varchar2) is
v_task_name varchar2(200) := 'p_truncate_partition.'||p_table_name||'.'||p_partition_name;
v_msg varchar2(4000);
v_count number;
v_sql varchar2(1000);
begin
sopmgr.pkg_iplan_log.write_task_log(gv_module_name, v_task_name, 'Start', v_msg, p_oflow_id, p_oflow_name);
select count(1)
into v_count
from user_tab_partitions t
where t.table_name = upper(p_table_name)
and t.partition_name = p_partition_name;
if v_count > 0 then
v_sql := 'alter table '||p_table_name||' truncate partition '||p_partition_name;
execute immediate v_sql;
end if;
v_msg := 'Success!';
p_err_code := 0;
sopmgr.pkg_iplan_log.write_task_log(gv_module_name, v_task_name, 'End', v_msg, p_oflow_id, p_oflow_name);
EXCEPTION
WHEN OTHERS
THEN
p_err_code := sqlcode;
v_msg := substr(dbms_utility.format_error_stack||dbms_utility.format_error_backtrace, 1, 4000);
sopmgr.pkg_iplan_log.write_task_log(gv_module_name, v_task_name, 'Failed', v_msg, p_oflow_id, p_oflow_name);
raise_application_error(-20001, v_msg);
end p_truncate_partition;
/**
* 备份历史
* added by 80325612 2021/4/26
**/
PROCEDURE p_backup_table(p_oflow_id in varchar2,
p_oflow_name in varchar2,
p_err_code out number,
p_table_name in varchar2,
p_his_table_name in varchar2,
p_backup_days in number default 180) is
v_task_name varchar2(200) := 'p_backup_table.'||p_table_name||'.'||p_his_table_name||'.'||p_backup_days;
v_sub_task_name varchar2(200);
v_msg varchar2(4000);
v_count number;
v_tab_cols varchar2(4000);
v_sql varchar2(4000);
begin
sopmgr.pkg_iplan_log.write_task_log(gv_module_name, v_task_name, 'Start', v_msg, p_oflow_id, p_oflow_name);
select count(1)
into v_count
from user_tables t
where t.TABLE_NAME in (upper(p_table_name), upper(p_his_table_name));
if v_count = 2 then
v_sub_task_name := '.delete '||p_his_table_name||'.'||p_backup_days;
sopmgr.pkg_iplan_log.write_task_log(gv_module_name, v_task_name||v_sub_task_name, 'Start', v_msg, p_oflow_id, p_oflow_name);
v_sql := 'delete from '||p_his_table_name||' t where t.backup_date < sysdate - '||p_backup_days;
execute immediate v_sql;
v_msg := 'rows='||SQL%ROWCOUNT;
commit;
sopmgr.pkg_iplan_log.write_task_log(gv_module_name, v_task_name||v_sub_task_name, 'End', v_msg, p_oflow_id, p_oflow_name);
v_sub_task_name := '.insert '||p_his_table_name;
sopmgr.pkg_iplan_log.write_task_log(gv_module_name, v_task_name||v_sub_task_name, 'Start', v_msg, p_oflow_id, p_oflow_name);
select listagg(t.column_name, ',') within group (order by column_name)
into v_tab_cols
from user_tab_columns t
where t.table_name = upper(p_table_name);
v_sql := 'insert into '||p_his_table_name||'('||v_tab_cols||',backup_date) select '||v_tab_cols||',sysdate from '||p_table_name;
execute immediate v_sql;
v_msg := 'rows='||SQL%ROWCOUNT;
commit;
sopmgr.pkg_iplan_log.write_task_log(gv_module_name, v_task_name||v_sub_task_name, 'End', v_msg, p_oflow_id, p_oflow_name);
end if;
v_msg := 'Success';
p_err_code := 0;
sopmgr.pkg_iplan_log.write_task_log(gv_module_name, v_task_name, 'End', v_msg, p_oflow_id, p_oflow_name);
EXCEPTION
WHEN OTHERS
THEN
p_err_code := sqlcode;
v_msg := substr(dbms_utility.format_error_stack||dbms_utility.format_error_backtrace, 1, 4000);
sopmgr.pkg_iplan_log.write_task_log(gv_module_name, v_task_name, 'Failed', v_msg, p_oflow_id, p_oflow_name);
raise_application_error(-20001, v_msg);
end p_backup_table;
begin
-- Initialization
null;
end PKG_IPLAN_UTIL;
投递谷歌等公司7个岗位 >
阿里巴巴公司福利 609人发布
