Oracle 中的匿名 TABLE 或 VARRAY 类型

2021-11-18 00:00:00 arrays sql oracle anonymous-types

在 Oracle 中，我有时想创建诸如此类的构造

In Oracle, I would sometimes like to create constructs such as these ones

SELECT * FROM TABLE(STRINGS('a', 'b', 'c'))
SELECT * FROM TABLE(NUMBERS(1, 2, 3))

显然，我可以为上述声明我自己的类型.我可以在 TABLE 和 VARRAY 之间进行选择.例如:

Obviously, I can declare my own types for the above. I can choose between TABLE and VARRAY. For example:

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

但我可能有更复杂的例子，我真的需要一个 TABLE/VARRAY 类型.那么，如果我的 SQL 运行在一个未知系统上，我无法创建类型，因为我可能没有必要的授权，该怎么办?

But I may have more complex examples where I will really need a TABLE / VARRAY type. So what if my SQL is running on an unknown system where I cannot create types because I may not have the necessary grants?

所以我的问题是: Oracle 是否知道在任何 Oracle 实例上可用的匿名"TABLE/VARRAY 类型?类似于 Postgres/H2/HSQLDB 的简单 ARRAY 类型?

So my question is: Does Oracle know "anonymous" TABLE / VARRAY types that are available on any Oracle instance? Similar to Postgres / H2 / HSQLDB's simple ARRAY types?

更新:如果相关的话，我主要是从 Java 运行这个 SQL.无需向我解释 PL/SQL，我实际上只是在寻找匿名 SQL 数组类型(即匿名"独立存储类型).如果它们根本不存在，答案是NO

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

推荐答案

SQL 表和变量类型

用户APC 在这里提供了一个有趣的解决方案.对于这个问题的未来读者，看到这个查询提供了我真正感兴趣的内容可能会很有趣:

SQL table and varray types

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:

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;

导致(在 Oracle 11g 中):

Resulting in (in 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|
+-------------+--------------+----------------------+------+-----------+

看起来 ORA_MINING_NUMBER_NT 和 ORA_MINING_VARCHAR2_NT 将是我需要的最佳匹配.

It looks as though ORA_MINING_NUMBER_NT and ORA_MINING_VARCHAR2_NT will be the best match for my needs.

如果使用 Oracle 12c 和 PL/SQL，还可以使用任何 DBMS_SQL 类型，可以使用 TABLE(..) 构造函数取消嵌套.有:

If using Oracle 12c and PL/SQL, there's also the possibility to use any of the DBMS_SQL types, which can be unnested using the TABLE(..) constructor. There are:

  • DBMS_SQL.CLOB_TABLE
  • DBMS_SQL.BINARY_FLOAT_TABLE
  • DBMS_SQL.BINARY_DOUBLE_TABLE
  • DBMS_SQL.BLOB_TABLE
  • DBMS_SQL.BFILE_TABLE
  • DBMS_SQL.DATE_TABLE
  • DBMS_SQL.NUMBER_TABLE
  • DBMS_SQL.UROWID_TABLE
  • DBMS_SQL.VARCHAR2_TABLE
  • DBMS_SQL.TIME_TABLE
  • DBMS_SQL.TIME_WITH_TIME_ZONE_TABLE
  • DBMS_SQL.TIMESTAMP_TABLE
  • DBMS_SQL.TIMESTAMP_WITH_LTZ_TABLE
  • DBMS_SQL.TIMESTAMP_WITH_TIME_ZONE_TABLE
  • DBMS_SQL.INTERVAL_DAY_TO_SECOND_TABLE
  • DBMS_SQL.INTERVAL_YEAR_TO_MONTH_TABLE

相关文章