时间:2021-07-01 10:21:17 帮助过:50人阅读
2.随机取数据:
- <span style="color: #008080">1</span> <span style="color: #0000ff">SELECT</span> <span style="color: #808080">*</span> <span style="color: #0000ff">FROM</span><span style="color: #000000"> (
- </span><span style="color: #008080">2</span> <span style="color: #0000ff">SELECT</span><span style="color: #000000"> ename, job
- </span><span style="color: #008080">3</span> <span style="color: #0000ff">FROM</span><span style="color: #000000"> emp
- </span><span style="color: #008080">4</span> <span style="color: #0000ff">ORDER</span> <span style="color: #0000ff">BY</span><span style="color: #000000"> DBMS_RANDOM.VALUE()
- </span><span style="color: #008080">5</span> ) <span style="color: #0000ff">WHERE</span> ROWNUM <span style="color: #808080"><=</span> <span style="color: #800000; font-weight: bold">5</span>;
3.使用connect by 造数据:
- <span style="color: #008080"> 1</span> <span style="color: #0000ff">create</span> <span style="color: #0000ff">table</span> t_test_random <span style="color: #0000ff">as</span>
- <span style="color: #008080"> 2</span> <span style="color: #0000ff">select</span> <span style="color: #0000ff">level</span><span style="color: #000000"> L1,
- </span><span style="color: #008080"> 3</span> substr(<span style="color: #ff00ff">abs</span>(dbms_random.random), <span style="color: #800000; font-weight: bold">2</span>, <span style="color: #800000; font-weight: bold">8</span><span style="color: #000000">) L2,
- </span><span style="color: #008080"> 4</span> trunc(dbms_random.value(<span style="color: #800000; font-weight: bold">1</span>, <span style="color: #800000; font-weight: bold">101</span><span style="color: #000000">)) L3,
- </span><span style="color: #008080"> 5</span> (<span style="color: #800000; font-weight: bold">2</span> <span style="color: #808080">*</span> trunc(dbms_random.value(<span style="color: #800000; font-weight: bold">1</span>, <span style="color: #800000; font-weight: bold">50</span>)) <span style="color: #808080">-</span> <span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">) L4,
- </span><span style="color: #008080"> 6</span> dbms_random.string(<span style="color: #ff0000">‘</span><span style="color: #ff0000">a</span><span style="color: #ff0000">‘</span>, <span style="color: #800000; font-weight: bold">10</span><span style="color: #000000">) L5,
- </span><span style="color: #008080"> 7</span> dbms_random.string(<span style="color: #ff0000">‘</span><span style="color: #ff0000">x</span><span style="color: #ff0000">‘</span>, <span style="color: #800000; font-weight: bold">10</span><span style="color: #000000">) L6,
- </span><span style="color: #008080"> 8</span> to_date(trunc(dbms_random.value(to_number(to_char(to_date(<span style="color: #ff0000">‘</span><span style="color: #ff0000">2012-10-1</span><span style="color: #ff0000">‘</span><span style="color: #000000">,
- </span><span style="color: #008080"> 9</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">yyyy-mm-dd</span><span style="color: #ff0000">‘</span><span style="color: #000000">),
- </span><span style="color: #008080">10</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">j</span><span style="color: #ff0000">‘</span><span style="color: #000000">)),
- </span><span style="color: #008080">11</span> to_number(to_char(to_date(<span style="color: #ff0000">‘</span><span style="color: #ff0000">2012-10-8</span><span style="color: #ff0000">‘</span><span style="color: #000000">,
- </span><span style="color: #008080">12</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">yyyy-mm-dd</span><span style="color: #ff0000">‘</span><span style="color: #000000">),
- </span><span style="color: #008080">13</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">j</span><span style="color: #ff0000">‘</span><span style="color: #000000">)))),
- </span><span style="color: #008080">14</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">j</span><span style="color: #ff0000">‘</span>) <span style="color: #808080">+</span>
- <span style="color: #008080">15</span> dbms_random.value(<span style="color: #800000; font-weight: bold">9</span>,<span style="color: #800000; font-weight: bold">18</span>)<span style="color: #808080">/</span><span style="color: #800000; font-weight: bold">24</span><span style="color: #000000"> L7,
- </span><span style="color: #008080">16</span> sysdate <span style="color: #808080">-</span> dbms_random.value(<span style="color: #800000; font-weight: bold">0</span>,<span style="color: #800000; font-weight: bold">30</span>)<span style="color: #808080">/</span><span style="color: #800000; font-weight: bold">24</span><span style="color: #808080">/</span><span style="color: #800000; font-weight: bold">60</span><span style="color: #000000"> L8,
- </span><span style="color: #008080">17</span> trunc(sysdate) <span style="color: #808080">-</span> trunc(dbms_random.value(<span style="color: #800000; font-weight: bold">1</span>,<span style="color: #800000; font-weight: bold">11</span><span style="color: #000000">)) L9
- </span><span style="color: #008080">18</span> <span style="color: #0000ff">from</span><span style="color: #000000"> dual
- </span><span style="color: #008080">19</span> <span style="color: #0000ff">where</span> <span style="color: #0000ff">level</span> <span style="color: #808080">>=</span> <span style="color: #800000; font-weight: bold">100</span>
- <span style="color: #008080">20</span> connect <span style="color: #0000ff">by</span> <span style="color: #0000ff">level</span> <span style="color: #808080"><=</span> <span style="color: #800000; font-weight: bold">1000000</span>;
oracle PLSQL程序造数据笔记
标签:网易 website weight name 取数 随机 acl row traffic