当前位置:Gxlcms > 数据库问题 > oracle PLSQL程序造数据笔记

oracle PLSQL程序造数据笔记

时间:2021-07-01 10:21:17 帮助过:50人阅读

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.随机取数据:

  1. <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"> (
  2. </span><span style="color: #008080">2</span> <span style="color: #0000ff">SELECT</span><span style="color: #000000"> ename, job
  3. </span><span style="color: #008080">3</span> <span style="color: #0000ff">FROM</span><span style="color: #000000"> emp
  4. </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()
  5. </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 造数据:

  1. <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>
  2. <span style="color: #008080"> 2</span> <span style="color: #0000ff">select</span> <span style="color: #0000ff">level</span><span style="color: #000000"> L1,
  3. </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,
  4. </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,
  5. </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,
  6. </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,
  7. </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,
  8. </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">,
  9. </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">),
  10. </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">)),
  11. </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">,
  12. </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">),
  13. </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">)))),
  14. </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>
  15. <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,
  16. </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,
  17. </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
  18. </span><span style="color: #008080">18</span> <span style="color: #0000ff">from</span><span style="color: #000000"> dual
  19. </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>
  20. <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   

人气教程排行