1. 首页
  2. 编程与技术

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

发布于 2016-03-24 07:39:01 | 110 次阅读 | 评论: 0 | 来源: 网友投递

这里有新鲜出炉的精品教程，程序狗速度看过来！

Oracle关系数据库管理系统

Oracle Database，又名Oracle RDBMS，或简称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;
 



最新网友评论  共有(0)条评论 发布评论 返回顶部
推荐阅读
  • 终极炫富——2016 年 iOS 开发者收入调查报告
  • 年度大片：Stack Overflow 2017 开发者调查报告发布！
  • 开发者需要大学学位吗？
  • 程序员多大年纪算高龄，届时该何去何从？
  • 2017 年高薪编程语言排行榜，你的语言上榜没？
  • 各编程语言开发者最常使用的按键，中了没?
  • 伤不起的三十岁，干不动的程序员要何去何从？
  • 2016年中国加班最狠IT公司排行
  • 一句话"面试"漫画：黑了苹果、谷歌、三星
  • 拿代码量算 KPI ……跟程序员们来这套？
  • 程序员遇到 Bug 时的 30 个反应，你是哪一种？
  • 2016年收入最高的5个编程语言 Java和C没在前五?
  • 为什么很少见人傻钱多的程序员炫富？
  • 阿里月薪两万是什么体验？
  • 代码缩进时 程序员更喜欢空格键还是Tab键？
    • 最新资讯
  • Python获取系统所有进程PID及进程名称的方法示例
  • Python简单获取网卡名称及其IP地址的方法【基于psutil模块】
  • Python实现的根据IP地址计算子网掩码位数功能示例
  • 好的Python培训机构应该具备哪些条件
  • 详解Pytorch 使用Pytorch拟合多项式(多项式回归)
  • Python加载带有注释的Json文件实例
  • 对python的文件内注释 help注释方法
  • Python实现判断一行代码是否为注释的方法
  • Oracle数据库表中字段顺序的修改方法
  • ASP.NET MVC 4使用PagedList.Mvc分页的实现代码
  • Oracle实例启动时报错：ORA-32004的解决方法
  • 解读ASP.NET 5 & MVC6系列教程（7）：依赖注入
  • 详解IDEA用maven创建springMVC项目和配置
  • ASP.NET MVC5网站开发之展示层架构（五）
  • SpringBoot创建maven多模块项目实战代码
    • 后端技术
    前端技术
    数据库
    热门框架
    常用IDE
    其他
    Copyright © 2007-2017 PHPERZ.COM All Rights Reserved   冀ICP备14009818号  版权声明  广告服务