插入千万测试数据

drop table student;
drop table st_class;
drop table st_score
drop sequence SEQ_st_class;
drop sequence SEQ_student;
drop sequence seq_st_score;

--创建学生表
create table student
(
st_id NUMBER not null primary key,
st_name VARCHAR2(50),
)

--创建班级表
create table st_class
(
class_id NUMBER not null primary key,
st_id number,
class_name VARCHAR2(50)
);

--创建成绩表
create table st_score
(
score_id number not null primary key
st_id number,
subject_name varchar(30),
score float
);

create sequence SEQ_student
minvalue 1
maxvalue 999999999
start with 1
increment by 1
cache 20;

create sequence SEQ_st_class
minvalue 1
maxvalue 999999999
start with 1
increment by 1
cache 20;

create sequence SEQ_st_score
minvalue 1
maxvalue 999999999
start with 1
increment by 1
cache 20;
create or replace procedure P_Insert as
type type_array is TABLE of varchar2(400);
var_firstname type_array := type_array('陈','黄','温','江,','翁','洪','方','龚','汪','钟','关','张','赵','马','林');
var_secondName type_array := type_array('桂','唐','斯','益','杨','金','远','国','宋','辽','羽','飞'
,'云','超','备','侃','拼','莹','基','泽','邓','胡','吴','郑');
var_thirdName type_array := type_array('年','球','理','象','挺','波','忠','新','法','笔','这','为'
,'上','和','人','有','天','鑫','煌','东','仍','夺','淡','艰');
var_name varchar2(100);
var_no varchar2(40);
var_address varchar2(40);
var_county type_array := type_array('鼓楼区',
'晋安区',
'台江区',
'仓山区',
'马尾区');
var_address1 type_array := type_array('鼓楼路',
'晋安路',
'台江路',
'仓山路',
'马尾路');
var_address2 type_array := type_array('鼓楼街',
'晋安街',
'台江街',
'仓山街',
'马尾街');
var_subject type_array := type_array('语文','数学','英语','政治');
BEGIN
for var_i in 1 .. 2000000 loop
--名字
var_name := var_firstname(round(DBMS_RANDOM.VALUE(0,15))) || var_secondName(round(DBMS_RANDOM.VALUE(0,24))) || var_thirdName(round(DBMS_RANDOM.VALUE(0,24)));
var_no := 10000000+var_i;
var_address := var_county(round(DBMS_RANDOM.VALUE(0,4))) || var_address1(round(DBMS_RANDOM.VALUE(0,4))) || var_address2(round(DBMS_RANDOM.VALUE(0,4)));
--插入学生表
insert into student
(st_id, st_name,st_no, ADDRESS)
values
(SEQ_student.NEXTVAL, var_name,var_no,var_address);
--班级表数据
insert into st_class
(CLASS_ID, ST_ID,CLASS_NAME)
values
(seq_st_class.NEXTVAL, SEQ_student.Currval,round(dbms_random.value(1,20))||'班');
--成绩表
for var_j in 0 .. 3 loop
insert into st_score(SCORE_ID,st_id,subject_name,score)
values(seq_st_score.nextval,seq_student.currval,var_subject(var_j),round(dbms_random.value(0,100)));
end loop;
end loop;
commit;
END;
/
begin
-- Call the procedure
p_insert;
end;
/