oracle PLSQL程序造数据笔记

declare 2 type t_website_id is table of number(10); 3 type t_website_name is table of varchar2(20); 4 type t_area_id_tb is table of varchar2(5); 5 type t_area_name_tb is table of varchar2(16); 6 website_id t_website_id :=t_website_id(1001,1002,1003,1004,1005,1006,1007,1008,1009,1010); 7 website_name t_website_name :=t_website_name(‘LOFT‘,‘京东‘,‘网易‘,‘知乎‘,‘搜狐‘,‘新浪‘,‘淘宝‘,‘腾讯‘,‘百度‘,‘人人‘); 8 area_id_tb t_area_id_tb := t_area_id_tb(‘55‘,‘551‘,‘553‘,‘559‘,‘556‘,‘562‘,‘564‘,‘554‘,‘561‘,‘557‘,‘5581‘); 9 area_name_tb t_area_name_tb := t_area_name_tb(‘安徽‘,‘合肥‘,‘芜湖‘,‘黄山‘,‘安庆‘,‘铜陵‘,‘六安‘,‘淮南‘,‘淮北‘,‘宿州‘,‘亳州‘); 10 11 begin 12 delete from TEST_WEBSITE_AREA_H; 13 for i in 0..23 loop 14 for j in area_id_tb.first .. area_id_tb.last loop 15 for k in website_id.FIRST .. website_id.LAST loop 16 insert into TEST_WEBSITE_AREA_H 17 ( 18 time_id, 19 area_id, 20 area_name, 21 website_id, 22 website_name, 23 fst_screen_duration, 24 total_flow, 25 click_traffic 26 ) 27 values( 28 to_char(sysdate,‘yyyyMMdd‘)||lpad(i,2,‘0‘)||‘00‘, 29 area_id_tb(j), 30 area_name_tb(j), 31 website_id(k), 32 website_name(k), 33 trunc(dbms_random.value(100, 800)), 34 round(dbms_random.value(1024, 9999999),2), 35 trunc(dbms_random.value(5000000,99999999)) 36 ); 37 end loop; 38 end loop; 39 end loop; 40 end;

 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