用于表空间和表分区的方法

最新推荐文章于 2023-08-27 17:40:37 发布
fhuan123
最新推荐文章于 2023-08-27 17:40:37 发布
阅读量126
点赞数
分类专栏：数据库-oracle 文章标签： SQL
本文链接：https://blog.csdn.net/fhuan123/article/details/83667061
版权
数据库-oracle 专栏收录该内容
41 篇文章 0 订阅
订阅专栏
CREATE OR REPLACE FUNCTION MASAMKt.fun_add_table_partitionst(
v_table_owner in varchar2,
v_table_name  in varchar2,
v_partition_name in varchar2,
v_subpartition_name in varchar2,
v_object_type in varchar2,
v_value_type  in varchar2,
v_values in integer,
v_tablespace in varchar2
)
 ----/************************************************************
 ----过 程 名： fun_add_table_partitions
 ----过程描述： 清空表或表分区，分区不存在的增加分区（适用于所有表、分区表）
 ----输入参数：	1、表的拥有者：masadw、masamk、masakr等
 ----           2、表名
 ----           3、分区名：带分区值的分区全名，例如sc_user_day_20071211
 ----           4、子分区名：同3
 ----           5、对象类型：仅限于三个值：table（表）、partition（分区表）、subpartition（带子分区的分区表）
 ----           6、分区值的类型：仅限于三个值：integer（整型）、char（字符型）、date（日期型）
 ----           7、分区值：整型的月份或日期（即使是日期型的分区值，也输入整型参数）
 ----           8、表空间名称（不可以为空）
 ----输出参数：	执行状态(0、执功,-1、执行失败)
 ----调用的过程或函数：
 ----被哪些过程或函数调用：
 ----依赖的过程或函数：
 ----编写人员：	  ----**************************************************************
 RETURN  integer

 AS
      vv_task_name          varchar2(30):='fun_add_table_partitions';
      vv_task_pos           varchar2(100);
      
      vv_table_owner        varchar2(30):=upper(trim(v_table_owner));
      vv_table_name         varchar2(30):=upper(trim(v_table_name));
      vv_partition_name     varchar2(30):=upper(trim(v_partition_name));
      vv_subpartition_name  varchar2(30):=upper(trim(v_subpartition_name));
      vv_object_type        varchar2(30):=upper(trim(v_object_type));
      vv_value_type         varchar2(30):=upper(trim(v_value_type));
      vi_values             integer:=v_values;
      vv_tablespace         varchar2(30):=upper(trim(v_tablespace));
      vv_last_partition     varchar2(30);
      
      vi_result             integer;
      exc_error             exception;          -- 程序出错返回自定义异常
      
      vi_flag               integer:=0;
      vv_sql                varchar2(1000);
 BEGIN
      vv_task_pos := '参数有效性检验';
      vi_result := masamk.sf_write_proc_log(vv_task_name, vv_table_name, sysdate, 1, vv_task_pos, null);
      
      --检查用户名
      if vv_table_owner is null then
         vv_task_pos := '用户名不能为空';
         vi_result := masamk.sf_write_proc_log(vv_task_name, vv_table_name, sysdate, 0, vv_task_pos, null);
         raise exc_error;
      end if;
      --检查表名
      if vv_table_name is null then
         vv_task_pos := '表名不能为空';
         vi_result := masamk.sf_write_proc_log(vv_task_name, vv_table_name, sysdate, 0, vv_task_pos, null);
         raise exc_error;
      end if;
      --检查对象类型
      if vv_object_type is null then
         vv_task_pos := '对象类型不能为空';
         vi_result := masamk.sf_write_proc_log(vv_task_name, vv_table_name, sysdate, 0, vv_task_pos, null);
         raise exc_error;
      end if;
      --检查表空间
      if vv_tablespace is null then
         vv_task_pos := '表空间不能为空';
         vi_result := masamk.sf_write_proc_log(vv_task_name, vv_table_name, sysdate, 0, vv_task_pos, null);
         raise exc_error;
      end if;
      
      --参数检查通过，进入函数主体
      vv_task_pos := '进入函数主体';
      vi_result := masamk.sf_write_proc_log(vv_task_name, vv_table_name, sysdate, 2, vv_task_pos, null);
      
      --对象类型是 TABLE 的表处理
      if vv_object_type = 'TABLE' then
         vv_task_pos := '该对象类型是 TABLE';
         vi_result := masamk.sf_write_proc_log(vv_task_name, vv_table_name, sysdate, 3, vv_task_pos, null);
         
         vi_flag := 0;
         select count('a') into vi_flag
           from all_tables t
          where t.owner=vv_table_owner
            and t.table_name=vv_table_name;
         
         --如果表不存在，退出
         if vi_flag = 0 then
            vv_task_pos := '表 '||vv_table_owner||'.'||vv_table_name||' 不存在';
            vi_result := masamk.sf_write_proc_log(vv_task_name, vv_table_name, sysdate, 4, vv_task_pos, null);
            vi_result := 9;
            raise exc_error;
         else
            --如果表存在，则清空此表
            vv_task_pos := '清空表 '||vv_table_owner||'.'||vv_table_name;
            vi_result := masamk.sf_write_proc_log(vv_task_name, vv_table_name, sysdate, 4, vv_task_pos, null);
            vv_sql := 'truncate table '||vv_table_owner||'.'||vv_table_name;
            execute immediate vv_sql;
         end if;
      end if;
      
      --对象类型是 PARTITION 的表处理
      if vv_object_type = 'PARTITION' then
         vv_task_pos := '该对象类型是 PARTITION';
         vi_result := masamk.sf_write_proc_log(vv_task_name, vv_table_name, sysdate, 3, vv_task_pos, null);
         
         --参数有效性检查
         if vv_partition_name is null or 
            vv_value_type is null or
            vi_values is null then
            
            vv_task_pos := '分区表参数不能为空：缺少分区名、分区值类型或分区值';
            vi_result := masamk.sf_write_proc_log(vv_task_name, vv_table_name, sysdate, 0, vv_task_pos, null);
            raise exc_error;
         end if;
         
         vi_flag := 0;
         select count('a') into vi_flag
           from all_tab_partitions t
          where t.table_owner=vv_table_owner
            and t.table_name=vv_table_name
            and t.partition_name=vv_partition_name;
         --如果分区不存在，则建立该分区
         if vi_flag = 0 then
            vv_task_pos := '分区 '||vv_partition_name||' 不存在，建立此分区';
            vi_result := masamk.sf_write_proc_log(vv_task_name, vv_table_name, sysdate, 4, vv_task_pos, null);
            
            vv_sql := 'alter table '||vv_table_owner||'.'||vv_table_name||' add partition '
            ||vv_partition_name||' values('||case when vv_value_type='INTEGER' then ''
            ||vi_values||'' when vv_value_type='CHAR' then ''''||vi_values||'''' 
            when vv_value_type='DATE' then 'to_date('||vi_values||',''yyyymmdd'')' end||') 
            tablespace '||vv_tablespace;
            execute immediate vv_sql;
         else
            --如果分区存在，则情况此分区
            vv_task_pos := '分区 '||vv_partition_name||' 存在，清空此分区';
            vi_result := masamk.sf_write_proc_log(vv_task_name, vv_table_name, sysdate, 4, vv_task_pos, null);
            
            vv_sql := 'alter table '||vv_table_owner||'.'||vv_table_name||' truncate partition '
            ||vv_partition_name;
            execute immediate vv_sql;
         end if;
      end if;
      
      --对象类型是 SUBPARTITION 的表处理
      if vv_object_type = 'SUBPARTITION' then
         vv_task_pos := '该对象类型是 SUBPARTITION';
         vi_result := masamk.sf_write_proc_log(vv_task_name, vv_table_name, sysdate, 3, vv_task_pos, null);
         
         --参数有效性检验
         if vv_partition_name is null or 
            vv_subpartition_name is null or
            vv_value_type is null or
            vi_values is null then
            vv_task_pos := '分区表参数不能为空：缺少分区名、子分区名、子分区值类型或子分区值';
            vi_result := masamk.sf_write_proc_log(vv_task_name, vv_table_name, sysdate, 0, vv_task_pos, null);
            raise exc_error;
         end if;
         
         --检查分区是否存在
         vi_flag := 0;
         select count('a') into vi_flag
           from all_tab_partitions t
          where t.table_owner=vv_table_owner
            and t.table_name=vv_table_name
            and t.partition_name=vv_partition_name;
         
         --分区不存在的处理
         if vi_flag = 0 then
            vv_task_pos := '分区 '||vv_partition_name||' 及其子分区不存在，建立此分区';
            vi_result := masamk.sf_write_proc_log(vv_task_name, vv_table_name, sysdate, 4, vv_task_pos, null);
            
            vv_last_partition := to_char(add_months(to_date(vi_values,'yyyymmdd'),1),'yyyymm');
            
            vv_sql := 'alter table '||vv_table_owner||'.'
            ||vv_table_name||' add partition '||vv_partition_name
            ||' values less than('||vv_last_partition||')
            (subpartition '||vv_subpartition_name||' values('
            ||case when vv_value_type='INTEGER' then ''||vi_values||'' 
            when vv_value_type='CHAR' then ''''||vi_values||'''' 
            when vv_value_type='DATE' then 'to_date('||vi_values||',''yyyymmdd'')' end||') tablespace '
            ||vv_tablespace||')';
            
           
            execute immediate vv_sql;
         else
            --分区存在，检查子分区是否存在
            vi_flag := 0;
            select count('a') into vi_flag
              from all_tab_subpartitions t
             where t.table_owner=vv_table_owner
               and t.table_name=vv_table_name
               and t.partition_name=vv_partition_name
               and t.subpartition_name=vv_subpartition_name;
            
            if vi_flag = 0 then
               --子分区不存在的处理
               vv_task_pos := '子分区 '||vv_subpartition_name||' 不存在，增加该子分区';
               vi_result := masamk.sf_write_proc_log(vv_task_name, vv_table_name, sysdate, 4, vv_task_pos, null);
               
               vv_sql := 'alter table '||vv_table_owner||'.'||vv_table_name||' modify partition '
               ||vv_partition_name||' add subpartition '||vv_subpartition_name||' values('
               ||case when vv_value_type='INTEGER' then ''||vi_values||'' 
               when vv_value_type='CHAR' then ''''||vi_values||'''' 
               when vv_value_type='DATE' then 'to_date('||vi_values||',''yyyymmdd'')' end||') tablespace '
               ||vv_tablespace;
               execute immediate vv_sql;
            else
               --子分区存在的处理
               vv_task_pos := '子分区 '||vv_subpartition_name||' 存在，清空此分区';
               vi_result := masamk.sf_write_proc_log(vv_task_name, vv_table_name, sysdate, 4, vv_task_pos, null);
               
               vv_sql := 'alter table '||vv_table_owner||'.'||vv_table_name||' truncate subpartition '
               ||vv_subpartition_name;
               execute immediate vv_sql;
            end if;
         end if;
      
      end if;
      
      vv_task_pos := '完成！';
      vi_result := masamk.sf_write_proc_log(vv_task_name, vv_table_name, sysdate, 0, vv_task_pos, null);
      
      return(vi_result);
      
/*EXCEPTION
  WHEN exc_error then 
    vi_result:=0;
	WHEN OTHERS THEN
	vi_result := masamk.sf_write_proc_log(vv_task_name, vv_table_name, sysdate, 0, sqlcode, sqlerrm);
  return(vi_result);*/

 END fun_add_table_partitions;