SQL Oracle 中的匿名 TABLE 或 VARRAY 类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8785459/
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
Anonymous TABLE or VARRAY type in Oracle
提问by Lukas Eder
In Oracle, I would sometimes like to create constructs such as these ones
在 Oracle 中，我有时想创建这样的结构
SELECT * FROM TABLE(STRINGS('a', 'b', 'c'))
SELECT * FROM TABLE(NUMBERS(1, 2, 3))
Obviously, I can declare my own types for the above. I can choose between TABLEand VARRAY. For example:
显然，我可以为上述声明我自己的类型。我可以在TABLE和之间选择VARRAY。例如：
CREATE TYPE STRINGS AS TABLE OF VARCHAR2(100);
CREATE TYPE NUMBERS AS VARRAY(100) OF NUMBER(10);
In this particular case, another solution is to write things like
在这种特殊情况下，另一种解决方案是编写类似
SELECT 'a' FROM DUAL UNION ALL
SELECT 'b' FROM DUAL UNION ALL
SELECT 'c' FROM DUAL
But I may have more complex examples where I will really need a TABLE/ VARRAYtype. So what if my SQL is running on an unknown system where I cannot create types because I may not have the necessary grants?
但是我可能有更复杂的例子，我真的需要一个TABLE/VARRAY类型。那么，如果我的 SQL 运行在一个未知的系统上，我无法创建类型，因为我可能没有必要的授权怎么办？
So my question is:Does Oracle know "anonymous" TABLE/ VARRAYtypes that are available on any Oracle instance? Similar to Postgres / H2 / HSQLDB's simple ARRAYtypes?
所以我的问题是：Oracle 是否知道任何 Oracle 实例上可用的“匿名” TABLE/VARRAY类型？类似于 Postgres / H2 / HSQLDB 的简单ARRAY类型？
UPDATE: I am mostly running this SQL from Java, if this is relevant. No need to explain PL/SQL to me, I'm really just looking for anonymous SQL array types (i.e. "anonymous" standalone stored types). If they don't exist at all, the answer is NO
更新：如果相关的话，我主要是从 Java 运行这个 SQL。无需向我解释 PL/SQL，我实际上只是在寻找匿名 SQL 数组类型（即“匿名”独立存储类型）。如果它们根本不存在，答案是否定的
采纳答案by Lukas Eder
SQL table and varray types
SQL 表和变量类型
An interesting solution was given by user APC here. For future readers of this question, it may be interesting to see that this query provides what I'm really interested in:
用户APC 在这里给出了一个有趣的解决方案。对于这个问题的未来读者，看到这个查询提供了我真正感兴趣的内容可能会很有趣：
select coll_type, elem_type_name, type_name, length, upper_bound
from all_coll_types
where owner = 'SYS'
and elem_type_name IN ('VARCHAR2', 'NUMBER')
order by coll_type, elem_type_name, type_name;
Resulting in (in Oracle 11g):
结果（在 Oracle 11g 中）：
+-------------+--------------+----------------------+------+-----------+
|COLL_TYPE |ELEM_TYPE_NAME|TYPE_NAME |LENGTH|UPPER_BOUND|
+-------------+--------------+----------------------+------+-----------+
|TABLE |NUMBER |KU$_OBJNUMSET |{null}| {null}|
|TABLE |NUMBER |KU$_XMLCOLSET_T |{null}| {null}|
|TABLE |NUMBER |ORA_MINING_NUMBER_NT |{null}| {null}|
|TABLE |VARCHAR2 |DBMS_AW$_COLUMNLIST_T | 100| {null}|
|TABLE |VARCHAR2 |DBMS_DEBUG_VC2COLL | 1000| {null}|
|TABLE |VARCHAR2 |HSBLKNAMLST | 30| {null}|
|TABLE |VARCHAR2 |KU$_VCNT | 4000| {null}|
|TABLE |VARCHAR2 |ORA_MINING_VARCHAR2_NT| 4000| {null}|
|VARYING ARRAY|NUMBER |AWRRPT_NUM_ARY |{null}| 30|
|VARYING ARRAY|NUMBER |JDM_NUM_VALS |{null}| 999|
|VARYING ARRAY|NUMBER |ODCIGRANULELIST |{null}| 65535|
|VARYING ARRAY|NUMBER |ODCINUMBERLIST |{null}| 32767|
|VARYING ARRAY|NUMBER |SQL_OBJECTS |{null}| 2000|
|VARYING ARRAY|NUMBER |TABLESPACE_LIST |{null}| 64000|
|VARYING ARRAY|VARCHAR2 |AQ$_JMS_NAMEARRAY | 200| 1024|
|VARYING ARRAY|VARCHAR2 |AQ$_MIDARRAY | 32| 1024|
|VARYING ARRAY|VARCHAR2 |AWRRPT_VCH_ARY | 80| 30|
|VARYING ARRAY|VARCHAR2 |DBMSOUTPUT_LINESARRAY | 32767| 2147483647|
|VARYING ARRAY|VARCHAR2 |DBMS_XS_ROLELIST | 1024| 4096|
|VARYING ARRAY|VARCHAR2 |FLASHBACKTBLIST | 30| 100|
|VARYING ARRAY|VARCHAR2 |HSBLKVALARY | 4000| 250|
|VARYING ARRAY|VARCHAR2 |JDM_ATTR_NAMES | 60| 999|
|VARYING ARRAY|VARCHAR2 |JDM_STR_VALS | 4000| 999|
|VARYING ARRAY|VARCHAR2 |KU$_DROPCOLLIST | 4000| 1000|
|VARYING ARRAY|VARCHAR2 |KUPC$_LOBPIECES | 4000| 4000|
|VARYING ARRAY|VARCHAR2 |ODCIRIDLIST | 5072| 32767|
|VARYING ARRAY|VARCHAR2 |ODCIVARCHAR2LIST | 4000| 32767|
|VARYING ARRAY|VARCHAR2 |RE$NAME_ARRAY | 30| 1024|
|VARYING ARRAY|VARCHAR2 |RE$RULE_LIST | 65| 1024|
|VARYING ARRAY|VARCHAR2 |SQLPROF_ATTR | 500| 2000|
|VARYING ARRAY|VARCHAR2 |TXNAME_ARRAY | 256| 100|
+-------------+--------------+----------------------+------+-----------+
It looks as though ORA_MINING_NUMBER_NTand ORA_MINING_VARCHAR2_NTwill be the best match for my needs.
它看起来好像ORA_MINING_NUMBER_NT并且ORA_MINING_VARCHAR2_NT将是我需要的最佳匹配。
PL/SQL indexed array types
PL/SQL 索引数组类型
If using Oracle 12c and PL/SQL, there's also the possibility to use any of the DBMS_SQLtypes, which can be unnested using the TABLE(..)constructor. There are:
如果使用 Oracle 12c 和 PL/SQL，还可以使用任何DBMS_SQL类型，这些类型可以使用TABLE(..)构造函数取消嵌套。有：
DBMS_SQL.CLOB_TABLEDBMS_SQL.BINARY_FLOAT_TABLEDBMS_SQL.BINARY_DOUBLE_TABLEDBMS_SQL.BLOB_TABLEDBMS_SQL.BFILE_TABLEDBMS_SQL.DATE_TABLEDBMS_SQL.NUMBER_TABLEDBMS_SQL.UROWID_TABLEDBMS_SQL.VARCHAR2_TABLEDBMS_SQL.TIME_TABLEDBMS_SQL.TIME_WITH_TIME_ZONE_TABLEDBMS_SQL.TIMESTAMP_TABLEDBMS_SQL.TIMESTAMP_WITH_LTZ_TABLEDBMS_SQL.TIMESTAMP_WITH_TIME_ZONE_TABLEDBMS_SQL.INTERVAL_DAY_TO_SECOND_TABLEDBMS_SQL.INTERVAL_YEAR_TO_MONTH_TABLE
DBMS_SQL.CLOB_TABLEDBMS_SQL.BINARY_FLOAT_TABLEDBMS_SQL.BINARY_DOUBLE_TABLEDBMS_SQL.BLOB_TABLEDBMS_SQL.BFILE_TABLEDBMS_SQL.DATE_TABLEDBMS_SQL.NUMBER_TABLEDBMS_SQL.UROWID_TABLEDBMS_SQL.VARCHAR2_TABLEDBMS_SQL.TIME_TABLEDBMS_SQL.TIME_WITH_TIME_ZONE_TABLEDBMS_SQL.TIMESTAMP_TABLEDBMS_SQL.TIMESTAMP_WITH_LTZ_TABLEDBMS_SQL.TIMESTAMP_WITH_TIME_ZONE_TABLEDBMS_SQL.INTERVAL_DAY_TO_SECOND_TABLEDBMS_SQL.INTERVAL_YEAR_TO_MONTH_TABLE
回答by APC
Providing you're not scared of explicitly referencing the SYS schema there are a few. Here are some I use quite often (well odcivarchar2listnot so much, as it chews up a lot of memory: for strings I prefer dbms_debug_vc2coll).
如果您不害怕显式引用 SYS 架构，则有一些。这里有一些我经常使用的（odcivarchar2list不是那么多，因为它会占用大量内存：对于我更喜欢的字符串dbms_debug_vc2coll）。
SQL> desc sys.odcinumberlist
sys.odcinumberlist VARRAY(32767) OF NUMBER
SQL> desc sys.odcivarchar2list
sys.odcivarchar2list VARRAY(32767) OF VARCHAR2(4000)
SQL> desc sys.ODCIDATELIST
sys.ODCIDATELIST VARRAY(32767) OF DATE
SQL> desc sys.dbms_debug_vc2coll
sys.dbms_debug_vc2coll TABLE OF VARCHAR2(1000)
SQL>
However, if those aren't sufficient for your needs run this query to find some more:
但是，如果这些不足以满足您的需求，请运行此查询以查找更多信息：
select type_name
, owner
from all_types
where typecode = 'COLLECTION'
and owner != user
/
Of course, this result will vary from database to database. For instance a lot of the colllections on my database are owned by XDB and not every system will have that installed. The four I listed at the the top of this answer should be available on every database since 9iR2 (and perhaps early) although they are not always documented in earlier versions.
当然，这个结果会因数据库而异。例如，我的数据库中的很多集合都归 XDB 所有，并不是每个系统都会安装它。我在这个答案顶部列出的四个应该在自 9iR2（也许是早期）以来的每个数据库中都可用，尽管它们并不总是在早期版本中记录。
"Note that ALL_COLL_TYPES seems to be an even better dictionary view to find appropriate types"
“请注意 ALL_COLL_TYPES 似乎是一个更好的字典视图来找到合适的类型”
That's a good point. We can also filter on COLL_TYPE to winnow out the VARRAYs. That view was introduced into 10g whereas ALL_TYPES was available on 9i. As with most things Oracle, the later the version the more functionality it has.
那是个很好的观点。我们还可以过滤 COLL_TYPE 以筛选出 VARRAY。该视图被引入 10g，而 ALL_TYPES 在 9i 上可用。与 Oracle 的大多数事物一样，版本越晚，它的功能就越多。
回答by Yahia
Your question is very general - basically you can use these (VARARRAY/ TABLE) without an explicit CREATE TYPEin anonymous PL/SQL blocks like this:
您的问题非常普遍 - 基本上您可以在匿名 PL/SQL 块中使用这些（VARARRAY/ TABLE）而无需显式CREATE TYPE，如下所示：
DECLARE
TYPE genres IS VARRAY(4) OF book_genre.genre_name%TYPE;
Fiction_genres genres;
TYPE phone_no_tab IS VARRAY(6) OF VARCHAR2(20) ;
phone_nos phone_no_tab;
BEGIN
fiction_genres := genres('MYSTERY','SUSPENSE', 'ROMANCE','HORROR');
phone_nos := phone_no_tab();
phone_nos.EXTEND(2);
phone_nos(1) := '0117 942 2508';
END;
OR like this
或者像这样
declare
TYPE auftrag_table_typ IS TABLE OF auftrag%ROWTYPE
INDEX BY BINARY_INTEGER;
auftrag_table auftrag_table_typ;
v_index BINARY_INTEGER;
begin
v_index := auftrag_table.first;
while v_index is not NULL loop
// do something with auftrag_table(v_index)
v_index := auftrag_table.next (v_index);
end loop;
end;
For Oracle reference see http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/composites.htm- according to this any such VARARRAYand/or TABLEneeds to be DECLAREd or created via CREATE TYPE, so AFAIK there is no such thing as "anonymous VARARRAY/ TABLE".
对于 Oracle 参考，请参阅http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/composites.htm- 据此，任何此类VARARRAY和/或TABLE需要通过DECLAREd 或创建CREATE TYPE，因此 AFAIK 没有这样的作为“匿名VARARRAY/ TABLE”的东西。
