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;  回复  更多评论