如何以Collection变量作为数据源来向数据表中插入数据，或者更新数据。

前面我已经告诉大家如何在Oracle中向Collection类型的变量中逐条插入数据， 如何在Oracle中修改Collection类型的变量。现在要变被动为主动，如何以Collection为数据源来处理真实数据表中的数据。

插入：

-- Bulk insert into table from an array.
insert   into  department_teststruct
   select   *   from   table ( cast (d2  as  dept_array));

更新：

UPDATE  Table1 O
    SET  Col2  =  NVL(( select  Col2
                     from   table (p_inventory_array_in) T
                    WHERE  O.Col1  =  T.Col1),
                  Col2);

不能使用下面的方法来更新

UPDATE  Table1  --  Error: PL/SQL: SQL Statement ignored
    SET  Col2  =  T. Col2  FROM  Table1 O, ( SELECT   *   FROM   TABLE (p_array_in)) T  -- Error: PL/SQL: ORA-00933: SQL command not properly ended 
  WHERE  O. Col1  =  T. Col1;

我们可以使用两个数租来进行更新，

 forall i  in   1 ..array1. Count  
   update  table1
     set  col2  =  array2(i)
   where  col1  =  array1(i);

这个方法是使用Collection进行更新的方法中最快的一种。因为这种方法会减少PL/SQL引擎和SQL引擎之间的交互内容。不能使用下面的代码： array(i).col1_value，只能使用：array(i) 。

 

原因是：Bind variables cannot be passed with offset addresses from another bind variable's base address.

当然这样处理的不足就是需要更新所有的数据，我们也可以循环Collection的数据来进行处理。以后我会测试一下性能问题，反馈给大家。

 

Reference:

How to update or sort data in collection?

How to pass arrays to plsql from OAF