• 企业400电话
  • 微网小程序
  • AI电话机器人
  • 电商代运营
  • 全 部 栏 目

    企业400电话 网络优化推广 AI电话机器人 呼叫中心 网站建设 商标✡知产 微网小程序 电商运营 彩铃•短信 增值拓展业务
    oracle存储过程创建表分区实例

    用存储过程创建数据表:
    创建时注意必须添加authid current_user,如果创建的表已存在,存储过程继续执行,但如不不加此关键语句,存储过程将出现异常,
    这个语句相当于赋权限。
    例1
    创建语句如下:

    复制代码 代码如下:

    create or replace
    procedure sp_create_mnl(i_id varchar2) authid current_user  as
      /*********************************
    名称:sp_create_mnl
    功能描述:创建模拟量历史数据存储表

    修订记录:
    版本号   编辑时间  编辑人  修改描述
    1.0.0    2012-9-20 wylaok  1.创建此存储过程
    1.0.1    2012-9-21 wylaok  2.修改表名称及变量名称,增加必要注释

    入参出参描述:
    i_id 测点编号
    **********************************/
          v_tablename varchar2(30);--表名
          v_flag number(10,0);
          v_sqlfalg varchar(200);
    begin
      v_flag:=0;
          v_tablename:=CONCAT('MNL', UPPER(i_id));
          v_sqlfalg:='select count(*) from user_TABLES where table_name='''||v_tablename||'''';
          dbms_output.put_line(v_sqlfalg);
          execute immediate v_sqlfalg into v_flag;
          if v_flag=0 then  --如果没有这个表 则去创建
             begin
      execute immediate 'create table '||v_tablename ||'
      ( DATETIME DATE,
      MIN00    FLOAT,
      AGV00    FLOAT,
      MAX00    FLOAT,
      MIN05    FLOAT,
      AVG05    FLOAT,
      MAX05    FLOAT,
      MIN10    FLOAT,
      AGV10    FLOAT,
      MAX10    FLOAT,
      MIN15    FLOAT,
      AGV15    FLOAT,
      MAX15    FLOAT,
      MIN20    FLOAT,
      AGV20    FLOAT,
      MAX20    FLOAT,
      MIN25    FLOAT,
      AGV25    FLOAT,
      MAX25    FLOAT,
      MIN30    FLOAT,
      AGV30    FLOAT,
      MAX30    FLOAT,
      MIN35    FLOAT,
      AGV35    FLOAT,
      MAX35    FLOAT,
      MIN40    FLOAT,
      AGV40    FLOAT,
      MAX40    FLOAT,
      MIN45    FLOAT,
      AGV45    FLOAT,
      MAX45    FLOAT,
      MIN50    FLOAT,
      AGV50    FLOAT,
      MAX50    FLOAT,
      MIN55    FLOAT,
      AGV55    FLOAT,
      MAX55    FLOAT,
      MINV     FLOAT,
      MAXV     FLOAT,
      AVGV     FLOAT,
      MAXTIME  DATE,
      MINTIME  DATE
      )
      tablespace WYG
      pctfree 10
      initrans 1
      maxtrans 255
      storage
      (
        initial 512K
        next 512K
        minextents 1
        maxextents unlimited
        pctincrease 0
      )';
      --    execute immediate sqlstr;
             end;
          end if;
          end;

    调用此存储过程:
    复制代码 代码如下:

    begin
      createmnl('mnl_14');
      end;

    例2
    复制代码 代码如下:

    CREATE OR REPLACE PROCEDURE BIP_MMS_PARTITION_PROC AS

      v_Mms_Task_Tab    VARCHAR2(50); --表名
      v_Mms_Content_Tab VARCHAR2(50);
      v_Mms_User_Tab    VARCHAR2(50);
      v_TableSpace      VARCHAR2(20); --表空间
      v_PartPreFlag     VARCHAR2(50); --分区名标识
      v_SqlCursor       NUMBER; --游标
      v_SqlExec         VARCHAR2(2000); --执行语句
      v_PartPreDate     VARCHAR2(20); --分区日期
      v_RangeValue      NUMBER;
      v_RangeDate       NUMBER;
      v_Rows            NUMBER(30) := 0;
      v_Num             NUMBER(30) := 0;
      vErrInfo          VARCHAR2(200);
      p_DateFrom        NUMBER;
      p_PartNum         NUMBER;
      p_Range           NUMBER;
    BEGIN

      v_Mms_Task_Tab    := 'BIP_MMS_MT_TASK_LOG_TAB_TEST';
      v_Mms_Content_Tab := 'BIP_MMS_MT_CONTENT_TAB_TEST';
      v_Mms_User_Tab    := 'BIP_MMS_MT_USER_LOG_TAB_TEST';
      -- 读取配置参数
      BEGIN
        SELECT TO_NUMBER(VALUE)
          INTO p_DateFrom
          FROM BIP_OTHERS_PROPERTIES_TAB
         WHERE NAME = 'p_DateFrom';
        SELECT TO_NUMBER(VALUE)
          INTO p_PartNum
          FROM BIP_OTHERS_PROPERTIES_TAB
         WHERE NAME = 'p_PartNum';
        SELECT TO_NUMBER(VALUE)
          INTO p_Range
          FROM BIP_OTHERS_PROPERTIES_TAB
         WHERE NAME = 'p_Range';
      EXCEPTION
        WHEN OTHERS THEN
          BEGIN
            p_DateFrom := 0;
            p_PartNum  := 1;
            p_Range    := 180;
          END;
      END;
      --记录存储过程添加分区
      INSERT INTO BIP_LOG_STAT_EXEC_TAB
      VALUES
        (TO_CHAR(SYSDATE, 'yyyymmddhh24miss'),
         'BIP_MMS_PARTITION_PROC_ADD',
         'BEGIN');
      COMMIT;
      --ADD PARTITION 
      FOR i IN 1 .. p_PartNum LOOP
        --BIP_MMS_MT_CONTENT_TAB 添加分区
        v_PartPreDate := TO_CHAR(SYSDATE + p_DateFrom + i, 'YYYYMMDD');
        dbms_output.put_line(v_PartPreDate);
        v_Num         := 0;
        v_TableSpace  := 'BIP_MMS_TS_TEST';
        v_PartPreFlag := 'MMS_MT_CONTENT';
        SELECT COUNT(*)
          INTO v_Num
          FROM user_tab_partitions
         WHERE table_name = v_Mms_Content_Tab
           AND SUBSTR(partition_name, 16, 8) = v_PartPreDate;
        IF v_Num 1 THEN
          v_RangeDate  := TO_CHAR(SYSDATE + p_DateFrom + i, 'YYYYMMDD');
          v_RangeValue := v_RangeDate || '240000';
          dbms_output.put_line(v_RangeValue);
          v_SqlExec := 'ALTER TABLE ' || v_Mms_Content_Tab || ' ADD PARTITION ' ||
                       v_PartPreFlag || '_' || v_PartPreDate ||
                       ' VALUES LESS THAN(''' || v_RangeValue ||
                       ''') TABLESPACE ' || v_TableSpace;
          dbms_output.put_line(v_SqlExec);
          v_SqlCursor := DBMS_SQL.OPEN_CURSOR;
          DBMS_SQL.PARSE(v_SqlCursor, v_SqlExec, DBMS_SQL.NATIVE);
          v_Rows := DBMS_SQL.EXECUTE(v_SqlCursor);
          DBMS_SQL.CLOSE_CURSOR(v_SqlCursor);
        END IF;
        --BIP_MMS_MT_TASK_LOG_TAB_TEST 添加分区
        v_PartPreDate := TO_CHAR(SYSDATE + p_DateFrom + i, 'YYYYMMDD');
        v_Num         := 0;
        v_TableSpace  := 'BIP_MMS_TS_TEST';
        v_PartPreFlag := 'MMS_MT_TASK_LOG';
        SELECT COUNT(*)
          INTO v_Num
          FROM user_tab_partitions
         WHERE table_name = v_Mms_Task_Tab
           AND SUBSTR(partition_name, 17, 8) = v_PartPreDate;
        IF v_Num 1 THEN
          v_RangeDate  := TO_CHAR(SYSDATE + p_DateFrom + i, 'YYYYMMDD');
          v_RangeValue := v_RangeDate || '240000';
          v_SqlExec    := 'ALTER TABLE ' || v_Mms_Task_Tab || ' ADD PARTITION ' ||
                          v_PartPreFlag || '_' || v_PartPreDate ||
                          ' VALUES LESS THAN(''' || v_RangeValue ||
                          ''') TABLESPACE ' || v_TableSpace;
          dbms_output.put_line(v_SqlExec);
          v_SqlCursor := DBMS_SQL.OPEN_CURSOR;
          DBMS_SQL.PARSE(v_SqlCursor, v_SqlExec, DBMS_SQL.NATIVE);
          v_Rows := DBMS_SQL.EXECUTE(v_SqlCursor);
          DBMS_SQL.CLOSE_CURSOR(v_SqlCursor);
        END IF;
        --BIP_MMS_MT_USER_LOG_TAB_TEST 添加分区
        v_PartPreDate := TO_CHAR(SYSDATE + p_DateFrom + i, 'YYYYMMDD');
        v_Num         := 0;
        v_TableSpace  := 'BIP_MMS_TS_TEST';
        v_PartPreFlag := 'MMS_MT_USER_LOG';
        SELECT COUNT(*)
          INTO v_Num
          FROM user_tab_partitions
         WHERE table_name = v_Mms_User_Tab
           AND SUBSTR(partition_name, 17, 8) = v_PartPreDate;
        IF v_Num 1 THEN
          v_RangeDate  := TO_CHAR(SYSDATE + p_DateFrom + i, 'YYYYMMDD');
          v_RangeValue := v_RangeDate || '240000';
          v_SqlExec    := 'ALTER TABLE ' || v_Mms_User_Tab || ' ADD PARTITION ' ||
                          v_PartPreFlag || '_' || v_PartPreDate ||
                          ' VALUES LESS THAN(''' || v_RangeValue ||
                          ''') TABLESPACE ' || v_TableSpace;
          dbms_output.put_line(v_SqlExec);
          v_SqlCursor := DBMS_SQL.OPEN_CURSOR;
          DBMS_SQL.PARSE(v_SqlCursor, v_SqlExec, DBMS_SQL.NATIVE);
          v_Rows := DBMS_SQL.EXECUTE(v_SqlCursor);
          DBMS_SQL.CLOSE_CURSOR(v_SqlCursor);
        END IF;
      END LOOP;
      COMMIT;

      INSERT INTO BIP_LOG_STAT_EXEC_TAB
      VALUES
        (TO_CHAR(SYSDATE, 'yyyymmddhh24miss'), 'BIP_MMS_PARTITION_PROC_ADD', 'END');
      COMMIT;

      --DELETE PARTITION
      INSERT INTO BIP_LOG_STAT_EXEC_TAB
      VALUES
        (TO_CHAR(SYSDATE, 'yyyymmddhh24miss'),
         'BIP_MMS_PARTITION_PROC_DEL',
         'BEGIN');
      COMMIT;

      BEGIN
        v_PartPreFlag := 'MMS_MT_CONTENT' || '_' ||
                         TO_CHAR(SYSDATE - p_Range, 'yyyymmdd');
        dbms_output.put_line(v_PartPreFlag);
        v_SqlExec := 'ALTER TABLE ' || v_Mms_Content_Tab ||
                     ' TRUNCATE PARTITION ' || v_PartPreFlag;
        dbms_output.put_line(v_SqlExec);
        v_SqlCursor := DBMS_SQL.OPEN_CURSOR;
        DBMS_SQL.PARSE(v_SqlCursor, v_SqlExec, DBMS_SQL.NATIVE);
        v_Rows := DBMS_SQL.EXECUTE(v_SqlCursor);
        DBMS_SQL.CLOSE_CURSOR(v_SqlCursor);
        dbms_output.put_line(v_PartPreFlag || ' truncated');

        v_SqlExec := 'ALTER TABLE ' || v_Mms_Content_Tab || ' DROP PARTITION ' ||
                     v_PartPreFlag;
        dbms_output.put_line(v_SqlExec);
        v_SqlCursor := DBMS_SQL.OPEN_CURSOR;
        DBMS_SQL.PARSE(v_SqlCursor, v_SqlExec, DBMS_SQL.NATIVE);
        v_Rows := DBMS_SQL.EXECUTE(v_SqlCursor);
        DBMS_SQL.CLOSE_CURSOR(v_SqlCursor);
        dbms_output.put_line(v_PartPreFlag || ' dropped');
      END;

      BEGIN
        v_PartPreFlag := 'MMS_MT_TASK_LOG' || '_' ||
                         TO_CHAR(SYSDATE - p_Range, 'yyyymmdd');
        dbms_output.put_line(v_PartPreFlag);
        v_SqlExec := 'ALTER TABLE ' || v_Mms_Task_Tab || ' TRUNCATE PARTITION ' ||
                     v_PartPreFlag;
        dbms_output.put_line(v_SqlExec);
        v_SqlCursor := DBMS_SQL.OPEN_CURSOR;
        DBMS_SQL.PARSE(v_SqlCursor, v_SqlExec, DBMS_SQL.NATIVE);
        v_Rows := DBMS_SQL.EXECUTE(v_SqlCursor);
        DBMS_SQL.CLOSE_CURSOR(v_SqlCursor);
        dbms_output.put_line(v_PartPreFlag || ' truncated');

        v_SqlExec   := 'ALTER TABLE ' || v_Mms_Task_Tab || ' DROP PARTITION ' ||
                       v_PartPreFlag;
        v_SqlCursor := DBMS_SQL.OPEN_CURSOR;
        DBMS_SQL.PARSE(v_SqlCursor, v_SqlExec, DBMS_SQL.NATIVE);
        v_Rows := DBMS_SQL.EXECUTE(v_SqlCursor);
        DBMS_SQL.CLOSE_CURSOR(v_SqlCursor);
        dbms_output.put_line(v_PartPreFlag || ' dropped');
      END;

      BEGIN
        v_PartPreFlag := 'MMS_MT_USER_LOG' || '_' ||
                         TO_CHAR(SYSDATE - p_Range, 'yyyymmdd');
        dbms_output.put_line(v_PartPreFlag);
        v_SqlExec := 'ALTER TABLE ' || v_Mms_User_Tab || ' TRUNCATE PARTITION ' ||
                     v_PartPreFlag;
        dbms_output.put_line(v_SqlExec);
        v_SqlCursor := DBMS_SQL.OPEN_CURSOR;
        DBMS_SQL.PARSE(v_SqlCursor, v_SqlExec, DBMS_SQL.NATIVE);
        v_Rows := DBMS_SQL.EXECUTE(v_SqlCursor);
        DBMS_SQL.CLOSE_CURSOR(v_SqlCursor);
        dbms_output.put_line(v_PartPreFlag || ' truncated');

        v_SqlExec   := 'ALTER TABLE ' || v_Mms_User_Tab || ' DROP PARTITION ' ||
                       v_PartPreFlag;
        v_SqlCursor := DBMS_SQL.OPEN_CURSOR;
        DBMS_SQL.PARSE(v_SqlCursor, v_SqlExec, DBMS_SQL.NATIVE);
        v_Rows := DBMS_SQL.EXECUTE(v_SqlCursor);
        DBMS_SQL.CLOSE_CURSOR(v_SqlCursor);
        dbms_output.put_line(v_PartPreFlag || ' dropped');
      END;

      COMMIT;

      INSERT INTO BIP_LOG_STAT_EXEC_TAB
      VALUES
        (TO_CHAR(SYSDATE, 'yyyymmddhh24miss'),
         'BIP_MMS_PARTITION_PROC_DEL',
         'END');
      COMMIT;
    EXCEPTION
      WHEN OTHERS THEN
        BEGIN
          ROLLBACK;
          dbms_output.put_line(TO_CHAR(SQLCODE));
          vErrInfo := SUBSTR(SQLERRM, 1, 200);
          dbms_output.put_line(TO_CHAR(vErrInfo));
          INSERT INTO BIP_LOG_STAT_EXEC_TAB
          VALUES
            (TO_CHAR(SYSDATE, 'yyyymmddhh24miss'),
             'BIP_MMS_PARTITION_PROC_ERROR',
             vErrInfo);
          COMMIT;
        END;

    end bip_mms_partition_proc;
     

    您可能感兴趣的文章:
    • 详解oracle的分表之表分区的具体使用和示例
    • oracle表空间表分区详解及oracle表分区查询使用方法
    • Oracle 数据表分区的策略
    • Oracle数据表分区的策略
    • oracle表分区的概念及操作
    上一篇:oracle联机数据库备份详解
    下一篇:oracle 使用sql获取数据库表、表的字段的多种方法
  • 相关文章
  • 

    © 2016-2020 巨人网络通讯 版权所有

    《增值电信业务经营许可证》 苏ICP备15040257号-8

    oracle存储过程创建表分区实例 oracle,存储,过程,创建,表,