Oracle stored procedures CREATE TABLE partitions two ways

To create a data table with a stored procedure:

Note that you must add Authid current_user if you create a table that already exists, the stored procedure continues to execute, but if you do not add this key statement, the stored procedure will appear unexpectedly.

This statement is equivalent to assigning permissions.

Example 1

The statement is created as follows:

The code is as follows

Copy Code

Create or replace Procedure sp_create_mnl (i_id varchar2) Authid Current_User as /********************************* Name: SP_CREATE_MNL Function Description: Create analog volume History data store table Revision history: Version number edit time edit person Modify description 1.0.0 2012-9-20 wylaok 1. Create this stored procedure 1.0.1 2012-9-21 wylaok 2. Modify table name and variable name, add necessary comments Enter the parameter description: I_ID Measuring 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 there is no this table to create 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 Call this stored procedure: Begin CREATEMNL (' mnl_14 '); End

Example 2

The code is as follows

Copy Code

CREATE OR REPLACE PROCEDURE Bip_mms_partition_proc as V_mms_task_tab VARCHAR2 (50); --Table name V_mms_content_tab VARCHAR2 (50); V_mms_user_tab VARCHAR2 (50); V_tablespace VARCHAR2 (20); --Table space V_partpreflag VARCHAR2 (50); --Partition name identification V_sqlcursor number; --Cursor V_sqlexec VARCHAR2 (2000); --EXECUTE 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; --Record stored procedure add partition 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 Partitions 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, 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 Add Partitions 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, 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 Add Partitions 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, 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;