oracle存储过程创建表分区实例_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;
 

时间： 2024-12-03 12:00:04

oracle存储过程创建表分区实例_oracle的相关文章

oracle 存储过程创建表分区二种方法

用存储过程创建数据表: 创建时注意必须添加authid current_user ,如果创建的表已存在,存储过程继续执行,但如不不加此关键语句,存储过程将出现异常, 这个语句相当于赋权限. 例1 创建语句如下:  代码如下 复制代码 create or replace procedure sp_create_mnl(i_id varchar2) authid current_user  as   /********************************* 名称:sp_create_mn

oracle为一个表创建表分区问题

问题描述 oracle一个日志表,每日的数据量是2000万,该表数据要保留3-6个月.如何创建表分区.求指点 解决方案 这种么 直接hash分区或者range分区都可以吧,用hash用得比较多..因为字段的值不确定用range不好办而且不均匀,像这种日期比较确定的用range可以的,用hash也行http://blog.csdn.net/xieyuooo/article/details/5437126 http://space.itpub.net/18953778/viewspace-55747

[ASP.NET]对Oracle数据库创建表/判断数据表是否已存在

asp.net|oracle|创建|数据|数据库 对Oracle数据库创建表: Dim myConnectionString As String Dim myConnection As OleDbConnection Dim myCommand As OleDbCommand Dim myCreateSQL As String myConnectionString = "Provider='OraOLEDB.Oracle.1';User ID=[User ID];Data Source=[Dat

利用存储过程创建表提示权限不足，但是单独创建就可以

问题描述 利用存储过程创建表提示权限不足,但是单独创建就可以 利用存储过程创建表提示权限不足,但是单独创建就可以 create or replace procedure PRC_GZF_BZ_YWSLID(ywslid in verchar2) as L_YWSLID verchar2(30); tpg_gzfspbnum verchar2(30);begin L_YWSLID:=ywslid; tpg_gzfspbnum:='tpg_gzfspb'||L_SLIDID;execute imme

PostgreSQL 创建表分区_PostgreSQL

创建表分区步骤如下: 1. 创建主表 CREATE TABLE users ( uid int not null primary key, name varchar(20)); 2. 创建分区表(必须继承上面的主表) CREATE TABLE users_0 ( check (uid >= 0 and uid< 100) ) INHERITS (users); CREATE TABLE users_1 ( check (uid >= 100)) INHERITS (users); 3.

SQL Server 创建表分区

原文:SQL Server 创建表分区 先准备测试表 CREATE TABLE [dbo].[Employee] ( EmployeeNo INT IDENTITY(1,1) PRIMARY KEY, EmployeeName NVARCHAR(50) NULL ); 插入一些数据 INSERT Employee DEFAULT VALUES; GO 10000 UPDATE Employee set EmployeeName = 'Employee ' + CONVERT(VARCHAR, E

oracle 临时表详解及实例_oracle

在Oracle8i或以上版本中,可以创建以下两种临时表: 1.会话特有的临时表 CREATE GLOBAL TEMPORARY <TABLE_NAME> ( <column specification> ) ON COMMIT PRESERVE ROWS:  2.事务特有的临时表 CREATE GLOBAL TEMPORARY <TABLE_NAME> ( <column specification> ) ON COMMIT DELETE ROWS: CRE

oracle Streams创建表空间级环境概述：初始化

DBMS_STREAMS_ADM 中提供了三种创建表空间级复制环境的过程: MAINTAIN_SIMPLE_TTS :单个表空间的复制 MAINTAIN_TTS :多个表空间的复制 PRE_INSTANTIATION_SETUP 和POST_INSTANTIATION_SETUP:多个表空间的复制 MAINTAIN_SIMPLE_TTS 和MAINTAIN_TTS两过程都会自动添加否定规则集过滤streams不支持的数据库对象,PRE_INSTAINTIATION_SETUP和POST_INST

Oracle 如何创建和使用全文索引_oracle

不使用Oracle text功能,也有很多方法可以在Oracle数据库中搜索文本.可以使用标准的INSTR函数和LIKE操作符实现. SELECT *FROM mytext WHERE INSTR (thetext, 'Oracle') > 0; SELECT * FROM mytext WHERE thetext LIKE '%Oracle%'; 有很多时候,使用instr和like是很理想的, 特别是搜索仅跨越很小的表的时候.然而通过这些文本定位的方法将导致全表扫描,对资源来说消耗比较昂贵,