oracle 将 pl/sql 记录作为参数传递给过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19636714/
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
pass pl/sql record as arguement to procedure
提问by subodh1989
How to pass pl/sql record type to a procedure :
如何将 pl/sql 记录类型传递给过程：
CREATE OR REPLACE PACKAGE BODY PKGDeleteNumber
AS
PROCEDURE deleteNumber (
list_of_numbers IN List_Numbers
)
IS
i_write VARCHAR2(5);
BEGIN
--do something
END deleteNumber;
END PKGDeleteNumber;
/
In this procedure deleteNumberI have used List_Numbers, which is a record type. The package declaration for the same is :
在这个过程中，deleteNumber我使用了List_Numbers，它是一种记录类型。相同的包声明是：
CREATE OR REPLACE PACKAGE PKGDeleteNumber
AS
TYPE List_Numbers IS RECORD (
IID NUMBER
);
TYPE list_of_numbers IS TABLE OF List_Numbers;
PROCEDURE deleteNumber (
list_of_numbers IN List_Numbers
);
END PKGDeleteNumber;
I have to execute the procedure deleteNumberpassing a list of values. I inserted numbers in temp_testtable, then using a cursor U fetched the data from it :
我必须执行deleteNumber传递值列表的过程。我在temp_test表中插入数字，然后使用游标 U 从中获取数据：
SELECT *
BULK COLLECT INTO test1
FROM temp_test;
Now, to call the procedure I am using
现在，调用我正在使用的程序
execute immediate 'begin PKGDELETENUMBER.DELETENUMBER(:1); end;'
using test1;
I have tried many other things as well(for loop, dbms_binding, etc). How do I pass a pl/sql record type as argument to the procedure?
我已经尝试了很多其他的事情，以及（for loop，dbms_binding，等）。如何将 pl/sql 记录类型作为参数传递给过程？
EDIT:
编辑：
Basically, I want to pass a list of numbers, using native dynamic sql only...
基本上，我想传递一个数字列表，仅使用本机动态 sql ......
adding the table temp_test defn (no index or constraint):
添加表 temp_test defn（无索引或约束）：
create table test_temp (
IID number
);
and then inserted 1,2,3,4,5 using normal insertstatements.
然后使用普通insert语句插入 1,2,3,4,5 。
For this solution,
对于这个解决方案，
In a package testproc
CREATE TYPE num_tab_t IS TABLE OF NUMBER;
CREATE OR REPLACE PROCEDURE my_dyn_proc_test (p_num_array IN num_tab_t) AS
BEGIN
dbms_output.put_line(p_num_array.COUNT);
END;
/
this is called from sql prompt/toad DECLARE v_tab testproc.num_tab_t := testproc.num_tab_t(1, 10); BEGIN EXECUTE IMMEDIATE 'BEGIN testproc.my_dyn_proc_test(:1); END;' USING v_tab; END;
这是从 sql prompt/toad DECLARE v_tab testproc.num_tab_t := testproc.num_tab_t(1, 10); BEGIN EXECUTE IMMEDIATE 'BEGIN testproc.my_dyn_proc_test(:1); 结尾;' 使用 v_tab; 结尾;
this will not work.This shows error.I am not at my workstation so am not able to reproduce the issue now.
这将不起作用。这显示错误。我不在我的工作站，所以现在无法重现该问题。
采纳答案by Przemyslaw Kruglej
You can't use RECORDtypes in USINGclause of EXECUTE IMMEDIATEstatement. If you just want to pass a list of numbers, why don't you just use a variable of TABLE OF NUMBERtype? Check below example:
您不能RECORD在USING语句的子句中使用类型EXECUTE IMMEDIATE。如果你只想传递一个数字列表，为什么不使用一个TABLE OF NUMBER类型的变量呢？检查以下示例：
CREATE TYPE num_tab_t IS TABLE OF NUMBER;
CREATE OR REPLACE PROCEDURE my_dyn_proc_test (p_num_array IN num_tab_t) AS
BEGIN
dbms_output.put_line(p_num_array.COUNT);
END;
/
DECLARE
v_tab num_tab_t := num_tab_t(1, 10);
BEGIN
EXECUTE IMMEDIATE 'BEGIN my_dyn_proc_test(:1); END;' USING v_tab;
END;
Output:
输出：
2
Edit
编辑
Try this:
尝试这个：
CREATE TYPE num_tab_t IS TABLE OF NUMBER;
CREATE OR REPLACE PACKAGE testproc AS
PROCEDURE my_dyn_proc_test (p_num_array IN num_tab_t);
END;
/
CREATE OR REPLACE PACKAGE BODY testproc AS
PROCEDURE my_dyn_proc_test (p_num_array IN num_tab_t) AS
BEGIN
dbms_output.put_line(p_num_array.COUNT);
END;
END;
/
DECLARE
v_tab num_tab_t := num_tab_t(1, 10);
BEGIN
EXECUTE IMMEDIATE 'BEGIN testproc.my_dyn_proc_test(:1); END;' USING v_tab;
END;
回答by Perrault Jean-Paul
Use an object type. object types are visible to all packages
使用对象类型。对象类型对所有包可见
