Create a table partition instance in the oracle Stored Procedure

Create a data table using a stored procedure:
When creating a table, note that you must add authid current_user. If the created table already exists, the stored procedure continues to run. If you do not add this key statement, the stored procedure will be abnormal,
This statement is equivalent to granting permissions.
Example 1
The creation statement is as follows:
Copy codeThe Code is as follows:
Create or replace
Procedure sp_create_mnl (I _id varchar2) authid current_user
/*********************************
Name: sp_create_mnl
Function Description: Creates a storage table for historical analog data.

Revision history:
Version Number editing time modifier modify description
1.0.0 2012-9-20 wylaok 1. Create this stored procedure
1.0.1 2012-9-21 wylaok 2. Modify the table name and variable name and add necessary comments

Description of input and output parameters:
I _id measurement point number
**********************************/
V_tablename varchar2 (30); -- table name
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 -- if this table is not available, create it.
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
(
Initialize 512 K
Next 512 K
Minextents 1
Maxextents unlimited
Pctincrease 0
)';
-- Execute immediate sqlstr;
End;
End if;
End;

Call this stored procedure:
Copy codeThe Code is as follows:
Begin
Createmnl ('mnl _ 14 ');
End;

Example 2
Copy codeThe Code is as follows:
Create or replace procedure BIP_MMS_PARTITION_PROC

V_Mms_Task_Tab VARCHAR2 (50); -- table name
V_Mms_Content_Tab VARCHAR2 (50 );
V_Mms_User_Tab VARCHAR2 (50 );
V_TableSpace VARCHAR2 (20); -- tablespace
V_PartPreFlag VARCHAR2 (50); -- partition name ID
V_SqlCursor NUMBER; -- cursor
V_SqlExec VARCHAR2 (2000); -- execute the statement
V_PartPreDate VARCHAR2 (20); -- partition date
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 ';
-- Read configuration parameters
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;
-- Add partitions in the Stored Procedure
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: Add a partition.
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 | '20140901 ';
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: Add a partition.
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 | '20140901 ';
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: Add a partition.
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 | '20140901 ';
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;