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

1 SELECT * FROM (
2     SELECT ename, job
3         FROM emp
4      ORDER BY DBMS_RANDOM.VALUE()
5   ) WHERE ROWNUM <= 5;

3.使用connect by 造数据：

 1 create table t_test_random as
 2 select level L1,
 3  substr(abs(dbms_random.random), 2, 8) L2,
 4  trunc(dbms_random.value(1, 101)) L3,
 5  (2 * trunc(dbms_random.value(1, 50)) - 1) L4,
 6  dbms_random.string(‘a‘, 10) L5,
 7  dbms_random.string(‘x‘, 10) L6,
 8  to_date(trunc(dbms_random.value(to_number(to_char(to_date(‘2012-10-1‘,
 9  ‘yyyy-mm-dd‘),
10  ‘j‘)),
11  to_number(to_char(to_date(‘2012-10-8‘,
12  ‘yyyy-mm-dd‘),
13  ‘j‘)))),
14  ‘j‘) +
15  dbms_random.value(9,18)/24 L7,
16  sysdate - dbms_random.value(0,30)/24/60 L8,
17  trunc(sysdate) - trunc(dbms_random.value(1,11)) L9
18  from dual
19  where level >= 100
20 connect by level <= 1000000;

 

oracle PLSQL程序造数据笔记

标签：网易   website   weight   name   取数   随机   acl   row   traffic