Oracle存储过程，使用数组作为表插入的参数 [英] Oracle stored procedure using array as parameter for table insert

问题描述

我一直在寻找一个明确的例子，但是没有运气.对不起，如果已经回答了.

I've been searching for a clear example of this, without luck. Sorry if it's already been answered.

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

I'm trying to do something pretty simple - a stored procedure that will take the input and insert them into a table. I want it to take an array of multiple rows and do the insert all at once.

我以为这很简单，但是我还没有找到向我展示的例子.

I thought this would be simple, but I haven't found an example that shows me.

在许多示例中，我看到人们创建了一个返回数组的函数-我要做的是什么?

In a lot of examples, I see people create a function to return the array - is what I have to do?

到目前为止，我有:

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;

推荐答案

以下是您要实现的示例.

Here is an example of want you want to achieve.

让我们创建一个对象类型，其中将包含这样的name和phone属性.

Lets create an object type which will contain name and phone attributes like so.

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

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

Now lets create a collection which elements are of my_object type:

CREATE OR REPLACE TYPE my_table is table of my_object;

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

And now our procedure that will insert into a particular table the data passed in as a collection:

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

---

回答评论

---

Answer to the comment

我想您的Oracle版本是11g之前的版本.因此，要解决此错误(PLS-00436 "implementation restriction".)，您可以使用嵌入式视图插入数据:

I suppose your Oracle version is prior 11g. So, To get around this error(PLS-00436 "implementation restriction".) you can insert data using in-line 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);

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

Or try not to specify column names of the table in the insert statement if number of columns of the table you are inserting into and inserted values are the same:

  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; 

这篇关于Oracle存储过程，使用数组作为表插入的参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！