由于工作需要,需要导入数据到生产机上去,生产数据有一百多条,涉及了好几张表,手动导入肯定是行不通的,数据量大且很容易出问题的,现自己模拟导入数据到生产机。以下详细步骤。
生产只给了一个excel表格,上面列了一些关键的信息,因此这个excel上面的信息需要仔细分析下,这里面肯定有突破口。我这里也参照这个excel自己模拟了下,现假设:选修课漏掉了三条选修记录要补上,内容如下:
学号 姓名 选修课程 成绩(新) 0151714104 张蓝 Java Web 89 0151734101 王丽 J2SE 78 0151724101 刘玉 JavaScript 82 |
前言:
手头的数据表有以下几张,分别是temp_class、temp_student、temp_course、temp_score、temp_teacher、temp_option_course6张表
正文:
分析表结构及表之间关系
整体思路:
将excel数据导入到temp_data里面去,然后写存储过程遍历操作
Procedure 新增记录 tmep_score和 temp_option_course
有两个条件:班级号
张蓝 -->stu_code
选修课 -->cou_code
关键:根据学生姓名找到stu_code和cou_code insert操作 temp_score
再找到teach_code insert操作 temp_option_course
代码附录:
表结构和表数据如下:
1.temp_class (班级表)
create table scott.TEMP_CLASS ( class_code NUMBER not null, class_no VARCHAR2(30), class_name VARCHAR2(50), class_describe VARCHAR2(200), class_hornor VARCHAR2(200), class_plan VARCHAR2(200), stu_code NUMBER, stu_name NVARCHAR2(30) ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K next 8K minextents 1 maxextents unlimited ); comment on column scott.TEMP_CLASS.class_code is '主键'; comment on column scott.TEMP_CLASS.class_no is '班级号'; comment on column scott.TEMP_CLASS.class_name is '班级名称'; comment on column scott.TEMP_CLASS.class_describe is '班级描述'; comment on column scott.TEMP_CLASS.class_hornor is '班级荣誉'; comment on column scott.TEMP_CLASS.class_plan is '班级计划'; comment on column scott.TEMP_CLASS.stu_code is '关联temp_student主键stu_code'; comment on column scott.TEMP_CLASS.stu_name is '学生姓名'; alter table scott.TEMP_CLASS add primary key (CLASS_CODE) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited );
insert into temp_class (CLASS_CODE, CLASS_NO, CLASS_NAME, CLASS_DESCRIBE, CLASS_HORNOR, CLASS_PLAN, STU_CODE, STU_NAME) values (1, '17141', '计算机科学技术', '', '', '', 1, '张三');
insert into temp_class (CLASS_CODE, CLASS_NO, CLASS_NAME, CLASS_DESCRIBE, CLASS_HORNOR, CLASS_PLAN, STU_CODE, STU_NAME) values (2, '17142', '计算机科学技术', '', '', '', 2, '李四');
insert into temp_class (CLASS_CODE, CLASS_NO, CLASS_NAME, CLASS_DESCRIBE, CLASS_HORNOR, CLASS_PLAN, STU_CODE, STU_NAME) values (9, '17141', '计算机科学技术', '', '', '', 3, '吴鹏');
insert into temp_class (CLASS_CODE, CLASS_NO, CLASS_NAME, CLASS_DESCRIBE, CLASS_HORNOR, CLASS_PLAN, STU_CODE, STU_NAME) values (10, '17142', '计算机科学技术', '', '', '', 4, '琴沁');
insert into temp_class (CLASS_CODE, CLASS_NO, CLASS_NAME, CLASS_DESCRIBE, CLASS_HORNOR, CLASS_PLAN, STU_CODE, STU_NAME) values (11, '17142', '计算机科学技术', '', '', '', 5, '赵丽盈');
insert into temp_class (CLASS_CODE, CLASS_NO, CLASS_NAME, CLASS_DESCRIBE, CLASS_HORNOR, CLASS_PLAN, STU_CODE, STU_NAME) values (12, '17142', '计算机科学技术', '', '', '', 6, '李波');
insert into temp_class (CLASS_CODE, CLASS_NO, CLASS_NAME, CLASS_DESCRIBE, CLASS_HORNOR, CLASS_PLAN, STU_CODE, STU_NAME) values (13, '17241', '教育技术学', '', '', '', 7, '刘玉');
insert into temp_class (CLASS_CODE, CLASS_NO, CLASS_NAME, CLASS_DESCRIBE, CLASS_HORNOR, CLASS_PLAN, STU_CODE, STU_NAME) values (14, '17342', '软件工程', '', '', '', 8, '萧蓉');
insert into temp_class (CLASS_CODE, CLASS_NO, CLASS_NAME, CLASS_DESCRIBE, CLASS_HORNOR, CLASS_PLAN, STU_CODE, STU_NAME) values (15, '17142', '计算机科学技术', '', '', '', 9, '陈萧晓');
insert into temp_class (CLASS_CODE, CLASS_NO, CLASS_NAME, CLASS_DESCRIBE, CLASS_HORNOR, CLASS_PLAN, STU_CODE, STU_NAME) values (16, '17242', '教育技术学', '', '', '', 10, '陈美');
insert into temp_class (CLASS_CODE, CLASS_NO, CLASS_NAME, CLASS_DESCRIBE, CLASS_HORNOR, CLASS_PLAN, STU_CODE, STU_NAME) values (17, '17141', '计算机科学技术', '', '', '', 11, '张蓝');
insert into temp_class (CLASS_CODE, CLASS_NO, CLASS_NAME, CLASS_DESCRIBE, CLASS_HORNOR, CLASS_PLAN, STU_CODE, STU_NAME) values (18, '17341', '软件工程', '', '', '', 12, '王丽');
|
2.temp_course (课程表)
create table scott.TEMP_COURSE ( cou_code NUMBER not null, cou_no VARCHAR2(50), cou_name VARCHAR2(50), teach_code VARCHAR2(30) ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K next 8K minextents 1 maxextents unlimited ); comment on column scott.TEMP_COURSE.cou_code is '主键'; comment on column scott.TEMP_COURSE.cou_no is '课程号'; comment on column scott.TEMP_COURSE.cou_name is '课程名称'; comment on column scott.TEMP_COURSE.teach_code is '关联temp_teacher主键teach_code'; alter table scott.TEMP_COURSE add primary key (COU_CODE) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited );
insert into TEMP_course (COU_CODE, COU_NO, COU_NAME, TEACH_CODE) values (1, 'XK2016090002', 'J2SE', '2');
insert into TEMP_course (COU_CODE, COU_NO, COU_NAME, TEACH_CODE) values (2, 'XK2015090003', 'Java Web', '1');
insert into TEMP_course (COU_CODE, COU_NO, COU_NAME, TEACH_CODE) values (3, 'XK2016010001', 'SSH', '1');
insert into TEMP_course (COU_CODE, COU_NO, COU_NAME, TEACH_CODE) values (4, 'XK2015010001', 'Oracle', '5');
insert into TEMP_course (COU_CODE, COU_NO, COU_NAME, TEACH_CODE) values (5, 'XK2015090004', 'SQL SERVER 2005', '2');
insert into TEMP_course (COU_CODE, COU_NO, COU_NAME, TEACH_CODE) values (6, 'XK2015010002', 'C#', '3');
insert into TEMP_course (COU_CODE, COU_NO, COU_NAME, TEACH_CODE) values (7, 'XK2014090002', 'JavaScript', '4');
insert into TEMP_course (COU_CODE, COU_NO, COU_NAME, TEACH_CODE) values (8, 'XK2016090003', 'DIV+CSS', '4');
insert into TEMP_course (COU_CODE, COU_NO, COU_NAME, TEACH_CODE) values (9, 'XK2015090005', 'PHP', '3');
insert into TEMP_course (COU_CODE, COU_NO, COU_NAME, TEACH_CODE) values (10, 'Xk2014090001', 'EJB3.0', '2');
|
temp_data (临时数据表)
create table scott.TEMP_DATA ( stu_no VARCHAR2(50), stu_name VARCHAR2(30), cou_name VARCHAR2(50), score NUMBER ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K next 8K minextents 1 maxextents unlimited ); comment on column scott.TEMP_DATA.class_no is '班级号'; comment on column scott.TEMP_DATA.stu_name is '学生姓名'; comment on column scott.TEMP_DATA.cou_name is '课程名称'; comment on column scott.TEMP_DATA.score is '成绩';
insert into temp_data (STU_NO, STU_NAME, COU_NAME, SCORE) values (' 0151714104 ', '张蓝', 'Java Web', 89);
insert into temp_data (STU_NO, STU_NAME, COU_NAME, SCORE) values (' 0151724101 ', '刘玉', 'JavaScript', 82);
insert into temp_data (STU_NO, STU_NAME, COU_NAME, SCORE) values (' 0151734101 ', '王丽', 'J2SE', 78);
|
Temp_option_course (选修表)
create table scott.TEMP_OPTION_COURSE ( opt_code NUMBER not null, stu_code NUMBER, stu_no VARCHAR2(50), stu_name VARCHAR2(30), class_code NUMBER, class_no VARCHAR2(50), class_name VARCHAR2(50), cou_code NUMBER, cou_no VARCHAR2(50), cou_name VARCHAR2(50), score NUMBER, teach_code NUMBER, teach_no VARCHAR2(50), teach_name VARCHAR2(30) ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K next 8K minextents 1 maxextents unlimited ); comment on column scott.TEMP_OPTION_COURSE.opt_code is '主键'; comment on column scott.TEMP_OPTION_COURSE.stu_code is '关联temp_student主键stu_code'; comment on column scott.TEMP_OPTION_COURSE.stu_no is '学号'; comment on column scott.TEMP_OPTION_COURSE.stu_name is '学生姓名'; comment on column scott.TEMP_OPTION_COURSE.class_code is '关联temp_class主键class_code'; comment on column scott.TEMP_OPTION_COURSE.class_no is '班级号'; comment on column scott.TEMP_OPTION_COURSE.class_name is '班级名称'; comment on column scott.TEMP_OPTION_COURSE.cou_code is '关联temp_course主键cou_code'; comment on column scott.TEMP_OPTION_COURSE.cou_no is '课程号'; comment on column scott.TEMP_OPTION_COURSE.cou_name is '课程名称'; comment on column scott.TEMP_OPTION_COURSE.score is '成绩'; comment on column scott.TEMP_OPTION_COURSE.teach_code is '关联temp_teacher主键teach_code'; comment on column scott.TEMP_OPTION_COURSE.teach_no is '教师号'; comment on column scott.TEMP_OPTION_COURSE.teach_name is '教师姓名'; alter table scott.TEMP_OPTION_COURSE add primary key (OPT_CODE) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited );
--新增记录 insert into temp_option_course (OPT_CODE, STU_CODE, STU_NO, STU_NAME, CLASS_CODE, CLASS_NO, CLASS_NAME, COU_CODE, COU_NO, COU_NAME, SCORE, TEACH_CODE, TEACH_NO, TEACH_NAME) values (14, 11, '0151714104', '张蓝', 17, '17141', '计算机科学技术', 2, 'XK2015090003', 'Java Web', 89, 1, 'JX20150901001', '刘阳');
insert into temp_option_course (OPT_CODE, STU_CODE, STU_NO, STU_NAME, CLASS_CODE, CLASS_NO, CLASS_NAME, COU_CODE, COU_NO, COU_NAME, SCORE, TEACH_CODE, TEACH_NO, TEACH_NAME) values (15, 7, '0151724101', '刘玉', 13, '17241', '教育技术学', 7, 'XK2014090002', 'JavaScript', 82, 4, 'JX20160316001', '赵雅芝');
insert into temp_option_course (OPT_CODE, STU_CODE, STU_NO, STU_NAME, CLASS_CODE, CLASS_NO, CLASS_NAME, COU_CODE, COU_NO, COU_NAME, SCORE, TEACH_CODE, TEACH_NO, TEACH_NAME) values (16, 12, '0151734101', '王丽', 18, '17341', '软件工程', 1, 'XK2016090002', 'J2SE', 78, 2, 'JX20140308001', '谌燕');
|
Temp_score (成绩表)
create table scott.TEMP_SCORE ( sco_code NUMBER not null, stu_code NUMBER, cou_code NUMBER, score NUMBER(5) ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K next 8K minextents 1 maxextents unlimited ); comment on column scott.TEMP_SCORE.sco_code is '主键'; comment on column scott.TEMP_SCORE.stu_code is '关联temp_student主键stu_code'; comment on column scott.TEMP_SCORE.cou_code is '关联temp_course主键cou_code'; comment on column scott.TEMP_SCORE.score is '成绩'; alter table scott.TEMP_SCORE add primary key (SCO_CODE) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited );
insert into temp_score (SCO_CODE, STU_CODE, COU_CODE, SCORE) values (1, 2, 8, 79);
insert into temp_score (SCO_CODE, STU_CODE, COU_CODE, SCORE) values (2, 3, 2, 82);
insert into temp_score (SCO_CODE, STU_CODE, COU_CODE, SCORE) values (3, 1, 5, 61);
insert into temp_score (SCO_CODE, STU_CODE, COU_CODE, SCORE) values (4, 6, 6, 83);
insert into temp_score (SCO_CODE, STU_CODE, COU_CODE, SCORE) values (5, 9, 3, 73);
insert into temp_score (SCO_CODE, STU_CODE, COU_CODE, SCORE) values (6, 8, 4, 82);
insert into temp_score (SCO_CODE, STU_CODE, COU_CODE, SCORE) values (7, 1, 3, 59);
insert into temp_score (SCO_CODE, STU_CODE, COU_CODE, SCORE) values (8, 7, 8, 79);
insert into temp_score (SCO_CODE, STU_CODE, COU_CODE, SCORE) values (9, 11, 6, 61);
insert into temp_score (SCO_CODE, STU_CODE, COU_CODE, SCORE) values (10, 9, 5, 79);
insert into temp_score (SCO_CODE, STU_CODE, COU_CODE, SCORE) values (11, 8, 1, 82);
insert into temp_score (SCO_CODE, STU_CODE, COU_CODE, SCORE) values (12, 6, 2, 57);
insert into temp_score (SCO_CODE, STU_CODE, COU_CODE, SCORE) values (13, 5, 4, 49);
insert into temp_score (SCO_CODE, STU_CODE, COU_CODE, SCORE) values (14, 7, 9, 79);
insert into temp_score (SCO_CODE, STU_CODE, COU_CODE, SCORE) values (15, 3, 1, 54);
insert into temp_score (SCO_CODE, STU_CODE, COU_CODE, SCORE) values (16, null, null, 57);
insert into temp_score (SCO_CODE, STU_CODE, COU_CODE, SCORE) values (17, 2, 5, 52);
insert into temp_score (SCO_CODE, STU_CODE, COU_CODE, SCORE) values (18, 12, 3, 55); --新增记录 insert into temp_score (SCO_CODE, STU_CODE, COU_CODE, SCORE) values (37, 11, 2, 89);
insert into temp_score (SCO_CODE, STU_CODE, COU_CODE, SCORE) values (38, 7, 7, 82);
insert into temp_score (SCO_CODE, STU_CODE, COU_CODE, SCORE) values (39, 12, 1, 78);
|
TEMP_STUDENT (学生表)
create table scott.TEMP_STUDENT ( stu_code NUMBER not null, stu_no VARCHAR2(30), zh_name VARCHAR2(30), sex VARCHAR2(4), age NUMBER(3), school VARCHAR2(100) ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K next 8K minextents 1 maxextents unlimited ); comment on column scott.TEMP_STUDENT.stu_code is '主键'; comment on column scott.TEMP_STUDENT.stu_no is '学号'; comment on column scott.TEMP_STUDENT.zh_name is '姓名'; comment on column scott.TEMP_STUDENT.sex is '性别'; comment on column scott.TEMP_STUDENT.age is '年龄'; comment on column scott.TEMP_STUDENT.school is '学校'; alter table scott.TEMP_STUDENT add primary key (STU_CODE) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited );
insert into temp_student (STU_CODE, STU_NO, ZH_NAME, SEX, AGE, SCHOOL) values (1, '0151714101', '张三', '男', 23, '北京大学');
insert into temp_student (STU_CODE, STU_NO, ZH_NAME, SEX, AGE, SCHOOL) values (2, '0151714201', '李四', '男', 23, '北京大学');
insert into temp_student (STU_CODE, STU_NO, ZH_NAME, SEX, AGE, SCHOOL) values (3, '0151714102', '吴鹏', '男', 25, '北京大学');
insert into temp_student (STU_CODE, STU_NO, ZH_NAME, SEX, AGE, SCHOOL) values (4, '0151714202', '琴沁', '女', 20, '北京大学');
insert into temp_student (STU_CODE, STU_NO, ZH_NAME, SEX, AGE, SCHOOL) values (5, '0151714203', '赵丽盈', '女', 20, '北京大学');
insert into temp_student (STU_CODE, STU_NO, ZH_NAME, SEX, AGE, SCHOOL) values (6, '0151714204', '李波', '男', 21, '北京大学');
insert into temp_student (STU_CODE, STU_NO, ZH_NAME, SEX, AGE, SCHOOL) values (7, '0151724101', '刘玉', '男', 21, '北京大学');
insert into temp_student (STU_CODE, STU_NO, ZH_NAME, SEX, AGE, SCHOOL) values (8, '0151734201', '萧蓉', '女', 21, '北京大学');
insert into temp_student (STU_CODE, STU_NO, ZH_NAME, SEX, AGE, SCHOOL) values (9, '0151714103', '陈萧晓', '女', 23, '北京大学');
insert into temp_student (STU_CODE, STU_NO, ZH_NAME, SEX, AGE, SCHOOL) values (10, '0151724202', '陈美', '女', 22, '北京大学');
insert into temp_student (STU_CODE, STU_NO, ZH_NAME, SEX, AGE, SCHOOL) values (11, '0151714104', '张蓝', '女', 24, '北京大学');
insert into temp_student (STU_CODE, STU_NO, ZH_NAME, SEX, AGE, SCHOOL) values (12, '0151734101', '王丽', '女', 26, '北京大学');
|
Temp_teacher (教师表)
create table scott.TEMP_TEACHER ( teach_code NUMBER, teach_no VARCHAR2(30), teach_name VARCHAR2(30), sex VARCHAR2(4), age NUMBER(3), teach_time NUMBER(3) ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K next 8K minextents 1 maxextents unlimited ); comment on column scott.TEMP_TEACHER.teach_code is '主键'; comment on column scott.TEMP_TEACHER.teach_no is '教师号'; comment on column scott.TEMP_TEACHER.teach_name is '教师姓名'; comment on column scott.TEMP_TEACHER.sex is '性别'; comment on column scott.TEMP_TEACHER.age is '年龄'; comment on column scott.TEMP_TEACHER.teach_time is '教龄';
insert into temp_teacher (TEACH_CODE, TEACH_NO, TEACH_NAME, SEX, AGE, TEACH_TIME) values (1, 'JX20150901001', '刘阳', '男', 26, 2);
insert into temp_teacher (TEACH_CODE, TEACH_NO, TEACH_NAME, SEX, AGE, TEACH_TIME) values (2, 'JX20140308001', '谌燕', '女', 29, 6);
insert into temp_teacher (TEACH_CODE, TEACH_NO, TEACH_NAME, SEX, AGE, TEACH_TIME) values (3, 'JX20150509002', '胡明星', '女', 27, 4);
insert into temp_teacher (TEACH_CODE, TEACH_NO, TEACH_NAME, SEX, AGE, TEACH_TIME) values (4, 'JX20160316001', '赵雅芝', '女', 28, 1);
insert into temp_teacher (TEACH_CODE, TEACH_NO, TEACH_NAME, SEX, AGE, TEACH_TIME) values (5, 'JX20161020002', '蒋干', '男', 32, 6);
|
几个表的主键seqence
create sequence scott.SEQ_CLASS_CODE minvalue 1 maxvalue 9999 start with 21 increment by 1 cache 20;
create sequence scott.SEQ_COU_CODE minvalue 1 maxvalue 9999 start with 21 increment by 1 cache 20;
create sequence scott.SEQ_OPT_CODE minvalue 1 maxvalue 9999 start with 21 increment by 1 cache 20;
create sequence scott.SEQ_SCO_CODE minvalue 1 maxvalue 9999 start with 41 increment by 1 cache 20;
create sequence scott.SEQ_STU_CODE minvalue 1 maxvalue 9999 start with 21 increment by 1 cache 20;
create sequence scott.SEQ_TEACH_CODE minvalue 1 maxvalue 9999 start with 21 increment by 1 cache 20; |
Procedure temp_OC_init_content (操作数据)
CREATEORREPLACEProcedure temp_OC_init_contentAs v_num Number; Type temp_datIsRefCursor; t_data temp_dat; up_data temp_data%Rowtype; t_stu temp_student%Rowtype; t_cou temp_course%Rowtype ; t_teach temp_teacher%Rowtype; t_class temp_class%Rowtype; Begin Open t_dataForSelect *From temp_data td ; Loop Fetch t_dataInto up_data; ExitWhen t_data%Notfound; SelectCount(*)Into v_numFrom temp_student tsWhere ts.stu_no=up_data.stu_no; If v_num=0ThenExit; Else Select ts.*Into t_stuFrom temp_student tsWhere ts.stu_no=up_data.stu_no; Select tc.*Into t_couFrom temp_course tc Where tc.cou_name=up_data.cou_name; Select tt.*Into t_teachFrom temp_teacher ttWhere tt.teach_code=t_cou.teach_code; SelectCount(*)Into v_numfrom temp_score tcWhere tc.stu_code=t_stu.stu_codeAnd tc.cou_code=t_cou.cou_code; If v_num=1Then DeleteFrom temp_score tcWhere tc.stu_code=t_stu.stu_codeAnd tc.cou_code=t_cou.cou_code; Commit; Else InsertInto temp_score te(te.sco_code,te.stu_code,te.cou_code,te.score)Select seq_sco_code.nextval,t_stu.stu_code,t_cou.cou_code,up_data.scoreFrom dual; Commit; EndIf; SelectCount(*)Into v_numFrom temp_class tclWhere tcl.stu_code=t_stu.stu_code ; If v_num =0ThenExit; Else Select tcl.*Into t_classFrom temp_class tclWhere tcl.stu_code=t_stu.stu_code ; EndIf; SelectCount(*)Into v_numFrom temp_option_course tocWhere toc.stu_code=t_stu.stu_code ; If v_num =1Then DeleteFrom temp_option_course ttWhere tt.stu_code=t_stu.stu_code ; Commit; Else InsertInto temp_option_courseSelect seq_opt_code.nextval, t_stu.stu_code,t_stu.stu_no,t_stu.zh_name, t_class.class_code,t_class.class_no,t_class.class_name, t_cou.cou_code,t_cou.cou_no,t_cou.cou_name,up_data.score, t_teach.teach_code,t_teach.teach_no,t_teach.teach_name from dual; Commit; EndIf; EndIf; EndLoop; End temp_OC_init_content; |
测试存储过程即可将数据刷到temp_score 和表temp_option_course中。