存储过程建立表分区

create or replace function fsc_add_table_partitions

(

    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 varchar2,

    v_tablespace        in varchar2

) return  integer

/** HEAD

  * @name masadm#fsc_add_table_partitions

  * @caption 分区处理函数

  * @type 自动分区

  * @parameter v_table_owner      表的拥有者：masadw、masamk、masakr等

  * @parameter v_table_name       表名

  * @parameter v_partition_name   分区名：带分区值的分区全名，例如P20071211

  * @parameter v_subpartition_name子分区名：同3

  * @parameter v_object_type      对象类型：仅限于三个值：table（表）、partition（分区表）、subpartition（带子分区的分区表）

  * @parameter v_value_type       分区值的类型：仅限于三个值：integer（整型）、char（字符型）、date（日期型）

  * @parameter v_values           分区值：整型的月份或日期（即使是日期型的分区值，也输入整型参数）

  * @parameter v_tablespace       表空间名称

  * @description 清空表或表分区，分区不存在的增加分区（适用于所有表、分区表）

  * @version 1.0

  */

 as

    /**

      * @description 变量定义

      * @variable-define vi_task_id integer 任务日志ID

      * @variable-define vv_task_name varchar2  任务名称

      * @variable-define vv_table_name varchar2  表名称

      * @variable-define vv_task_pos varchar2  任务位置

      * @variable-define vi_result integer  临时结果

      * @variable-define vi_flag integer  判断标识

      * @variable-define vi_err_code integer  错误信息代码

      * @variable-define vv_err_msg varchar2  错误信息详情

      * @variable-define exc_return exception 程序中间返回自定义异常

      * @variable-define exc_error exception 程序出错返回自定义异常

      * @variable-define vi_date integer  整数类型的统计日期

      * @variable-define vi_last_date integer  整数类型的统计日期

      */

      vv_task_name          varchar2(30) := 'fsc_add_table_partitions';

      vv_task_pos           varchar2(100);

      vi_task_id            integer;            -- 任务日志ID

      vi_result             integer;

      vi_err_code           integer;            -- 错误信息代码

      vv_err_msg            varchar2(200);      -- 错误信息详情

      exc_return            exception;          -- 程序中间返回自定义异常

      exc_error             exception;          -- 程序出错返回自定义异常

      vi_flag               integer:=0;

      vv_sql                varchar2(1000);

      vv_table_owner        varchar2(50) := upper(trim(v_table_owner));

      vv_table_name         varchar2(50) := upper(trim(v_table_name));

      vv_partition_name     varchar2(50) := upper(trim(v_partition_name));

      vv_subpartition_name  varchar2(50) := upper(trim(v_subpartition_name));

      vv_object_type        varchar2(50) := upper(trim(v_object_type));

      vv_value_type         varchar2(50) := upper(trim(v_value_type));

      vi_values             varchar2(50) := v_values;

      vv_tablespace         varchar2(50) := upper(trim(v_tablespace));

      vv_last_partition     varchar2(50);

 begin

      vv_task_pos := '参数有效性检验';

      psc_sys_log(vi_task_id, 1, null, vv_task_name, vv_table_name, vi_values, vv_err_msg, vv_task_pos, vi_result);

      --检查用户名

      if vv_table_owner is null then

         vv_task_pos := '用户名不能为空';

         raise exc_error;

      end if;

      --检查表名

      if vv_table_name is null then

         vv_task_pos := '表名不能为空';

         raise exc_error;

      end if;

      --检查对象类型

      if vv_object_type is null then

         vv_task_pos := '对象类型不能为空';

         raise exc_error;

      end if;

      --参数检查通过，进入函数主体

      vv_task_pos := '进入函数主体';

      --对象类型是 TABLE 的表处理

      if vv_object_type = 'TABLE' then

         vv_task_pos := '该对象类型是 TABLE';

         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||' 不存在';

            raise exc_error;

         else

            --如果表存在，则清空此表

            vv_task_pos := '清空表 '||vv_table_owner||'.'||vv_table_name;

            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';

         --参数有效性检查

         if vv_partition_name is null or

            vv_value_type is null or

            vi_values is null then

            vv_task_pos := '分区表参数不能为空：缺少分区名、分区值类型或分区值';

            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||' 不存在，建立此分区';

            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||') '||

                      case when vv_tablespace is not null then ''||'tablespace '||vv_tablespace else '' end;

            execute immediate vv_sql;

         else

            --如果分区存在，则情况此分区

            vv_task_pos := '分区 '||vv_partition_name||' 存在，清空此分区';

            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';

         --参数有效性检验

         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 := '分区表参数不能为空：缺少分区名、子分区名、子分区值类型或子分区值';

            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||' 及其子分区不存在，建立此分区';

            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||') '||

                      case when vv_tablespace is not null then ''||'tablespace '||vv_tablespace  else '' end||')';

            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||' 不存在，增加该子分区';

               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||') '||

                         case when vv_tablespace is not null then ''||'tablespace '||vv_tablespace else '' end;

               execute immediate vv_sql;

            else

               --子分区存在的处理

               vv_task_pos := '子分区 '||vv_subpartition_name||' 存在，清空此分区';

               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 := '完成！';

      psc_sys_log(vi_task_id, 0, null, null, null, null, null, null, vi_result);

      return(vi_result);

exception

    when exc_return then

        /** @description 程序中间返回，记录程序结束日志，正常返回

          * @call ng2_app#psc_sys_log

          * @field-mapping oi_return = (0)

          */

        psc_sys_log(vi_task_id, 0, null, null, null, null, vv_err_msg, vv_task_pos, vi_result);

        return(vi_result);

    when exc_error then

        /** @description 程序出错返回自定义异常，记录程序出错日志，出错返回

          * @field-mapping vv_err_msg = 取前200个字符(vv_err_msg)

          * @call ng2_app#psc_sys_log

          * @field-mapping oi_return = (vi_err_code)

          */

         vv_err_msg := substr(vv_err_msg, 1, 200);

         rollback;

         psc_sys_log(vi_task_id, vi_err_code, null, null, null, null, vv_err_msg, vv_task_pos, vi_result);

         return(vi_result);

    when others then

        /** @description 程序出错，得到出错信息，回滚事务，记录程序出错日志，出错返回

          * @field-mapping vi_err_code = (sqlcode)

          * @field-mapping vv_err_msg = 取前200个字符(sqlerrm)

          * @call ng2_app#psc_sys_log

          * @field-mapping oi_return = (vi_err_code)

          */

        vi_err_code := sqlcode;

        vv_err_msg := substr(sqlerrm, 1, 200);

        rollback;

        psc_sys_log(vi_task_id, vi_err_code, null, null, null, null, vv_err_msg, vv_task_pos, vi_result);

        return(vi_result);

/** END */

end;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25903968/viewspace-1674952/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25903968/viewspace-1674952/