oracle PLSQL程序造数据笔记

1.造假数据：

declare
  type t_website_id is table of number(10);
  type t_website_name is table of varchar2(20);
  type t_area_id_tb is table of varchar2(5);
  type t_area_name_tb is table of varchar2(16);
  website_id  t_website_id :=t_website_id(1001,1002,1003,1004,1005,1006,1007,1008,1009,1010);
  website_name  t_website_name :=t_website_name('LOFT','京东','网易','知乎','搜狐','新浪','淘宝','腾讯','百度','人人');
  area_id_tb        t_area_id_tb := t_area_id_tb('55','551','553','559','556','562','564','554','561','557','5581');
  area_name_tb  t_area_name_tb := t_area_name_tb('安徽','合肥','芜湖','黄山','安庆','铜陵','六安','淮南','淮北','宿州','亳州');
  
begin
  delete from TEST_WEBSITE_AREA_H;
  for i in 0..23 loop
     for j in area_id_tb.first .. area_id_tb.last loop
        for k in website_id.FIRST .. website_id.LAST loop        
            insert into TEST_WEBSITE_AREA_H
            (
              time_id,
              area_id,
              area_name,
              website_id,
              website_name,
              fst_screen_duration,
              total_flow,
              click_traffic
            )
            values(
               to_char(sysdate,'yyyyMMdd')||lpad(i,2,'0')||'00',
               area_id_tb(j),
               area_name_tb(j),
               website_id(k),
               website_name(k),
               trunc(dbms_random.value(100, 800)),
               round(dbms_random.value(1024, 9999999),2),
               trunc(dbms_random.value(5000000,99999999))
            );
        end loop;
     end loop;
  end loop;
end;

 2.随机取数据：

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

3.使用connect by 造数据：

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