Oracle:存储过程的可变数量的参数 [英] Oracle: Variable number of parameters to a stored procedure
问题描述
我想使用存储过程将各种用户插入到Oracle数据库中.一个用户(表"user")具有例如姓名,姓氏和出生日期:
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:
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;
如果我想创建一个存储过程来插入变量(1或2或3等)数量的用户该怎么办?谢谢
What if I want to create a stored procedure to insert a variable (1 or 2 or 3 etc) number of users? Thanks
另外,请注意,上面的代码仅用于示例,因此可能会出现随机的错误位
推荐答案
您可以使用记录数组同时插入多个用户.考虑:
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
在这里,我定义了两种数据类型:RECORD
类型将包含一个用户的数据,而INDEX BY TABLE
类型将包含多个记录.现在程序本身:
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:
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
这篇关于Oracle:存储过程的可变数量的参数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!