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;

本文oracle存储过程创建表分区实例到此结束。气不和时少说话，出言必失；心不顺时莫处事，行事必败。事莫虚应，应则必办，不办便怨；愿莫轻许，许愿必还，不还成债。万物为说法，关键在着眼；一切是考验，用心才有为。知足是富人，平常是高人，无心是圣人。想开了自然微笑，看破了肯定放下。小编再次感谢大家对我们的支持！

您可能有感兴趣的文章
Oracle数据库存储过程的调试过程
oracle通过存储过程上传list保存功能
如何解决PL/SQL修改Oracle存储过程编译就卡死的问题
oracle 存储过程返回 结果集 table形式的案例
一篇关于Oracle存储过程和调度器如何实现自动对数据库过期数据清除的问题