Anonymous TABLE or VARRAY type in Oracle(Oracle 中的匿名 TABLE 或 VARRAY 类型)
问题描述
在 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
这篇关于Oracle 中的匿名 TABLE 或 VARRAY 类型的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!
本文标题为:Oracle 中的匿名 TABLE 或 VARRAY 类型
- 以一个值为轴心,但将一行上的数据按另一行分组? 2022-01-01
- 如何将 SonarQube 6.7 从 MySQL 迁移到 postgresql 2022-01-01
- 使用 Oracle PL/SQL developer 生成测试数据 2021-01-01
- 更改自动增量起始编号? 2021-01-01
- SQL 临时表问题 2022-01-01
- 如何使用 pip 安装 Python MySQLdb 模块? 2021-01-01
- 远程 mySQL 连接抛出“无法使用旧的不安全身份验证连接到 MySQL 4.1+"来自 XAMPP 的错误 2022-01-01
- 如何将 Byte[] 插入 SQL Server VARBINARY 列 2021-01-01
- 导入具有可变标题的 Excel 文件 2021-01-01
- 在SQL中,如何为每个组选择前2行 2021-01-01