- 概要:
现在有两张表 person 和 department表. 其中person表是子表,department表是父表,由外键deptid关联.
现在需要批量插入数据:
其中部门表每插入一条记录,该部门就会被插入若干个员工。
部门表插入的数量,和该部门一次性插入的员工数量由存储过程参数指定.
- 建表语句
-- 创建person表 create table PERSON ( PID INTEGER PRIMARY KEY, PNAME VARCHAR2(10) NOT NULL )TABLESPACE CICI; -- 创建 department create table DEPARTMENT ( DEPID INTEGER PRIMARY KEY, DEPTNAME VARCHAR2(10) NOT NULL )TABLESPACE CICI; -- 添加字段 alter table PERSON add DEPID INTEGER ; -- 添加外键约束 person -->department -- deptid -- >deptid ALTER TABLE PERSON ADD CONSTRAINT fk_PERSON_DEPT FOREIGN KEY (DEPID) REFERENCES DEPARTMENT (DEPID);
- 批量插入部门表的存储过程
执行 call BATCH_INSERT_TABLEA_DEPT(3);部门表会添加3条数据.deptid 分别为1,2,3
create or replace procedure BATCH_INSERT_TABLEA_DEPT(insertNo in integer) is /* *NAME : BATCH_INSERT_TABLEA_DEPT *PURPOSE : --向表DEPARTMENT批量插入数据 *IMPUT : -- 批量插入数据的数量 *OUTPUT : -- N/A *Author : -- CICI *CreateDate : -- 2012、12、30 *UpdateDate : -- ************************************************************/ V_DEPT_ID INTEGER; V_INSERT_NO INT; I INTEGER; BEGIN SELECT MAX(DEPTID) into V_DEPT_ID FROM DEPARTMENT; --如果表中没有数据 则先插入一条数据 IF V_DEPT_ID IS NULL THEN INSERT INTO DEPARTMENT(DEPTID,DEPTNAME) VALUES(1,'DEPT_'||1); V_DEPT_ID:=1; V_INSERT_NO:=insertNo-1; ELSE V_INSERT_NO:=insertNo; END IF; --表中至少有一条数据的前提之下 插入数据 FOR I IN V_DEPT_ID+1 ..V_DEPT_ID+V_INSERT_NO loop INSERT INTO DEPARTMENT(DEPTID,DEPTNAME) VALUES(I,'DEPT_'||I); end LOOP; COMMIT; end BATCH_INSERT_TABLEA_DEPT;
- 批量插入员工表存储过程
执行 call BATCH_INSERT_TABLEA_PERSON(2,1);
人员表会添加2条数据 ,其中deptid列值都为1
create or replace procedure BATCH_INSERT_TABLEA_PERSON(insertNo in integer,in_dept_no IN INTEGER) is /* *NAME : BATCH_INSERT_TABLEA_DEPT *PUCALL BATCH_INSERT_TABLEA_PERSON(50,1);RPOSE : --向表person批量插入数据 *IMPUT : -- insertNo:批量插入数据的数量 in_dept_no:外键 用来引用department deptid字段 *OUTPUT : -- N/A *Author : -- CICI *CreateDate : -- 2012、12、30 *UpdateDate : -- ************************************************************/ V_DEPT_ID INTEGER; V_PID INTEGER; V_INSERT_NO INT; I INTEGER; BEGIN IF in_dept_no IS NOT NULL THEN V_DEPT_ID:=in_dept_no; END IF; SELECT MAX(PID) into V_PID FROM PERSON; --如果表中没有数据 则先插入一条数据 IF V_PID IS NULL THEN INSERT INTO PERSON(PID,PNAME,DEPTID) VALUES(1,'PNAME_'||1,V_DEPT_ID); V_PID:=1; V_INSERT_NO:=insertNo-1; ELSE V_INSERT_NO:=insertNo; END IF; --表中至少有一条数据的前提之下 插入数据 FOR I IN V_PID+1 ..V_PID+V_INSERT_NO loop INSERT INTO PERSON(PID,PNAME,DEPTID) VALUES(I,'PNAME_'||I,V_DEPT_ID); end LOOP; COMMIT; end BATCH_INSERT_TABLEA_PERSON;
- 批量插入数据的存储过程
执行BATCH_INSERT_PERSONDEPT(3,2);
部门表会多出 3条数据 ,deptid 分别为1,2,3 或者max(deptid)+1,max(deptid)+2,max(deptid)+3
人员表会增加6条数据,即每个新增加的部门添加2个人员.
create or replace procedure BATCH_INSERT_PERSONDEPT(insertdeptNo in integer, insertPersonNo IN INTEGER) is /* *NAME : BATCH_INSERT_PERSONDEPT *PUCALL BATCH_INSERT_PERSONDEPT(50,1);RPOSE : --向表person 和 表 department 批量插入数据 *IMPUT : -- insertdeptNo:批量插入department表的数据的数量 insertPersonNo:批量插入person表的数据的数量 *OUTPUT : -- N/A *Author : -- CICI *CreateDate : -- 2012、12、30 *UpdateDate : -- ************************************************************/ V_PID INTEGER; V_INSERT_NO INTEGER; I INTEGER; BEGIN V_INSERT_NO := insertdeptNo; --从department表 取得DEPTID select max(deptid) into v_pid from department; if v_pid is not null then BATCH_INSERT_TABLEA_DEPT(V_INSERT_NO); -- 向department表插入 insertdeptNo条数据 --向 person表 插入数据 --每个新增加的deptid 都对应插入insertPersonNo 条数据 for I in v_pid + 1 .. v_pid + insertdeptNo loop BATCH_INSERT_TABLEA_PERSON(insertPersonNo, I); end loop; -- 如果dept表为空 else v_pid := 1; BATCH_INSERT_TABLEA_DEPT(V_INSERT_NO); -- 向department表插入 insertdeptNo条数据 --向 person表 插入数据 --每个新增加的deptid 都对应插入insertPersonNo 条数据 部门编号从1开始 for I in v_pid ..v_pid + insertdeptNo - 1 loop BATCH_INSERT_TABLEA_PERSON(insertPersonNo, I); end loop; end if; end BATCH_INSERT_PERSONDEPT;
- 批量删除数据
truncate table person; ALTER TABLE PERSON distable CONSTRAINT FK_PERSON_DEPT ; truncate table department; ALTER TABLE PERSON enable CONSTRAINT FK_PERSON_DEPT ;