Oracle：存储过程的可变参数数量

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数据库中。用户(表“用户”)具有姓名,姓氏和出生日期:

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

另请注意,上面的代码仅用于示例,因此可能存在随机错误位

2 个解决方案

#1

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 RECORD type that will contain the data for one user and an INDEX BY TABLE that 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

#2

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,但理论是否合理

#1