学步园

• 概要：

现在有两张表 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 ;