快捷導(dǎo)航

Oracle往某表批量插入記錄的幾種實(shí)現(xiàn)方法

更新時(shí)間：2023年07月22日 16:28:58 作者：客逍京北岸

這篇文章主要給大家介紹了關(guān)于Oracle往某表批量插入記錄的幾種實(shí)現(xiàn)方法,Oracle批量插入語句與其他數(shù)據(jù)庫不同,文中通過代碼實(shí)例介紹的非常詳細(xì),需要的朋友可以參考下

1. 使用INSERT INTO SELECT語句

INSERT INTO SELECT語句允許我們從另一個(gè)表或子查詢中選擇數(shù)據(jù)，并將其插入到指定表中。

例如，我們有一個(gè)名為EMPLOYEE_TEMP的臨時(shí)表，其中已經(jīng)存儲(chǔ)了3條記錄，內(nèi)容與上述相同。那么可以使用如下語句將這些數(shù)據(jù)批量插入到EMPLOYEE表中：

INSERT INTO EMPLOYEE (ID, NAME, AGE)
SELECT ID, NAME, AGE FROM EMPLOYEE_TEMP;

或者將需要插入的記錄先存儲(chǔ)在一個(gè)臨時(shí)表中，然后再將臨時(shí)表中的記錄插入到目標(biāo)表中。例如：

CREATE TABLE temp_table AS SELECT * FROM source_table WHERE condition;
INSERT INTO target_table SELECT * FROM temp_table;

2. 使用INSERT ALL語句

INSERT ALL語句可以向一個(gè)或多個(gè)表中插入多行數(shù)據(jù)，它允許一次性執(zhí)行多個(gè)INSERT INTO語句，可以有效地批量插入數(shù)據(jù)。一次性插入多條記錄。例如：

INSERT ALL
INTO target_table (column1, column2) VALUES (value1, value2)
INTO target_table (column1, column2) VALUES (value3, value4)
SELECT * FROM dual;
-------------------------------------------------展示：
INSERT ALL 
INTO EMPLOYEE (ID, NAME, AGE) VALUES (1, 'John', 25)
INTO EMPLOYEE (ID, NAME, AGE) VALUES (2, 'Mary', 30)
INTO EMPLOYEE (ID, NAME, AGE) VALUES (3, 'Michael', 35)
SELECT 1 FROM DUAL;

3. 使用INSERT INTO VALUES語句

例如：

INSERT INTO target_table (column1, column2) 
       VALUES (value1, value2), (value3, value4);

4. 使用FORALL語句

例如：

DECLARE
  TYPE t_record IS RECORD (column1 datatype, column2 datatype);
  TYPE t_table IS TABLE OF t_record;
  v_data t_table;
 
BEGIN
  SELECT column1, column2 BULK COLLECT INTO v_data 
  FROM source_table 
  WHERE condition;
 
  FORALL i IN 1..v_data.COUNT
    INSERT INTO target_table (column1, column2) 
                      VALUES (v_data(i).column1,v_data(i).column2);
END;

定義記錄類型 t_record，包含兩個(gè)字段 column1 和 column2。
定義表類型 t_table，用于存儲(chǔ) t_record 類型的記錄。
聲明 v_data 變量為 t_table 類型。
使用 BULK COLLECT INTO 語句從源表 source_table 中查詢滿足條件的數(shù)據(jù)，將結(jié)果集一次性存儲(chǔ)到 v_data 表變量中。
使用 FORALL 語句將 v_data 表變量中的所有記錄按順序一次性插入到目標(biāo)表 target_table 中，通過 index 變量 i 訪問每個(gè)數(shù)組元素，并將其對(duì)應(yīng)的字段值插入到目標(biāo)表中

再或者在 PL/SQL 中定義一個(gè)數(shù)組記錄，循環(huán)插入到表 users 中：

DECLARE
    TYPE user_tab_type IS TABLE OF users%ROWTYPE;
    l_users user_tab_type := user_tab_type();
BEGIN
    l_users.extend(2);
    l_users(1).id := 1;
    l_users(1).name := 'Alice';
    l_users(1).age := 20;
    l_users(2).id := 2;
    l_users(2).name := 'Bob';
    l_users(2).age := 25;
    FORALL i IN l_us.first .. l_users.last
        INSERT INTO users VALUES l_users(i);
END;

定義集合類型 user_tab_type，類型為表 users 的 ROWTYPE 類型，表示 l_users 集合包含多個(gè)用戶記錄。
使用 extend 方法將 l_users 集合長度擴(kuò)展到 2。
分別為 l_users 集合中的兩個(gè)元素賦值，并指定 id、name、age 三個(gè)字段的值。
使用 FORALL 語句將 l_users 集合中所有元素一次性寫入到表 users 中，通過 index 變量 i 訪問每個(gè)數(shù)組元素，并在 INSERT INTO 語句中將每個(gè)元素對(duì)應(yīng)的字段值插入到 users 表中。

上述代碼使用 PL/SQL 中的集合類型和 FORALL 語句，從而實(shí)現(xiàn)了批量插入數(shù)據(jù)到 users 表。

備注：

FORALL語句是一種用于批量操作的PL/SQL控制結(jié)構(gòu)，可以大大提高數(shù)據(jù)插入的效率。它需要使用BULK COLLECT子句將數(shù)據(jù)集合化并存儲(chǔ)到一個(gè)數(shù)組中，然后在循環(huán)內(nèi)部執(zhí)行DML操作。

再使用FORALL語句創(chuàng)建一個(gè)類型為EMP_RECORD的對(duì)象用于存儲(chǔ)員工的信息：

DECLARE
    -- 定義 EMP_RECORD 記錄類型，包含兩個(gè)字段 EMP_ID 和 EMP_NAME
    TYPE emp_record IS RECORD (
        emp_id NUMBER,
        emp_name VARCHAR2(100)
    );
    
    -- 定義 EMP_RECORD 類型的數(shù)組變量，用于存儲(chǔ)多個(gè)員工信息
    TYPE emp_tab_type IS TABLE OF emp_record;
    l_emp emp_tab_type := emp_tab_type();
BEGIN
    -- 擴(kuò)展 l_emp 數(shù)組長度到 3
    l_emp.extend(3);
    
    -- 按順序?yàn)槿齻€(gè)元素賦值
    l_emp(1).emp_id := 1;
    l_emp(1).emp_name := 'Alice';
    l_emp(2).emp_id := 2;
    l_emp(2).emp_name := 'Bob';
    l_emp(3).emp_id := 3;
    l_emp(3).emp_name := 'Charlie';
 
    -- 使用 FORALL 語句將所有員工信息一次性插入到 emp 表中
    FORALL i IN l_emp.first .. l_emp.last
        INSERT INTO emp VALUES l_emp(i).emp_id, l_emp(i).emp_name;
        
    -- 提交事務(wù)
    COMMIT;
END;

其實(shí)也可以使用簡(jiǎn)單的FOR循環(huán)和insert into 語句即可實(shí)現(xiàn)，如下所示：

declare 
  i NUMBER;  --定義一個(gè)變量i，類型為number
begin
  --用for循環(huán)插入數(shù)據(jù)，達(dá)到批量插入數(shù)據(jù)的效果
  for i in 5..204 loop
      insert into soption.tSO_YJDJ_ZKCS(bh,yyb,khh,khxm,khqz,zqlx,kpbz,fylb,wtfsfw,zkbl,zkxx,zksx,bdrq) 
	  values(i,'01','010000000001','測(cè)試個(gè)人2','0A','ASH','E',i,8,0.5,1,5,'20160309');
	 commit;   --記得要commit
  end LOOP;
END;

我在每次插入操作之后，都使用 commit 語句來提交數(shù)據(jù)庫事務(wù)，將數(shù)據(jù)保存到數(shù)據(jù)庫中。

以上就是Oracle實(shí)現(xiàn)批量插入記錄的幾種方法，具體使用哪種方法要取決于具體情況和需求。但是在設(shè)計(jì)并執(zhí)行SQL語句前，應(yīng)該全面、仔細(xì)地考慮各種特殊情況，并在程序運(yùn)行中仔細(xì)進(jìn)行測(cè)試和監(jiān)測(cè)，以保證程序穩(wěn)定、高效、可靠。在這里我對(duì)具體的情況進(jìn)行以下幾個(gè)方面的評(píng)估點(diǎn)提出建議：

表結(jié)構(gòu)和字段類型是否與插入的數(shù)據(jù)類型匹配，否則容易出現(xiàn)異?；蝈e(cuò)誤；
批量插入的記錄數(shù)量是否會(huì)對(duì)數(shù)據(jù)庫性能產(chǎn)生影響，應(yīng)該考慮到系統(tǒng)負(fù)荷，適當(dāng)控制一次插入的數(shù)量；
循環(huán)插入數(shù)據(jù)的時(shí)候，一旦出現(xiàn)異常如重復(fù)記錄等，則會(huì)導(dǎo)致插入操作終止，這可能需要添加異常處理機(jī)制。