arrays - Oracle存储过程使用数组作为表插入的参数

Question

我一直在寻找一个明显的例子，但没有运气。抱歉，如果已经得到答复。

我正在尝试做一些非常简单的事情 - 一个存储过程，它将获取输入并将它们插入到表中。我希望它获取多行数组并一次全部插入。

我认为这很简单，但我还没有找到可以展示我的例子。

在很多示例中，我看到人们创建一个函数来返回数组 - 这是我必须做的吗？

到目前为止我已经:

CREATE OR REPLACE TYPE my_type  is table of  ( name varchar2(20), phone varchar2(10));

CREATE OR REPLACE PROCEDURE customer.insert_mydata(v_my_data my_type )
AS
BEGIN

   BEGIN
 insert into mytable(Name, phone)
 values (v_my_data.name, v_my_data.phone) ; 
      COMMIT;
   END;
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;
---error logging      );
END;

Answer 1

这是您想要实现的愿望的示例。

让我们创建一个对象类型，其中包含 name 和 phone 属性，如下所示。

CREATE OR REPLACE TYPE my_object as object(
   name varchar2(20),
   phone varchar2(10)
);

现在让我们创建一个集合，其元素为 my_object 类型:

CREATE OR REPLACE TYPE my_table is table of my_object;

现在我们的过程将把作为集合传入的数据插入到特定的表中:

CREATE OR REPLACE PROCEDURE insert_mydata(v_my_data my_table)
AS
BEGIN
  forall i in 1..v_my_data.count
    insert into Some_Table(name, phone)         -- 
      values(v_my_data(i).name, v_my_data(i).phone);
END;

演示:

SQL> create table Some_table(
  2    name varchar2(20),
  3    phone varchar2(10)
  4  );

Table created

SQL> select * from some_table;

NAME                 PHONE
-------------------- ----------

SQL> 
SQL> declare
  2    l_col_data my_table;
  3  begin
  4    select my_object('Name'
  5                  ,  '(123)23') bulk collect into l_col_data
  6      from dual
  7    connect by level <=11;
  8  
  9    insert_mydata(l_col_data);
 10  end;
 11  /

PL/SQL procedure successfully completed

SQL> commit;

Commit complete

SQL> select * from some_table;

NAME                 PHONE
-------------------- ----------
Name                 (123)23
Name                 (123)23
Name                 (123)23
Name                 (123)23
Name                 (123)23
Name                 (123)23
Name                 (123)23
Name                 (123)23
Name                 (123)23
Name                 (123)23
Name                 (123)23

11 rows selected

<小时/>

回复评论

我想您的 Oracle 版本是 11g 之前的版本。因此，要解决此错误(PLS-00436“实现限制”。)，您可以使用内联 View 插入数据:

  forall i in 1..v_my_data.count
    insert into (select name, phone from some_table) <--
      values(v_my_data(i).name, v_my_data(i).phone);

或者，如果要插入的表的列数和插入的值相同，则尽量不要在插入语句中指定表的列名:

  forall i in 1..v_my_data.count
    insert into some_table                           <--
      values(v_my_data(i).name, v_my_data(i).phone);

或使用FOR .. LOOP.. END LOOP结构:

  for i in 1..v_my_data.count
  loop
    insert into Some_Table(name, phone)         -- 
      values(v_my_data(i).name, v_my_data(i).phone);
  end loop;