oracle的造数,【oracle】【demo】oracle批量造数据

【oracle】【demo】批量造数据

方式1：使用 dbms_random函数 和 connect by level 执行insert语句多次

示例：

create sequence seq_id

minvalue 1001

start with 1001

nomaxvale

increment by 1

no cycle

cache 30;

/

select seq_id.nextval from dual;

inser into t_student(id,name,age)

select

seq_id.nextval as id,

dbms_random.string('a',8) as name,

dbms_random.value(20,24) as age

from dual

connect by level <= 10; --迭代 执行 sql 10次

drop sequence seq_id;

方式2：创建 procedure 使用循环体

create or replace produce prc_insert_data(vn_id in number)

as

vv_name varchar2(64) default 'Kitty';

vn_age number := 20;

exec_sql varchar2(100);

begin

for i in 1..10

loop

inser into t_student(id,name,age)

values(vn_number+i,vv_name||to_char(i),vn_age+i);

end loop;

exec_sql := 'commit';

execute immediate exec_sql;

end;

/

call prc_insert_data(1000);

drop produce prc_insert_data;

方式3：匿名块

declare

vv_name varchar2(64) default 'Kitty';

vn_age number := 20;

exec_sql varchar2(100);

begin

for i in 1..10

loop

inser into t_student(id,name,age)

values(vn_number+i,vv_name||to_char(i),vn_age+i);

end loop;

exec_sql := 'commit';

execute immediate exec_sql;

end;

/