oracle存儲(chǔ)過(guò)程創(chuàng)建表分區(qū)實(shí)例

2024-08-29 13:57:43

字體：大中小

供稿：網(wǎng)友

用存儲(chǔ)過(guò)程創(chuàng)建數(shù)據(jù)表:
創(chuàng)建時(shí)注意必須添加authid current_user，如果創(chuàng)建的表已存在，存儲(chǔ)過(guò)程繼續(xù)執(zhí)行，但如不不加此關(guān)鍵語(yǔ)句，存儲(chǔ)過(guò)程將出現(xiàn)異常，
這個(gè)語(yǔ)句相當(dāng)于賦權(quán)限。
例1
創(chuàng)建語(yǔ)句如下：

復(fù)制代碼代碼如下:

create or replace
procedure sp_create_mnl(i_id varchar2) authid current_user as
/*********************************
名稱(chēng)：sp_create_mnl
功能描述：創(chuàng)建模擬量歷史數(shù)據(jù)存儲(chǔ)表

修訂記錄：
版本號(hào)   編輯時(shí)間編輯人修改描述
1.0.0    2012-9-20 wylaok 1.創(chuàng)建此存儲(chǔ)過(guò)程
1.0.1    2012-9-21 wylaok 2.修改表名稱(chēng)及變量名稱(chēng)，增加必要注釋

入?yún)⒊鰠⒚枋觯?
i_id 測(cè)點(diǎn)編號(hào)
**********************************/
      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 --如果沒(méi)有這個(gè)表則去創(chuàng)建
         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;

調(diào)用此存儲(chǔ)過(guò)程：

復(fù)制代碼代碼如下:

begin
createmnl('mnl_14');
end;

例2

復(fù)制代碼代碼如下:

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); --分區(qū)名標(biāo)識(shí)
v_SqlCursor       NUMBER; --游標(biāo)
v_SqlExec         VARCHAR2(2000); --執(zhí)行語(yǔ)句
v_PartPreDate     VARCHAR2(20); --分區(qū)日期
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';
-- 讀取配置參數(shù)
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;
--記錄存儲(chǔ)過(guò)程添加分區(qū)
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 添加分區(qū)
    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 添加分區(qū)
    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 添加分區(qū)
    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;