Анонимный тип TABLE или VARRAY в Oracle

Question

Анонимный тип TABLE или VARRAY в Oracle

В Oracle я иногда хотел бы создавать конструкции, подобные этим

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

Очевидно, я могу объявить свои собственные типы для вышеупомянутого. Я могу выбирать между TABLE а также VARRAY, Например:

CREATE TYPE STRINGS AS TABLE OF VARCHAR2(100);
CREATE TYPE NUMBERS AS VARRAY(100) OF NUMBER(10);

В данном конкретном случае другое решение - написать такие вещи, как

SELECT 'a' FROM DUAL UNION ALL
SELECT 'b' FROM DUAL UNION ALL
SELECT 'c' FROM DUAL

Но у меня могут быть более сложные примеры, где мне действительно понадобится TABLE / VARRAY тип. Так что, если мой SQL работает в неизвестной системе, где я не могу создавать типы, потому что у меня нет необходимых грантов?

Итак, мой вопрос: знает ли Oracle "анонимный" TABLE / VARRAY типы, которые доступны на любом экземпляре Oracle? Похож на простой Postgres / H2 / HSQLDB ARRAY типы?

ОБНОВЛЕНИЕ: я в основном запускаю этот SQL из Java, если это актуально. Мне не нужно объяснять PL/SQL, я просто ищу анонимные типы массивов SQL (т.е. "анонимные" автономные хранимые типы). Если они вообще не существуют, ответ НЕТ

19

sql arrays oracle anonymous-types

Источник

user521799 09 янв '12 в 08:12

3 ответа

Решение

Если вы не боитесь явной ссылки на схему 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>

Однако, если этого недостаточно для ваших нужд, запустите этот запрос, чтобы найти больше:

select type_name
       , owner
from all_types
where typecode = 'COLLECTION'
and owner != user
/

Конечно, этот результат будет варьироваться от базы данных к базе данных. Например, многие коллизии в моей базе данных принадлежат XDB, и не на каждой системе это будет установлено. Четыре, которые я перечислил в верхней части этого ответа, должны быть доступны в каждой базе данных начиная с 9iR2 (и, возможно, в начале), хотя они не всегда документированы в более ранних версиях.

"Обратите внимание, что ALL_COLL_TYPES кажется еще лучшим словарным представлением для поиска подходящих типов"

Неплохо подмечено. Мы также можем отфильтровать COLL_TYPE, чтобы отсеять VARRAY. Это представление было введено в 10g, тогда как ALL_TYPES был доступен в 9i. Как и в большинстве случаев Oracle, чем позже версия, тем больше у нее функциональности.

21

Источник

user146325 09 янв '12 в 09:45

Ваш вопрос очень общий - в основном вы можете использовать эти (VARARRAY / TABLE) без явного CREATE TYPE в анонимных блоках PL/SQL вот так:

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;

ИЛИ как это

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;

Для ссылки на Oracle см. http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/composites.htm - в соответствии с этим любой такой VARARRAY и / или TABLE должно быть DECLAREд или создан с помощью CREATE TYPEАФАИК нет такой вещи как "аноним VARARRAY / TABLE".

2

Источник

user847363 09 янв '12 в 08:40

Другие вопросы по тегам sql arrays oracle anonymous-types

user521799 09 янв '12 в 10:20 2012-01-09 10:20 · Accepted Answer · 2012-01-09 10:20

SQL таблица и типы переменных

Интересное решение было дано пользователем 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;

В результате (в 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 будет лучшим выбором для моих нужд.

PL / SQL индексируемые типы массивов

Если вы используете Oracle 12c и PL/SQL, есть также возможность использовать любой из DBMS_SQL типы, которые могут быть удалены, используя TABLE(..) конструктор. Есть:

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