oracle存储过程创建表分区实例
数据库 / 管理员 发布于 6年前 137
用存储过程创建数据表:
创建时注意必须添加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 使用sql获取数据库表、表的字段的多种方法
下一条：
oracle联机数据库备份详解
- 相关文章
- 分库分表的目的、优缺点及具体实现方式介绍(0个评论)
- DevDB - 在 VS 代码中直接访问数据库(0个评论)
- 在ubuntu系统中实现mysql数据存储目录迁移流程步骤(0个评论)
- 在mysql中使用存储过程批量新增测试数据流程步骤(0个评论)
- php+mysql数据库批量根据条件快速更新、连表更新sql实现(0个评论)
- 近期文章
- 在go中实现一个常用的先进先出的缓存淘汰算法示例代码(0个评论)
- 在go+gin中使用"github.com/skip2/go-qrcode"实现url转二维码功能(0个评论)
- 在go语言中使用api.geonames.org接口实现根据国际邮政编码获取地址信息功能(1个评论)
- 在go语言中使用github.com/signintech/gopdf实现生成pdf分页文件功能(0个评论)
- gmail发邮件报错:534 5.7.9 Application-specific password required...解决方案(0个评论)
- 欧盟关于强迫劳动的规定的官方举报渠道及官方举报网站(0个评论)
- 在go语言中使用github.com/signintech/gopdf实现生成pdf文件功能(0个评论)
- Laravel从Accel获得5700万美元A轮融资(0个评论)
- 在go + gin中gorm实现指定搜索/区间搜索分页列表功能接口实例(0个评论)
- 在go语言中实现IP/CIDR的ip和netmask互转及IP段形式互转及ip是否存在IP/CIDR(0个评论)
- 近期评论
-
122 在
学历：一种延缓就业设计，生活需求下的权衡之选中评论 工作几年后，报名考研了，到现在还没认真学习备考，迷茫中。作为一名北漂互联网打工人.. -
123 在
Clash for Windows作者删库跑路了，github已404中评论 按理说只要你在国内，所有的流量进出都在监控范围内，不管你怎么隐藏也没用，想搞你分.. -
原梓番博客 在
在Laravel框架中使用模型Model分表最简单的方法中评论 好久好久都没看友情链接申请了，今天刚看，已经添加。.. -
博主 在
佛跳墙vpn软件不会用?上不了网?佛跳墙vpn常见问题以及解决办法中评论 @1111老铁这个不行了，可以看看近期评论的其他文章.. -
1111 在
佛跳墙vpn软件不会用?上不了网?佛跳墙vpn常见问题以及解决办法中评论 网站不能打开，博主百忙中能否发个APP下载链接，佛跳墙或极光..
Copyright·© 2019 侯体宗版权所有·
粤ICP备20027696号
