熱點推薦：
您现在的位置： 電腦知識網 >> 編程 >> Oracle >> 正文

oracle存儲過程創建表分區實例

2022-06-13   來源: Oracle 

　　用存儲過程創建數據表:
創建時注意必須添加authid current_user如果創建的表已存在存儲過程繼續執行但如不不加此關鍵語句存儲過程將出現異常
這個語句相當於賦權限
例
創建語句如下

　　復制代碼 代碼如下:

　　create or replace
procedure sp_create_mnl(i_id varchar) authid current_user  as
  /*********************************
名稱sp_create_mnl
功能描述創建模擬量歷史數據存儲表

修訂記錄
版本號   編輯時間  編輯人  修改描述
    wylaok  創建此存儲過程
    wylaok  修改表名稱及變量名稱增加必要注釋

入參出參描述
i_id 測點編號
**********************************/
      v_tablename varchar();表名
      v_flag number();
      v_sqlfalg varchar();
begin
  v_flag:=;
      v_tablename:=CONCAT(MNL UPPER(i_id));
      v_sqlfalg:=select count(*) from user_TABLES where table_name=||v_tablename||;
      dbms_outputput_line(v_sqlfalg);
      execute immediate v_sqlfalg into v_flag;
      if v_flag= then  如果沒有這個表 則去創建
         begin
  execute immediate create table ||v_tablename ||
  ( DATETIME DATE
  MIN    FLOAT
  AGV    FLOAT
  MAX    FLOAT
  MIN    FLOAT
  AVG    FLOAT
  MAX    FLOAT
  MIN    FLOAT
  AGV    FLOAT
  MAX    FLOAT
  MIN    FLOAT
  AGV    FLOAT
  MAX    FLOAT
  MIN    FLOAT
  AGV    FLOAT
  MAX    FLOAT
  MIN    FLOAT
  AGV    FLOAT
  MAX    FLOAT
  MIN    FLOAT
  AGV    FLOAT
  MAX    FLOAT
  MIN    FLOAT
  AGV    FLOAT
  MAX    FLOAT
  MIN    FLOAT
  AGV    FLOAT
  MAX    FLOAT
  MIN    FLOAT
  AGV    FLOAT
  MAX    FLOAT
  MIN    FLOAT
  AGV    FLOAT
  MAX    FLOAT
  MIN    FLOAT
  AGV    FLOAT
  MAX    FLOAT
  MINV     FLOAT
  MAXV     FLOAT
  AVGV     FLOAT
  MAXTIME  DATE
  MINTIME  DATE
  )
  tablespace WYG
  pctfree
  initrans
  maxtrans
  storage
  (
    initial K
    next K
    minextents
    maxextents unlimited
    pctincrease
  );
      execute immediate sqlstr;
         end;
      end if;
      end;


調用此存儲過程

　　復制代碼 代碼如下:

　　begin
  createmnl(mnl_);
  end;


例

　　復制代碼 代碼如下:

　　CREATE OR REPLACE PROCEDURE BIP_MMS_PARTITION_PROC AS

  v_Mms_Task_Tab    VARCHAR(); 表名
  v_Mms_Content_Tab VARCHAR();
  v_Mms_User_Tab    VARCHAR();
  v_TableSpace      VARCHAR(); 表空間
  v_PartPreFlag     VARCHAR(); 分區名標識
  v_SqlCursor       NUMBER; 游標
  v_SqlExec         VARCHAR(); 執行語句
  v_PartPreDate     VARCHAR(); 分區日期
  v_RangeValue      NUMBER;
  v_RangeDate       NUMBER;
  v_Rows            NUMBER() := ;
  v_Num             NUMBER() := ;
  vErrInfo          VARCHAR();
  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 := ;
        p_PartNum  := ;
        p_Range    := ;
      END;
  END;
  記錄存儲過程添加分區
  INSERT INTO BIP_LOG_STAT_EXEC_TAB
  VALUES
    (TO_CHAR(SYSDATE yyyymmddhhmiss)
     BIP_MMS_PARTITION_PROC_ADD
     BEGIN);
  COMMIT;
  ADD PARTITION 
  FOR i IN p_PartNum LOOP
    BIP_MMS_MT_CONTENT_TAB 添加分區
    v_PartPreDate := TO_CHAR(SYSDATE + p_DateFrom + i YYYYMMDD);
    dbms_outputput_line(v_PartPreDate);
    v_Num         := ;
    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 ) = v_PartPreDate;
    IF v_Num < THEN
      v_RangeDate  := TO_CHAR(SYSDATE + p_DateFrom + i YYYYMMDD);
      v_RangeValue := v_RangeDate || ;
      dbms_outputput_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_outputput_line(v_SqlExec);
      v_SqlCursor := DBMS_SQLOPEN_CURSOR;
      DBMS_SQLPARSE(v_SqlCursor v_SqlExec DBMS_SQLNATIVE);
      v_Rows := DBMS_SQLEXECUTE(v_SqlCursor);
      DBMS_SQLCLOSE_CURSOR(v_SqlCursor);
    END IF;
    BIP_MMS_MT_TASK_LOG_TAB_TEST 添加分區
    v_PartPreDate := TO_CHAR(SYSDATE + p_DateFrom + i YYYYMMDD);
    v_Num         := ;
    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 ) = v_PartPreDate;
    IF v_Num < THEN
      v_RangeDate  := TO_CHAR(SYSDATE + p_DateFrom + i YYYYMMDD);
      v_RangeValue := v_RangeDate || ;
      v_SqlExec    := ALTER TABLE || v_Mms_Task_Tab || ADD PARTITION ||
                      v_PartPreFlag || _ || v_PartPreDate ||
                      VALUES LESS THAN( || v_RangeValue ||
                      ) TABLESPACE || v_TableSpace;
      dbms_outputput_line(v_SqlExec);
      v_SqlCursor := DBMS_SQLOPEN_CURSOR;
      DBMS_SQLPARSE(v_SqlCursor v_SqlExec DBMS_SQLNATIVE);
      v_Rows := DBMS_SQLEXECUTE(v_SqlCursor);
      DBMS_SQLCLOSE_CURSOR(v_SqlCursor);
    END IF;
    BIP_MMS_MT_USER_LOG_TAB_TEST 添加分區
    v_PartPreDate := TO_CHAR(SYSDATE + p_DateFrom + i YYYYMMDD);
    v_Num         := ;
    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 ) = v_PartPreDate;
    IF v_Num < THEN
      v_RangeDate  := TO_CHAR(SYSDATE + p_DateFrom + i YYYYMMDD);
      v_RangeValue := v_RangeDate || ;
      v_SqlExec    := ALTER TABLE || v_Mms_User_Tab || ADD PARTITION ||
                      v_PartPreFlag || _ || v_PartPreDate ||
                      VALUES LESS THAN( || v_RangeValue ||
                      ) TABLESPACE || v_TableSpace;
      dbms_outputput_line(v_SqlExec);
      v_SqlCursor := DBMS_SQLOPEN_CURSOR;
      DBMS_SQLPARSE(v_SqlCursor v_SqlExec DBMS_SQLNATIVE);
      v_Rows := DBMS_SQLEXECUTE(v_SqlCursor);
      DBMS_SQLCLOSE_CURSOR(v_SqlCursor);
    END IF;
  END LOOP;
  COMMIT;

  INSERT INTO BIP_LOG_STAT_EXEC_TAB
  VALUES
    (TO_CHAR(SYSDATE yyyymmddhhmiss) BIP_MMS_PARTITION_PROC_ADD END);
  COMMIT;

  DELETE PARTITION
  INSERT INTO BIP_LOG_STAT_EXEC_TAB
  VALUES
    (TO_CHAR(SYSDATE yyyymmddhhmiss)
     BIP_MMS_PARTITION_PROC_DEL
     BEGIN);
  COMMIT;

  BEGIN
    v_PartPreFlag := MMS_MT_CONTENT || _ ||
                     TO_CHAR(SYSDATE p_Range yyyymmdd);
    dbms_outputput_line(v_PartPreFlag);
    v_SqlExec := ALTER TABLE || v_Mms_Content_Tab ||
                 TRUNCATE PARTITION || v_PartPreFlag;
    dbms_outputput_line(v_SqlExec);
    v_SqlCursor := DBMS_SQLOPEN_CURSOR;
    DBMS_SQLPARSE(v_SqlCursor v_SqlExec DBMS_SQLNATIVE);
    v_Rows := DBMS_SQLEXECUTE(v_SqlCursor);
    DBMS_SQLCLOSE_CURSOR(v_SqlCursor);
    dbms_outputput_line(v_PartPreFlag || truncated);

    v_SqlExec := ALTER TABLE || v_Mms_Content_Tab || DROP PARTITION ||
                 v_PartPreFlag;
    dbms_outputput_line(v_SqlExec);
    v_SqlCursor := DBMS_SQLOPEN_CURSOR;
    DBMS_SQLPARSE(v_SqlCursor v_SqlExec DBMS_SQLNATIVE);
    v_Rows := DBMS_SQLEXECUTE(v_SqlCursor);
    DBMS_SQLCLOSE_CURSOR(v_SqlCursor);
    dbms_outputput_line(v_PartPreFlag || dropped);
  END;

  BEGIN
    v_PartPreFlag := MMS_MT_TASK_LOG || _ ||
                     TO_CHAR(SYSDATE p_Range yyyymmdd);
    dbms_outputput_line(v_PartPreFlag);
    v_SqlExec := ALTER TABLE || v_Mms_Task_Tab || TRUNCATE PARTITION ||
                 v_PartPreFlag;
    dbms_outputput_line(v_SqlExec);
    v_SqlCursor := DBMS_SQLOPEN_CURSOR;
    DBMS_SQLPARSE(v_SqlCursor v_SqlExec DBMS_SQLNATIVE);
    v_Rows := DBMS_SQLEXECUTE(v_SqlCursor);
    DBMS_SQLCLOSE_CURSOR(v_SqlCursor);
    dbms_outputput_line(v_PartPreFlag || truncated);

    v_SqlExec   := ALTER TABLE || v_Mms_Task_Tab || DROP PARTITION ||
                   v_PartPreFlag;
    v_SqlCursor := DBMS_SQLOPEN_CURSOR;
    DBMS_SQLPARSE(v_SqlCursor v_SqlExec DBMS_SQLNATIVE);
    v_Rows := DBMS_SQLEXECUTE(v_SqlCursor);
    DBMS_SQLCLOSE_CURSOR(v_SqlCursor);
    dbms_outputput_line(v_PartPreFlag || dropped);
  END;

  BEGIN
    v_PartPreFlag := MMS_MT_USER_LOG || _ ||
                     TO_CHAR(SYSDATE p_Range yyyymmdd);
    dbms_outputput_line(v_PartPreFlag);
    v_SqlExec := ALTER TABLE || v_Mms_User_Tab || TRUNCATE PARTITION ||
                 v_PartPreFlag;
    dbms_outputput_line(v_SqlExec);
    v_SqlCursor := DBMS_SQLOPEN_CURSOR;
    DBMS_SQLPARSE(v_SqlCursor v_SqlExec DBMS_SQLNATIVE);
    v_Rows := DBMS_SQLEXECUTE(v_SqlCursor);
    DBMS_SQLCLOSE_CURSOR(v_SqlCursor);
    dbms_outputput_line(v_PartPreFlag || truncated);

    v_SqlExec   := ALTER TABLE || v_Mms_User_Tab || DROP PARTITION ||
                   v_PartPreFlag;
    v_SqlCursor := DBMS_SQLOPEN_CURSOR;
    DBMS_SQLPARSE(v_SqlCursor v_SqlExec DBMS_SQLNATIVE);
    v_Rows := DBMS_SQLEXECUTE(v_SqlCursor);
    DBMS_SQLCLOSE_CURSOR(v_SqlCursor);
    dbms_outputput_line(v_PartPreFlag || dropped);
  END;

  COMMIT;

  INSERT INTO BIP_LOG_STAT_EXEC_TAB
  VALUES
    (TO_CHAR(SYSDATE yyyymmddhhmiss)
     BIP_MMS_PARTITION_PROC_DEL
     END);
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    BEGIN
      ROLLBACK;
      dbms_outputput_line(TO_CHAR(SQLCODE));
      vErrInfo := SUBSTR(SQLERRM );
      dbms_outputput_line(TO_CHAR(vErrInfo));
      INSERT INTO BIP_LOG_STAT_EXEC_TAB
      VALUES
        (TO_CHAR(SYSDATE yyyymmddhhmiss)
         BIP_MMS_PARTITION_PROC_ERROR
         vErrInfo);
      COMMIT;
    END;

end bip_mms_partition_proc;
 


From:http://tw.wingwit.com/Article/program/Oracle/201405/30854.html
    《oracle存儲過程創建表分區實例》本文是由Oracle免費提供，內容來源於互聯網,本文歸原作者所有。
  • 上一篇文章：

  • 下一篇文章：
    • 相關文章
    主板 CPU 硬盤 內存 顯卡 聲卡 顯示器 光驅 死機
    推薦文章
    Copyright © 2005-2022 電腦知識網 Computer Knowledge   All rights reserved.