[php]
\n\/*\u4e34\u65f6\u8f6c\u6362\u8868*\/
\ncreate table SPLIT_TABLE
\n(
\ntable_name VARCHAR2(30),
\npartition_name VARCHAR2(30),
\nhigh_value CLOB,
\ntablespace_name VARCHAR2(30),
\np_date DATE
\n)\uff1b
\n\/*\u751f\u6210\u7684sql*\/
\ncreate table SPLIT_TABLE_RSLT
\n(
\n table_name VARCHAR2(30),
\n partition_name VARCHAR2(30),
\n split_sql VARCHAR2(3000),
\n insert_date DATE,
\n app_date DATE,
\n id NUMBER
\n)
\n\/*\u5e8f\u5217\uff0c\u4fdd\u8bc1sql\u6267\u884c\u987a\u5e8f*\/
\n— Create sequence
\ncreate sequence SEQ_SPLIT_TABLE_RSLT
\nminvalue 1
\nmaxvalue 999999999999999999999999999
\nstart with 61
\nincrement by 1
\ncache 20;<\/p>\n
\/* \u751f\u6210\u5206\u533asql *\/
\nCREATE OR REPLACE PROCEDURE SPLIT_TABLE_SQL(V_TABLE_NAME IN VARCHAR2,
\n V_END_DATE IN VARCHAR2) AUTHID CURRENT_USER AS
\n N_MONTHS NUMBER;
\n V_SQL VARCHAR2(300);
\n V_SPLIT_SQL VARCHAR2(300);
\n V_MONTHS_DATE VARCHAR2(30);
\n V_MONTHS_DATE1 VARCHAR2(30);
\n V_PARTITION_NAME VARCHAR2(30);
\n V_PARTITION_NAME_MAX VARCHAR2(30);
\n D_PART_DATE DATE;
\n D_MAX_DATE DATE;
\n D_END_DATE DATE := TO_DATE(V_END_DATE || ‘ 00:00:00’,
\n ‘SYYYY-MM-DD HH24:MI:SS’);
\n V_SQL1 VARCHAR2(300);
\nBEGIN
\n \/* \u6e05\u7a7a\u4e2d\u95f4\u8868\uff0c\u5c06\u65b0\u7684\u5206\u533a\u4fe1\u606f\u8f7d\u5165\u8be5\u8868 *\/
\n EXECUTE IMMEDIATE ‘TRUNCATE TABLE SPLIT_TABLE’;
\n INSERT INTO SPLIT_TABLE
\n (TABLE_NAME, PARTITION_NAME, HIGH_VALUE, TABLESPACE_NAME)
\n SELECT T.TABLE_NAME,
\n T.PARTITION_NAME,
\n TO_LOB(T.HIGH_VALUE) HIGH_VALUE,
\n T.TABLESPACE_NAME
\n FROM USER_TAB_PARTITIONS T
\n WHERE T.TABLE_NAME = V_TABLE_NAME;
\n COMMIT;
\n \/* \u5c06\u5206\u533a\u4fe1\u606f\u6539\u5199\u6210\u65e5\u671f \u6c42\u5206\u533a\u4fe1\u606f\u4e2d\u9664\u4e86MAXVALUE \u6700\u5927\u7684\u65e5\u671f*\/
\n FOR C1 IN (SELECT TE.TABLE_NAME,
\n TE.PARTITION_NAME,
\n TO_CHAR(TE.HIGH_VALUE) HIGH_VALUE
\n FROM SPLIT_TABLE TE
\n WHERE TE.TABLE_NAME = V_TABLE_NAME
\n AND TO_CHAR(TE.HIGH_VALUE) <> ‘MAXVALUE’) LOOP
\n V_SQL1 := ‘select to_char( ‘ || C1.HIGH_VALUE || ‘ ) from dual’;
\n EXECUTE IMMEDIATE V_SQL1
\n INTO D_PART_DATE;
\n UPDATE SPLIT_TABLE SE
\n SET SE.P_DATE = D_PART_DATE
\n WHERE SE.TABLE_NAME = C1.TABLE_NAME
\n AND SE.PARTITION_NAME = C1.PARTITION_NAME;
\n COMMIT;
\n END LOOP;
\n EXECUTE IMMEDIATE ‘ SELECT MAX(LE.P_DATE) FROM SPLIT_TABLE LE GROUP BY LE.TABLE_NAME’
\n INTO D_MAX_DATE;
\n \/*\u6c42\u5206\u533a\u4fe1\u606f\u4e2d\u542b\u6709MAXVALUE PARTITION_NAME \u7684 *\/
\n EXECUTE IMMEDIATE ‘ SELECT TE.PARTITION_NAME FROM SPLIT_TABLE TE WHERE TO_CHAR(TE.HIGH_VALUE) = ‘ ||
\n CHR(39) || ‘MAXVALUE’ || CHR(39)
\n INTO V_PARTITION_NAME_MAX;
\n N_MONTHS := MONTHS_BETWEEN(D_END_DATE, D_MAX_DATE);
\n \/* \u62fc\u63a5sql *\/
\n FOR I IN 1 .. N_MONTHS LOOP
\n V_MONTHS_DATE := TO_CHAR(ADD_MONTHS(D_MAX_DATE, I), ‘YYYY\/MM’);
\n V_MONTHS_DATE1 := TO_CHAR(ADD_MONTHS(D_MAX_DATE, I – 1), ‘YYYYMM’);
\n V_PARTITION_NAME := ‘PART_’ || V_MONTHS_DATE1;
\n V_SPLIT_SQL := ‘ALTER TABLE ‘ || V_TABLE_NAME ||
\n ‘ SPLIT PARTITION ‘ || V_PARTITION_NAME_MAX ||
\n ‘ AT( TO_DATE( ‘ || CHR(39) || V_MONTHS_DATE ||
\n ‘\/01 00:00:00’ || CHR(39) || ‘, ‘ || CHR(39) ||
\n ‘SYYYY\/MM\/DD HH24:MI:SS’ || CHR(39) || ‘,’ ||
\n CHR(39) || ‘NLS_CALENDAR=GREGORIAN’ || CHR(39) ||
\n ‘) ) INTO(PARTITION ‘ || V_PARTITION_NAME ||
\n ‘,PARTITION ‘ || V_PARTITION_NAME_MAX || ‘)’;
\n –DBMS_OUTPUT.PUT_LINE(V_SPLIT_SQL);
\n INSERT INTO SPLIT_TABLE_RSLT ST
\n (ST.ID,
\n ST.TABLE_NAME,
\n ST.PARTITION_NAME,
\n ST.SPLIT_SQL,
\n ST.INSERT_DATE)
\n VALUES
\n (SEQ_SPLIT_TABLE_RSLT.NEXTVAL,
\n V_TABLE_NAME,
\n V_PARTITION_NAME,
\n V_SPLIT_SQL,
\n SYSDATE);
\n COMMIT;
\n END LOOP;
\nEND;<\/p>\n
\/*\u6267\u884c\u62c6\u5206\u811a\u672c*\/
\nCREATE OR REPLACE PROCEDURE EXEC_SPLIT_TABLE(V_TABLE_NAME IN VARCHAR2,
\n V_END_DATE IN VARCHAR2)
\n AUTHID CURRENT_USER AS
\n V_PROGRAM VARCHAR2(30) := ‘\u62c6\u5206\u5206\u533a’;
\n V_START_TIME DATE := SYSDATE;
\n V_TABLE_NAME1 VARCHAR2(30);
\n V_PARTITION_NAME VARCHAR2(30);
\nBEGIN
\n DELETE SPLIT_TABLE_RSLT ST WHERE ST.TABLE_NAME = V_TABLE_NAME;
\n SPLIT_TABLE_SQL(V_TABLE_NAME, V_END_DATE);
\n FOR C1 IN (SELECT ET.TABLE_NAME,
\n ET.PARTITION_NAME,
\n ET.SPLIT_SQL,
\n ET.INSERT_DATE,
\n ET.APP_DATE
\n FROM SPLIT_TABLE_RSLT ET
\n WHERE ET.TABLE_NAME = V_TABLE_NAME
\n ORDER BY ET.ID) LOOP
\n V_START_TIME := SYSDATE;
\n V_TABLE_NAME1 := C1.TABLE_NAME;
\n V_PARTITION_NAME := C1.PARTITION_NAME;
\n SAVE_ERROR_INFO(SQLCODE,
\n ‘\u8868:’ || C1.TABLE_NAME || ‘,\u5206\u533a\u540d:’ || C1.PARTITION_NAME ||
\n ‘\u5f00\u59cb\u6267\u884c\uff01\u5f53\u524d\u65f6\u95f4:’ ||
\n TO_CHAR(V_START_TIME, ‘YYYY-MM-DD HH24:MI:SS’),
\n V_PROGRAM);
\n COMMIT;<\/p>\n
EXECUTE IMMEDIATE C1.SPLIT_SQL;<\/p>\n
UPDATE SPLIT_TABLE_RSLT TET
\n SET TET.APP_DATE = SYSDATE
\n WHERE TET.TABLE_NAME = C1.TABLE_NAME
\n AND TET.PARTITION_NAME = C1.PARTITION_NAME;
\n SAVE_ERROR_INFO(SQLCODE,
\n ‘\u8868:’ || C1.TABLE_NAME || ‘,\u5206\u533a\u540d:’ || C1.PARTITION_NAME ||
\n ‘\u7edf\u8ba1\u5b8c\u6210,\u8017\u65f6’ ||
\n TO_CHAR(ROUND(TO_NUMBER(SYSDATE – V_START_TIME) * 1440 * 60,
\n 1),
\n ‘fm9999990.0’) || ‘\u79d2\uff01’,
\n V_PROGRAM);
\n COMMIT;<\/p>\n
END LOOP;
\nEXCEPTION
\n WHEN OTHERS THEN
\n ROLLBACK;
\n SAVE_ERROR_INFO(SQLCODE,
\n ‘\u8868:’ || V_TABLE_NAME1 || ‘,\u5206\u533a\u540d:’ || V_PARTITION_NAME ||
\n ‘,\u9519\u8bef\u4fe1\u606f:’ || SQLERRM,
\n V_PROGRAM);
\n COMMIT;<\/p>\n
END;
\n[\/php]<\/p>\n","protected":false},"excerpt":{"rendered":"
[php] \/*\u4e34\u65f6\u8f6c\u6362\u8868*\/ create table S …<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[4],"tags":[],"class_list":["post-43","post","type-post","status-publish","format-standard","hentry","category-4"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p7zRbQ-H","_links":{"self":[{"href":"https:\/\/www.lisq.net\/index.php?rest_route=\/wp\/v2\/posts\/43","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.lisq.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.lisq.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.lisq.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.lisq.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=43"}],"version-history":[{"count":3,"href":"https:\/\/www.lisq.net\/index.php?rest_route=\/wp\/v2\/posts\/43\/revisions"}],"predecessor-version":[{"id":48,"href":"https:\/\/www.lisq.net\/index.php?rest_route=\/wp\/v2\/posts\/43\/revisions\/48"}],"wp:attachment":[{"href":"https:\/\/www.lisq.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=43"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.lisq.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=43"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.lisq.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=43"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}