Oracle預存程序大量新增資料 (非序列產生主鍵方法)

• 概要：

現在有兩張表 person 和 department表. 其中person表是子表,department表是父表,由外鍵deptid關聯.

現在需要批量插入資料:

其中部門表每插入一條記錄,該部門就會被插入若干個員工。

部門表插入的數量，和該部門一次性插入的員工數量由預存程序參數指定.

 

• 建表語句

-- 建立person表create   table PERSON (       PID INTEGER PRIMARY KEY,       PNAME VARCHAR2(10) NOT NULL )TABLESPACE CICI;-- 建立 departmentcreate table DEPARTMENT (       DEPID INTEGER PRIMARY KEY,       DEPTNAME VARCHAR2(10) NOT NULL )TABLESPACE CICI;-- 添加欄位alter table PERSON add DEPID INTEGER ;-- 添加外鍵約束 person -->department--              deptid -- >deptidALTER TABLE PERSONADD CONSTRAINT fk_PERSON_DEPTFOREIGN 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 ;