【oracle】【demo】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;

/

 

posted on 2018-04-05 18:41  绿Z 阅读( ...) 评论( ...) 编辑 收藏

转载于:https://www.cnblogs.com/greenZ/p/8723752.html