oracle存储过程不定参数传递,Oracle：存储过程的可变参数数量

您可以使用一组记录在同一时间插入多个用户。试想一下：

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

在这里，我定义两种数据类型：一类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

你可以这样调用这样的过程：

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