数据库

位置:IT落伍者 >> 数据库 >> 浏览文章

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


发布日期:2023年07月25日
 
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;

上一篇:oracle数据库下统计专营店的男女数量的语句

下一篇:深入探讨:oracle中row