oracle建表时按天分区的自动生成语句

<span style="color:#0000FF;line-height:1.5 !important;">DECLARE</span><span style="line-height:1.5 !important;"> V_SQL </span><span style="color:#0000FF;line-height:1.5 !important;">VARCHAR2</span>(<span style="color:#800000;font-weight:bold;line-height:1.5 !important;">1000</span><span style="line-height:1.5 !important;">);
  V_SQL_HEAD </span><span style="color:#0000FF;line-height:1.5 !important;">VARCHAR2</span>(<span style="color:#800000;font-weight:bold;line-height:1.5 !important;">100</span><span style="line-height:1.5 !important;">);
  V_SQL_TAIL </span><span style="color:#0000FF;line-height:1.5 !important;">VARCHAR2</span>(<span style="color:#800000;font-weight:bold;line-height:1.5 !important;">10</span><span style="line-height:1.5 !important;">);
  V_DAY </span><span style="color:#0000FF;line-height:1.5 !important;">VARCHAR2</span>(<span style="color:#800000;font-weight:bold;line-height:1.5 !important;">8</span><span style="line-height:1.5 !important;">); </span><span style="color:#0000FF;line-height:1.5 !important;">CURSOR</span> C_CUR <span style="color:#0000FF;line-height:1.5 !important;">IS</span> <span style="color:#0000FF;line-height:1.5 !important;">SELECT</span> TO_CHAR((TO_DATE(<span style="color:#FF0000;line-height:1.5 !important;">‘</span><span style="color:#FF0000;line-height:1.5 !important;">2012-01</span><span style="color:#FF0000;line-height:1.5 !important;">‘</span>, <span style="color:#FF0000;line-height:1.5 !important;">‘</span><span style="color:#FF0000;line-height:1.5 !important;">yyyy-mm</span><span style="color:#FF0000;line-height:1.5 !important;">‘</span>) <span style="color:#808080;line-height:1.5 !important;">+</span> (ROWNUM <span style="color:#808080;line-height:1.5 !important;">-</span> <span style="color:#800000;font-weight:bold;line-height:1.5 !important;">1</span><span style="line-height:1.5 !important;">)), </span><span style="color:#FF0000;line-height:1.5 !important;">‘</span><span style="color:#FF0000;line-height:1.5 !important;">YYYYMMDD</span><span style="color:#FF0000;line-height:1.5 !important;">‘</span><span style="line-height:1.5 !important;">) S_DATE </span><span style="color:#0000FF;line-height:1.5 !important;">FROM</span><span style="line-height:1.5 !important;"> DUAL
    CONNECT </span><span style="color:#0000FF;line-height:1.5 !important;">BY</span> ROWNUM <span style="color:#808080;line-height:1.5 !important;"><=</span> LAST_DAY(TO_DATE(<span style="color:#FF0000;line-height:1.5 !important;">‘</span><span style="color:#FF0000;line-height:1.5 !important;">2012-12</span><span style="color:#FF0000;line-height:1.5 !important;">‘</span>, <span style="color:#FF0000;line-height:1.5 !important;">‘</span><span style="color:#FF0000;line-height:1.5 !important;">yyyy-mm</span><span style="color:#FF0000;line-height:1.5 !important;">‘</span>)) <span style="color:#808080;line-height:1.5 !important;">-</span><span style="line-height:1.5 !important;"> TO_DATE(</span><span style="color:#FF0000;line-height:1.5 !important;">‘</span><span style="color:#FF0000;line-height:1.5 !important;">2012-01</span><span style="color:#FF0000;line-height:1.5 !important;">‘</span>, <span style="color:#FF0000;line-height:1.5 !important;">‘</span><span style="color:#FF0000;line-height:1.5 !important;">yyyy-mm</span><span style="color:#FF0000;line-height:1.5 !important;">‘</span>) <span style="color:#808080;line-height:1.5 !important;">+</span> <span style="color:#800000;font-weight:bold;line-height:1.5 !important;">1</span><span style="line-height:1.5 !important;">; </span><span style="color:#0000FF;line-height:1.5 !important;">BEGIN</span><span style="line-height:1.5 !important;"> DBMS_OUTPUT.ENABLE(</span><span style="color:#800000;font-weight:bold;line-height:1.5 !important;">1000000</span><span style="line-height:1.5 !important;">); </span><span style="color:#0000FF;line-height:1.5 !important;">OPEN</span><span style="line-height:1.5 !important;"> C_CUR;
  V_SQL_HEAD :</span><span style="color:#808080;line-height:1.5 !important;">=</span> <span style="color:#FF0000;line-height:1.5 !important;">‘</span><span style="color:#FF0000;line-height:1.5 !important;">PARTITION BY RANGE(ACCT_DAY)</span><span style="color:#FF0000;line-height:1.5 !important;">‘</span> <span style="color:#808080;line-height:1.5 !important;">||</span> CHR(<span style="color:#800000;font-weight:bold;line-height:1.5 !important;">13</span>) <span style="color:#808080;line-height:1.5 !important;">||</span> <span style="color:#FF0000;line-height:1.5 !important;">‘</span><span style="color:#FF0000;line-height:1.5 !important;">(</span><span style="color:#FF0000;line-height:1.5 !important;">‘</span><span style="line-height:1.5 !important;">;
  V_SQL_TAIL :</span><span style="color:#808080;line-height:1.5 !important;">=</span> CHR(<span style="color:#800000;font-weight:bold;line-height:1.5 !important;">13</span>) <span style="color:#808080;line-height:1.5 !important;">||</span> <span style="color:#FF0000;line-height:1.5 !important;">‘</span><span style="color:#FF0000;line-height:1.5 !important;">)</span><span style="color:#FF0000;line-height:1.5 !important;">‘</span><span style="line-height:1.5 !important;">;
  DBMS_OUTPUT.PUT_LINE(V_SQL_HEAD);
  LOOP </span><span style="color:#0000FF;line-height:1.5 !important;">FETCH</span><span style="line-height:1.5 !important;"> C_CUR </span><span style="color:#0000FF;line-height:1.5 !important;">INTO</span><span style="line-height:1.5 !important;"> V_DAY; </span><span style="color:#0000FF;line-height:1.5 !important;">EXIT</span> <span style="color:#0000FF;line-height:1.5 !important;">WHEN</span> C_CUR<span style="color:#808080;line-height:1.5 !important;">%</span><span style="line-height:1.5 !important;">NOTFOUND;
    V_SQL :</span><span style="color:#808080;line-height:1.5 !important;">=</span> <span style="color:#FF0000;line-height:1.5 !important;">‘</span><span style="color:#FF0000;line-height:1.5 !important;">PARTITION PART_</span><span style="color:#FF0000;line-height:1.5 !important;">‘</span> <span style="color:#808080;line-height:1.5 !important;">||</span> V_DAY <span style="color:#808080;line-height:1.5 !important;">||</span> <span style="color:#FF0000;line-height:1.5 !important;">‘</span> <span style="color:#FF0000;line-height:1.5 !important;">‘</span> <span style="color:#808080;line-height:1.5 !important;">||</span> <span style="color:#FF0000;line-height:1.5 !important;">‘</span><span style="color:#FF0000;line-height:1.5 !important;">VALUES LESS THAN (</span><span style="color:#FF0000;line-height:1.5 !important;">‘‘‘</span> <span style="color:#808080;line-height:1.5 !important;">||</span><span style="line-height:1.5 !important;"> TO_CHAR((TO_DATE(V_DAY, </span><span style="color:#FF0000;line-height:1.5 !important;">‘</span><span style="color:#FF0000;line-height:1.5 !important;">YYYYMMDD</span><span style="color:#FF0000;line-height:1.5 !important;">‘</span>) <span style="color:#808080;line-height:1.5 !important;">+</span> <span style="color:#800000;font-weight:bold;line-height:1.5 !important;">1</span>), <span style="color:#FF0000;line-height:1.5 !important;">‘</span><span style="color:#FF0000;line-height:1.5 !important;">YYYYMMDD</span><span style="color:#FF0000;line-height:1.5 !important;">‘</span>) <span style="color:#808080;line-height:1.5 !important;">||</span> <span style="color:#FF0000;line-height:1.5 !important;">‘‘‘</span><span style="color:#FF0000;line-height:1.5 !important;">)</span><span style="color:#FF0000;line-height:1.5 !important;">‘</span><span style="line-height:1.5 !important;">;
    DBMS_OUTPUT.PUT_LINE(V_SQL); </span><span style="color:#0000FF;line-height:1.5 !important;">END</span><span style="line-height:1.5 !important;"> LOOP;
  DBMS_OUTPUT.PUT_LINE(V_SQL_TAIL); </span><span style="color:#0000FF;line-height:1.5 !important;">CLOSE</span><span style="line-height:1.5 !important;"> C_CUR; </span><span style="color:#0000FF;line-height:1.5 !important;">END</span>;

      该语句执行之后的效果就是生成了2012年一年的分区，不重不漏。具体情况具体修改。勿喷。

oracle建表时按天分区的自动生成语句

标签：