Oracle 存储过程使用数组作为表插入的参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12677746/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle stored procedure using array as parameter for table insert
提问by user1707389
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?
在很多例子中，我看到人们创建了一个函数来返回数组——这是我必须做的吗？
So far I have:
到目前为止，我有：
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;
回答by Nick Krasnov
Here is an example of want you want to achieve.
这是您想要实现的愿望的示例。
Lets create an object type which will contain nameand phoneattributes like so.
允许创建一个对象类型其中将包含name和phone属性喜欢这样。
CREATE OR REPLACE TYPE my_object as object(
name varchar2(20),
phone varchar2(10)
);
Now lets create a collection which elements are of my_objecttype:
现在让我们创建一个元素my_object类型的集合：
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;
Demonstration:
示范：
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
回复评论
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:
我想您的 Oracle 版本是 11g 之前的版本。因此，要解决此错误（PLS-00436 "implementation restriction".），您可以使用内嵌视图插入数据：
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);
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);
OR use the FOR .. LOOP.. END LOOPconstruct:
或使用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;
回答by user1944408
insert into mytable(Name, phone)
select name, phone
from TABLE(v_my_data);
