[oracle] 生成随机数据

/** 
*  创建序列，用于获得特征数据
*/
create sequence mock_data_seq 
       minvalue 1
       start with 1
       increment by 1
       cache 100;
/**
*  创建数据库表
*/
create table tab_mock_data (
id number,
serno long,
username varchar2(50),
birthdate date,
sex number(1),
amount number(19,6),
tag varchar2(20),
remark varchar2(200),
inputdate timestamp
);
/**
*  创建存储过程
*/
create or replace procedure mockdatagenertator(maxrecords in number :=100) is
i number :=1;
begin
  for i in 1..maxrecords
  loop
   insert into  tab_mock_data
   VALUES(mock_data_seq.NEXTVAL,i,'测试数据',SYSDATE-(i/24 + i/24/3600), mod(i,3),dbms_random.random/10000,'std','备注',current_timestamp);
  end loop;
  commit;
  dbms_output.put_line(maxrecords||' insert done');
end mockdatagenertator;

-- set serveroutput on; 
-- 执行，产生数据量通过参数指定 
call mockdatagenertator(1000);
-- 查询数据
select * from tab_mock_data;

--- 清理数据
drop sequence mock_data_seq;
drop table tab_mock_data;
drop procedure  mockdatagenertator;

declare
  maxtablecount number;
begin
  maxtablecount := 10;
  while maxtablecount > 0 loop
  
    execute immediate '
create table tab_mock_data' || maxtablecount || ' (
        id number,
        serno long,
        username varchar2(50),
        birthdate date,
        sex number(1),
        amount number(19,6),
        tag varchar2(20),
        remark varchar2(200),
        inputdate timestamp
        )';
    maxtablecount := maxtablecount - 1;
  end loop;
end;