FUNCTION func_cre_load_partition(v_table_name varchar2,
v_acct_month varchar2,
v_partitionName varchar2,
v_lan_id number) RETURN NUMBER IS
/***************************************************************
函數名：(func_cre_partition)
功能描述：建立分區策略
輸入參數說明：v_table_name 需要建分區的表名
v_acct_month 建分區的月份
v_partitionName 分區名稱
v_lan_id 建分區的本地網
返回參數說明： 1 成功 -1 失敗
創建人員：lizhenpeng
創建日期：2009-4-14
***************************************************************/
exists_flag int;
v_sql varchar2(2000);
i_status int := 0;
V_LOGID NUMBER(12);
v_err VARCHAR2(500);
begin
--判斷分區是否存在
select count(*)
into exists_flag
from USER_TAB_PARTITIONS
where table_name = UPPER(v_table_name)
and partition_name = UPPER(v_partitionName || '_' || v_acct_month || '_' ||
to_char(v_lan_id));
--不存在創建對應分區
if exists_flag = 0 then
loop
v_sql := 'LOCK TABLE OTH_PARTITION_CTL IN EXCLUSIVE MODE';
execute immediate v_sql;
--判斷是否鎖定 0未開始 2 進行 1 完成
begin
select status
into i_status
from oth_partition_ctl
where fwf_no = v_acct_month
and table_name = UPPER(v_table_name);

exception
when others then
insert into oth_partition_ctl
values
(v_acct_month, UPPER(v_table_name), 2);
commit;
i_status := 0;
end;
commit;
--創建分區考慮是否重復創建邏輯
if i_status = 0 then
update oth_partition_ctl
set status = 2
where fwf_no = v_acct_month
and table_name = UPPER(v_table_name);
commit;
for v1 in (select standard_code
from oth_code_relation
where system_id = 2
and code_type = 'LAN_ID'
and standard_code like '7%'
ORDER BY STANDARD_CODE) loop
v_sql := 'alter table ' || v_table_name || ' add PARTITION ' ||
v_partitionName || '_' || v_acct_month || '_' ||
v1.standard_code || ' values less than (' ||
v_acct_month || ',' ||
to_char(to_number(v1.standard_code) + 1) ||
') NOLOGGING';
execute immediate v_sql;
end loop;
--修改完成標志
update oth_partition_ctl
set status = 1
where fwf_no = v_acct_month
and table_name = UPPER(v_table_name);
commit;

elsif (i_status = 2) then
--別的進程正在建立分區，等待完成
dbms_lock.sleep(30);
elsif (i_status = 1) then
--已經等待別的進程分區創建完成
goto lab_exit;
end if;
end loop;
<<lab_exit>>
null;
--存在TRUNCATE對應分區
elsif (exists_flag > 0) then
dbms_lock.sleep(to_number(v_lan_id) - 700);
v_sql := 'alter table ' || v_table_name || ' truncate PARTITION ' ||
v_partitionName || '_' || v_acct_month || '_' ||
to_char(v_lan_id);
execute immediate v_sql;
end if;
return 1;
exception
when others then
update oth_partition_ctl
set status = 0
where fwf_no = v_acct_month
and table_name = UPPER(v_table_name);
commit;
v_err := substr(sqlerrm, 1, 254);
select seq_job_id.nextval into V_LOGID from dual;
INSERT INTO oth_fat_detail_log
(LOG_ID,
LAN_CODE,
ACCT_MONTH,
PROC_NAME,
ERR_CODE,
ERR_NAME,
ERR_MSG,
start_time)
VALUES
(V_LOGID,
v_lan_id,
v_acct_month,
V_TABLE_NAME,
v_partitionName || '_' || v_acct_month || '_' || v_lan_id,
v_partitionName || '_' || v_acct_month || '_' || v_lan_id,
v_err,
sysdate);
return - 1;
end func_cre_load_partition;  回復  更多評論