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

PHP小丑

用存储过程创建数据表:
创建时注意必须添加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;