Oracle：存储过程的可变参数数量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/980324/
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: Variable number of parameters to a stored procedure
提问by pistacchio
I want to INSERT various users into a Oracle db with a stored procedure. A user (table "user") has, say, name, surname and date of birth:
我想使用存储过程将各种用户插入到 Oracle 数据库中。一个用户（表“user”）有名字、姓氏和出生日期：
CREATE TABLE "USER"
(
"Name" VARCHAR2(50),
"Surname" VARCHAR2(50),
"Dt_Birth" DATE,
)
A stored procedure to create a user is pretty simple:
创建用户的存储过程非常简单：
CREATE PROCEDURE Insert_User(p_user, p_surname, p_dt_birth)
BEGIN
INSERT INTO User(Name, Surname, Dt_Birth) VALUES(p_user, p_surname, p_dt_birth);
END;
What if I want to create a stored procedure to insert a variable (1 or 2 or 3 etc) number of users? Thanks
如果我想创建一个存储过程来插入变量（1 或 2 或 3 等）数量的用户怎么办？谢谢
Also, please note that the above code is just for sample, so random incorrect bits may be present
另外，请注意，以上代码仅用于示例，因此可能存在随机不正确的位
回答by Vincent Malgrat
You could use an array of records to insert several users at the same time. Consider:
您可以使用一组记录同时插入多个用户。考虑：
SQL> CREATE TABLE "USER" (
2 "Name" VARCHAR2(50),
3 "Surname" VARCHAR2(50),
4 "Dt_Birth" DATE
5 );
Table created
SQL> CREATE OR REPLACE PACKAGE user_pkg IS
2
3 TYPE user_rec IS RECORD (
4 name VARCHAR2(50),
5 surname VARCHAR2(50),
6 dt_birth DATE
7 );
8 TYPE user_tab IS TABLE OF user_rec INDEX BY BINARY_INTEGER;
9
10 PROCEDURE insert_user(p_user user_tab);
11
12 END user_pkg;
13 /
Package created
Here I defined two datatypes: a RECORDtype that will contain the data for one user and an INDEX BY TABLEthat will contain several records. Now the procedure itself:
我在这里定义了两种数据类型：一种RECORD将包含一个用户的数据，一种INDEX BY TABLE将包含多条记录。现在程序本身：
SQL> CREATE OR REPLACE PACKAGE BODY user_pkg IS
2
3 PROCEDURE insert_user(p_user user_tab) IS
4 BEGIN
5 FOR i IN 1..p_user.count LOOP
6 INSERT INTO "USER"("Name", "Surname", "Dt_Birth")
7 VALUES (p_user(i).name,
8 p_user(i).surname,
9 p_user(i).dt_birth);
10 END LOOP;
11 END insert_user;
12
13 END user_pkg;
14 /
Package body created
You would then call the procedure like this:
然后你会像这样调用这个过程：
SQL> DECLARE
2 l_user_tab user_pkg.user_tab;
3 BEGIN
4 SELECT owner, object_name, created
5 BULK COLLECT INTO l_user_tab
6 FROM all_objects
7 WHERE ROWNUM <= 3;
8 user_pkg.insert_user(l_user_tab);
9 END;
10 /
SQL> SELECT * FROM "USER";
Name Surname Dt_Birth
------- -------- -----------
SYS IND$ 12/05/2000
SYS ICOL$ 12/05/2000
SYS OBJ$ 12/05/2000
回答by Glen
You could pass an array of users to the procedure
您可以将一组用户传递给该过程
type userType is record (
name varchar2(100),
...
);
type userList is table of userType index by binary_integer;
procedure array_insert (p_userList in userList) is
begin
for i in p_userList.first..p_userList.last
insert into users (username) values (p_userList(i) );
end array_insert;
Not sure if all that's valid PL/SQL but the theory is sound
不确定所有这些是否都是有效的 PL/SQL 但理论是合理的
