是否可以在包/过程之外创建 Oracle 关联数组类型？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1066476/
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
Is it possible to create Oracle associative array type outside of a package/procedure?
提问by jlpp
In Oracle Database 10g, is it possible to create an associative array type outside of a package or procedure? I would like to be able to do this so that I can reference this associative array type in another type. For example:
在 Oracle 数据库 10g 中，是否可以在包或过程之外创建关联数组类型？我希望能够这样做，以便我可以在另一种类型中引用此关联数组类型。例如：
create type my_type_map is table of varchar2(10) index by varchar2(10);
create type my_other_type as object (
id number(15),
member procedure initialize(p_my_type_map my_type_map)
) not instantiable not final;
The error I get is:
我得到的错误是：
SQL> create type my_type_map is table of varchar2(20) index by varchar2(10);
2 /
Warning: Type created with compilation errors.
SQL> show errors;
Errors for TYPE MY_TYPE_MAP:
LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
1/21 PLS-00355: use of pl/sql table not allowed in this context
SQL>
Seems that Oracle considers:
似乎甲骨文认为：
index by varchar2(10)
to be PL/SQL and doens't allow it in the creation of SQL types. If Oracle really doesn't allow associative arrays to be defined outside of packages then is there a good alternative? Is it possible to create types that extend Oracle's Object inside of a package so that all types are defined in the same package?
成为 PL/SQL 并且不允许在创建 SQL 类型时使用它。如果 Oracle 真的不允许在包之外定义关联数组，那么有没有好的选择？是否可以创建在包内扩展 Oracle 对象的类型，以便所有类型都定义在同一个包中？
Thanks, Jeff
谢谢，杰夫
Edit: Corrected code sample, added log, added possible alternative as question.
编辑：更正了代码示例，添加了日志，添加了可能的替代问题。
采纳答案by DCookie
The answer is no, you cannot do what you're trying to do, any more than you can create a type to add a BOOLEAN typed variable to an object. The items in an object must contain Oracle types, not PL/SQL types. A bit clunky alternative could be:
答案是否定的，你不能做你想做的事，就像你不能创建一个类型来向一个对象添加一个 BOOLEAN 类型变量一样。对象中的项必须包含 Oracle 类型，而不是 PL/SQL 类型。一个有点笨重的替代方案可能是：
CREATE TYPE t_aa AS VARRAY(10) OF VARCHAR2(10);
CREATE OR REPLACE TYPE t_ua AS OBJECT (ID NUMBER(15)
, MEMBER PROCEDURE initialize(p_aa t_aa)
, MEMBER PROCEDURE initialize(p_aa_i t_aa))
NOT INSTANTIABLE NOT FINAL;
Store your associated pairs of variables in the two VARRAYs. You will have to know the largest possible size of your arrays.
将关联的变量对存储在两个 VARRAY 中。您必须知道数组的最大可能大小。
回答by Jeffrey Kemp
Those are PL/SQL types as the error suggests. There's nothing stopping you from declaring them in the public specification of a package, then you can refer to them in any PL/SQL code you need.
正如错误所暗示的那样，这些是 PL/SQL 类型。没有什么可以阻止您在包的公共规范中声明它们，然后您可以在您需要的任何 PL/SQL 代码中引用它们。
