是否可以在过程之间传递 RECORD 类型的 Oracle 变量而过程不知道其真实类型？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1076363/
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
Can an Oracle variable of type RECORD be passed between procedures without the procedures knowing its real type?
提问by jlpp
Using Oracle 10g PL/SQL. I'm trying to find a way to pass a custom defined RECORD type around without the procedures having to know its real type. Sounds like a job for SYS.ANYDATA but it doesn't look like Oracle supports wrapping RECORD types. For example:
使用 Oracle 10g PL/SQL。我试图找到一种方法来传递自定义定义的 RECORD 类型，而无需程序知道其真实类型。听起来像是 SYS.ANYDATA 的工作，但看起来 Oracle 不支持包装 RECORD 类型。例如：
DECLARE
TYPE t_rec IS RECORD (id number);
v_rec t_rec;
v_ad SYS.ANYDATA;
BEGIN
v_rec.id := 1;
v_ad := SYS.ANYDATA.CONVERTOBJECT(v_rec);
END;
Fails with error:
因错误而失败：
v_ad := SYS.ANYDATA.CONVERTOBJECT(v_rec);
*
ERROR at line 7:
ORA-06550: line 7, column 11:
PLS-00306: wrong number or types of arguments in call to 'CONVERTOBJECT'
ORA-06550: line 7, column 3:
PL/SQL: Statement ignored
Clearly convertobject is not expecting a RECORD but I don't see any other candidates on http://download-west.oracle.com/docs/cd/B19306_01/appdev.102/b14258/t_anydat.htmeither.
显然 convertobject 不期待记录，但我也没有在http://download-west.oracle.com/docs/cd/B19306_01/appdev.102/b14258/t_anydat.htm上看到任何其他候选人。
Ultimately what I'm looking for is some way to store different RECORD types in the same variable. I'd also like to avoid Oracle object types (top-level types defined outside of packages) as they have their own issues.
最终，我要寻找的是某种将不同 RECORD 类型存储在同一变量中的方法。我还想避免 Oracle 对象类型（在包之外定义的顶级类型），因为它们有自己的问题。
Thanks.
谢谢。
采纳答案by DCookie
This sectionof the PL/SQL User's Guide and Reference would indicate that you cannot do what you want to do here directly - PL/SQL wants to try to convert parameters at runtime. You might want to see if you can make a REFCURSOR work. Simple example:
PL/SQL 用户指南和参考的这一部分将表明您不能直接在这里做您想做的事情 - PL/SQL 想要尝试在运行时转换参数。您可能想看看是否可以使 REFCURSOR 工作。简单的例子：
CREATE OR REPLACE PROCEDURE TheTest AS
v_cursor SYS_REFCURSOR;
v_v1 NUMBER;
v_v2 NUMBER;
v_v3 NUMBER;
PROCEDURE getRS(pr OUT SYS_REFCURSOR) IS
BEGIN
OPEN pr FOR SELECT 1,2,3 FROM dual;
END;
BEGIN
getRS(v_cursor);
FETCH v_cursor INTO v_v1, v_v2, v_v3;
dbms_output.put_line(v_v1||','||v_v2||','||v_v3);
CLOSE v_cursor;
END;
You can then pass around the cursor variable reference to your heart's content.
然后，您可以将游标变量引用传递给您的心脏内容。
HTH.
哈。
EDIT:
编辑：
Another option, depending on how many record types you need to deal with, is to overload the same handler procedure(s) with the various record types.
另一种选择，取决于您需要处理多少记录类型，是用各种记录类型重载相同的处理程序过程。
回答by Gary Myers
You are trying to deal with two separate layers, PL/SQL and SQL. SQL does not understand PL/SQL records and will not deal with them.
您正在尝试处理两个独立的层，PL/SQL 和 SQL。SQL 不理解 PL/SQL 记录，也不会处理它们。
"Ultimately what I'm looking for is some way to store different RECORD types in the same variable" Write wrappers that convert them to/from XML.
“最终，我正在寻找某种方式将不同的 RECORD 类型存储在同一变量中” 编写将它们转换为/从 XML 的包装器。
No offence intended, but looking at your other posts, you seem to be trying to impose an object oriented paradigm on SQL and PL/SQL, and it isn't fitting too well. If you are not coding for XE, then maybe consider using Java Stored Procedures in the database.
无意冒犯，但查看您的其他帖子，您似乎试图在 SQL 和 PL/SQL 上强加面向对象的范例，而且它不太合适。如果您不是为 XE 编码，那么可以考虑在数据库中使用 Java 存储过程。
回答by Michal Pravda
From my point of view application design should start in the most bottom layer, which usually is the database. You seem to try to force the DB do something it is not meant for.
在我看来，应用程序设计应该从最底层开始，通常是数据库。你似乎试图强迫数据库做一些它不应该做的事情。
If you really want universal container then you always can serialize anything as for example xml and store it as text (clob) (in this case you can store it as xmltype). However I would suggest to rethink the approach and start using the core capabilities of the DB - store and retrieve rows.
如果您真的想要通用容器，那么您始终可以将任何内容序列化为例如 xml 并将其存储为文本 (clob)（在这种情况下，您可以将其存储为 xmltype）。但是，我建议重新考虑该方法并开始使用数据库的核心功能 - 存储和检索行。
