分区整理语句 3

--删除分区

create or replace procedure clean_tab_partitions(iv_username in varchar2,oi_return out integer)

/** head

  * @name clean_tab_partitions

  * @caption  clean the partition of input users

  * @type     primarily management program

  * @parameter on_statuscode out number execute state: 0 means ok ; others means error.

  * @description  clean the partition of input users

  * @source all_tab_partitions and TB_SYS_TAB_PARTITION

  * @middle

  * @version 1.1.000

  * @author Wen Chenteng

  * @create-date

  * @todo

  * @author Wen Chenteng

  * @create-date 2012-03-26

  * @description

  * @copyright Huawei

  */

 is

    vv_sql          varchar2(3000); --the sql which is used to drop the partitions

    vi_users_num    integer;        --the user number which is inputed

    vv_capi_usernam varchar2(100);  --the capitalized user name

    vi_users_flag   integer;        --this the sign for check whether the input users is exist in the system

    vv_par_flag     integer;        --the sign of sp_dw_tab_add_partition procedure, 0 mean success; others mean fail

    vv_user1        varchar2(50);   --the first user's name

    vv_user2        varchar2(50);   --the second user's name

    vv_user3        varchar2(50);   --the third user's name

    vv_user4        varchar2(50);   --the fourth user's name

    exc_error1      exception;      -- customized error exception variable in program processing

    exc_error2      exception;      -- customized error exception variable in program processing

    exc_error3      exception;      -- customized error exception variable in program processing