Batch add data in Oracle stored procedures (non-sequential primary key generation method)

• Summary:

There are now two tables: person and department. The person table is a sub-table, and the department table is a parent table, which is associated with the foreign key deptid.

Now you need to insert data in batches:

Each time a record is inserted into the department table, several employees are inserted into the department table.

The number of inserted Department tables and the number of employees inserted by the Department at a time are specified by the stored procedure parameters.

 

• Table creation statement

 --  Create a person table  Create     Table  Person (PID  Integer   Primary   Key  , Pname  Varchar2 (10 ) Not   Null  ) Tablespace Cici;  --  Create Department  Create   Table  Department (depid  Integer   Primary   Key  , Deptname  Varchar2 ( 10 )Not   Null  ) Tablespace Cici;  --  Add Field  Alter   Table Person Add Depid Integer  ;  --  Add a foreign key constraint person --> Department  --  Deptid --> deptid  Alter   Table Person  Add   Constraint  Fk_person_dept  Foreign   Key (Depid) References Department (depid );

Stored Procedure for batch insert of Department tables

Run call batch_insert_tablea_dept (3 );Three data entries are added to the Department table. The values of deptid are 1, 2, and 3, respectively.

 Create   Or   Replace   Procedure Batch_insert_tablea_dept (insertno In  Integer ) Is  /*  * Name: batch_insert_tablea_dept * purpose: -- batch insert data to table Department * imput: -- number of batch insert data * 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 there is no data in the table, insert a data entry first.            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  ;  --  Insert data with at least one data entry in the table            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;

• Batch insert employee table Storage Process

Run call batch_insert_tablea_person (2, 1 );

Two data entries are added to the personnel table, where the value of the deptid column is 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 (); rpose: -- batch insert data to table person * imput: -- insertno: Number of batch insert data in_dept_no: the foreign key is used to reference the Department deptid field * 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 there is no data in the table, insert a data entry first.            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  ;  --  Insert data with at least one data entry in the table            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;

• Stored Procedure for batch data insertion

Execute batch_insert_persondept (3, 2 );

The Department table has three more data records. The values of deptid are 1, 2, 3, and Max (deptid) + 1,Max (deptid) + 2,Max (deptid) + 3

The personnel table adds 6 data records, that is, two persons are added for each newly added department.

 Create   Or   Replace   Procedure Batch_insert_persondept (insertdeptno In   Integer  , Insertpersonno  In   Integer ) Is    /* * Name: batch_insert_persondept * pucall batch_insert_persondept (); rpose: -- batch insert data to the table person and table Department * imput: -- insertdeptno: the number of data inserted to the Department table in batches: number of data inserted to the person table in batches * output: -- N/A * Author: -- Cici * createdate: -- 2012, 12, 30 * updatedate: --*************************************** ********************  */  V_pid  Integer  ; V_insert_no  Integer  ; I  Integer  ;  Begin  V_insert_no: =  Insertdeptno;  --  Get deptid from department table    Select   Max (Deptid) Into V_pid From  Department;  If V_pid Is   Not   Null   Then  Batch_insert_tablea_dept (v_insert_no ); --  Insert insertdeptno data to the Department table      --  Insert data to the person table      --  Each newly added deptid inserts insertpersonno data records.      For I In V_pid +   1 ... V_pid +  Insertdeptno loop batch_insert_tablea_person (insertpersonno, I );  End  Loop; --  If the dept table is empty    Else  V_pid:  =   1  ; Batch_insert_tablea_dept (v_insert_no );  --  Insert insertdeptno data to the Department table      --  Insert data to the person table      --  Each newly added deptid is inserted with insertpersonno. The data department number starts from 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;

 

• Batch delete data

Truncate TablePerson;Alter TablePerson distableConstraintFk_person_dept;Truncate TableDepartment;Alter TablePerson enableConstraintFk_person_dept;