Oracle преобразовать строку в массив

Обновлено: 04.07.2024

Коллекции присутствую в том или ином виде в большинстве языков программирования и везде имеют схожую суть в плане использования. А именно – позволяют хранить набор объектов одного типа и проводить над всем набором какие-либо действия, либо в цикле проводить однотипные действия со всеми элементами набора.

Таким же образом коллекции используются и в Oracle.

Содержание статьи

Общие сведения о коллекциях в pl/sql

Типы коллекций

Тип коллекции	Количество элементов	Тип индекса	Плотная или разреженная	Без инициализации	Где объявляется	Использование в SQL
Ассоциативный массив (index by table)	Не задано	String Pls_integer	Плотная и разреженная	Empty	PL/SQL block Package	Нет
Varray (variable-size array)	Задано	Integer	Только плотная	Null	PL/SQL block Package Schema level	Только определенные на уровне схемы
Nested table	Не задано	Integer	При создании плотная, может стать разреженной	Null	PL/SQL block Package Schema level	Только определенные на уровне схемы

Плотность коллекции означает, что между элементами коллекции нет пропусков, пустых мест. Некоторые коллекции, как видно из таблицы, могут быть разреженными – т.е. могут иметь разрывы между элементами. Это значит, что в коллекции, например, могут быть элементы с индексом 1 и 4, а с индексом 2 и 3 элементов нет. При этом слоты памяти под 2-й и 3-й элементы будут существовать и будут принадлежать коллекции (в случае nested table), но не содержать при этом объектов и попытка прочитать содержимое этих элементов вызовет ошибку no_data_found.
Подробности можно узнать из видео-лекции в конце статьи.

Ассоциативный массив

Набор пар ключ-значение
Данные хранятся в отсортированном по ключу порядке
Не поддерживает DML-операции (не может участвовать в селектах, не может храниться в таблицах)
При объявлении как константа должен быть сразу инициализирован функцией
Порядок элементов в ассоциативном массиве с строковым индексом зависит от параметров NLS_SORT и NLS_COMP
Нельзя объявить тип на уровне схемы, но можно в пакете
Не имеет конструктора
Индекс не может принимать значение null (но допускает пустую строку — подробности и ссылка на пример в первом комментарии)
Datatype – это любой тип данных, кроме ref cursor

Для помещения в память небольших таблиц-справочников
Для передачи в качестве параметра коллекции

Restrictions:
При изменении параметров NLS_SORT и NLS_COMP во время сессии после заполнения ассоциативного массива, можем получать неожиданные результаты вызовов методов first, last, next, previous. Также могут возникнуть проблемы при передаче ассоциативного массива в качестве параметра на другую БД с иными настройками NLS_SORT и NLS_COMP

Varray

Представляет собой массив последовательно хранящихся элементов

Тип описывается следующим образом (varay_type_def):

Размер задается при создании
Индексируется с 1
Инициализируется конструктором
Если параметры в конструктор не передаются, возвращается пустая коллекция
Datatype – это любой тип данных, кроме ref cursor

Знаем максимально возможное количество элементов
Доступ к элементам последовательный

Restrictions:
Максимальный размер – 2 147 483 647 элементов

Nested table

Тип описывается следующим образом (nested_table_type_def):

Размер коллекции изменяется динамически
Может быть в разряженном состоянии, как показано на картинке
<
Инициализируется конструктором
Если параметры в конструктор не передаются, возвращается пустая коллекция
Datatype – это любой тип данных, кроме ref cursor
Если содержит только одно скалярное значение, то имя колонки – Column_Value

Set operations с nested tables

Операции возможны только с коллекциями nested table. Обе коллекции, участвующие в операции, должны быть одного типа.
Результатом операции также является коллекция nested table.

Операция	Описание
MULTISET UNION	Возвращает объединение двух коллекций
MULTISET UNION DISTINCT	Возвращает объединение двух коллекций с дистинктом (убирает дубли)
MULTISET INTERSECT	Возвращает пересечение двух коллекций
MULTISET INTERSECT DISTINCT	Возвращает пересечение двух коллекций с дистинктом (убирает дубли)
SET	Возвращает коллекцию с дистинктом (т.е. коллекцию без дублей)
MULTISET EXCEPT	Возвращает разницу двух коллекций
MULTISET EXCEPT DISTINCT	Возвращает разницу двух коллекций с дистинктом (убирает дубли)

Небольшой пример (обратите внимание на результат операции MULTISET EXCEPT DISTINCT)

У меня есть вход ' 1,2,3' , и мне нужно преобразовать его в массив.

вот еще один более простой вариант

ответ дан Richard Sylvester, с репутацией 481, 27.01.2015

Oracle предоставляет встроенную функцию DBMS_UTILITY. COMMA_TO_TABLE .

К сожалению, этот не работает с номерами:

Но с небольшим трюком для префикса элементов с 'x', это работает:

С уважением, Роб.

ответ дан Rob van Wijk, с репутацией 15247, 30.09.2010

У нас никогда не останется альтернативы делать одно и то же по-разному, верно? Я недавно нашел это очень удобно:

Я знаю, что переполнение стека хмурится при вставке URL-адресов без объяснений, но у этой конкретной страницы есть несколько действительно хороших вариантов:

Мне особенно нравится этот, который преобразует список с разделителями во временную таблицу, к которой вы можете выполнять запросы:

Да, это очень расстраивает, что dbms_utility. comma_to_table поддерживает только списки, разделенные запятыми, и только тогда, когда элементы в списке являются допустимыми идентификаторами PL / SQL (поэтому числа вызывают ошибку).

Посмотрим на выход :

ответ дан Lalit Kumar B, с репутацией 33145, 17.02.2016

Это не экспертный ответ, поэтому я надеюсь, что кто-то улучшит этот ответ.

Быстрый поиск на моем BBDD привел меня к функции под названием split:

Я не знаю, будет ли это полезно, но мы нашли это здесь . , ,

Запускать его слишком дорого, так как он вычисляет полное разбиение каждый раз, когда вызывающий абонент запрашивает один из элементов в нем. , ,

Вы можете использовать функцию замены, чтобы легко заменить запятую. Делать это -

Синтаксис для функции REPLACE в SQL Server (Transact-SQL):
ЗАМЕНИТЬ (строка, строка_место, замена_строка)

Параметры или аргументы

string: Исходная строка, из которой последовательность символов будет заменена другим набором символов.
string_to_replace: Строка, которая будет искать в string1.
replace_string: Строка замены. Все вхождения string_to_replace будут заменены замещающей строкой в string1.
Примечание :

Функция REPLACE выполняет замену, которая не с учетом регистра. Таким образом, все вхождения string_to_replace будут заменено на replace_string независимо от случая string_to_replace или replace_string

Например:
ВЫБЕРИТЕ ЗАМЕНУ ( 'Kapil, raj, chouhan', ',', '' ) от DUAL;
Результат: Капил радж чухан

ИЗБРАННОЕ ЗАМЕНА ( «Я живу в Индии», «,» - «) из ДВОЙНОГО;
Результат: я живу в Индии

ВЫБРАТЬ ЗАМЕНУ ( 'facebook. com ',' face ',' friends ') от DUAL;
Результат: книга друзей. com

В Oracle PL/SQL Associative Arrays, также известные как индексные таблицы, в которых для значений индекса используя произвольные числа и строки. Associative Arrays - это набор пар ключ-значение, где каждый ключ уникален и используется для нахождения соответствующего значения в массиве.

Синтаксис

Синтаксис для определения, а затем объявление переменной типа Associative Arrays в Oracle PL/SQL.

TYPE type_assoc_arr IS TABLE OF element_type [NOT NULL]
INDEX BY [PLS_INTEGER | BINARY_INTEGER | VARCHAR2(size_limit)];
INDEX BY key_type;
var_type type_assoc_arr;

Параметры или аргументы

type_assoc_arr – имя типа Associative Arrays
element_type - любой тип данных PL/SQL, за исключением REF CURSOR
key_type тип индекса, может быть числовым: PLS_INTEGER или BINARY_INTEGER, это может быть также VARCHAR2 или один из его подтипов VARCHAR, STRING или LONG.
var_type - имя переменной типа Associative Arrays

Примечание

Типы RAW, LONG RAW, ROWID, CHAR и CHARACTER не разрешены в качестве ключей для Associative Arrays.
Associative Arrays могут хранить данные с использованием значения первичного ключа в качестве индекса, где значения ключа не являются последовательными.
Когда вы ссылаетесь на элемент Associative Arrays, который использует ключ на основе VARCHAR2, вы можете использовать другие типы, такие как DATE или TIMESTAMP, если они могут быть преобразованы в VARCHAR2 с помощью функции TO_CHAR.
Не используйте TO_CHAR (SYSDATE) в качестве ключа.

Примеры

Рассмотрим некоторые примеры чтобы понять как работать с ассоциативным массивом в Oracle PL/SQL.

Associative Arrays, индексированный VARCHAR2

TYPE population IS TABLE OF NUMBER -- Тип ассоциативного массива city_population population; -- переменная ассоциативного массива -- Добавление элементов (пары ключ-значение) в ассоциативный массив: i := city_population. FIRST ; -- Получим первый элемент массива i := city_population. NEXT (i); -- Получим следующий элемент массива

В этом примере мы определили тип ассоциативного массива population , индексированного строкой, объявили переменную этого типа city_population , заполнили переменную тремя элементами, изменили значение одного элемента и напечатали значения (в порядке сортировки, а не в порядке создания). (FIRST и NEXT - методы коллекций).

Associative Arrays, индексированный целым числом

В этом примере определяется тип ассоциативного массива, индексированный PLS_INTEGER, и функция возвращает ассоциативный массив.

TYPE sum_multiples IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER ; n PLS_INTEGER := 5; -- number of multiples to sum for display В результате получим сумму первых пяти чисел кратных 3:

Associative Arrays подходит для:

Относительно небольшая таблица поиска, которая может быть построена в памяти каждый раз, когда вы вызываете подпрограмму или инициализируете пакет, который ее объявляет.
Передача коллекций на сервер базы данных и обратно.

Заметка:

Вы не можете объявить тип Associative Arrays на уровне схемы. Поэтому, чтобы передать переменную Associative Arrays в качестве параметра в отдельную сохраненную подпрограмму, вы должны объявить тип этой переменной в спецификации пакета. Это делает тип доступным для вызываемой подпрограммы (которая объявляет формальный параметр этого типа) и вызывающей подпрограммы или анонимного блока (который объявляет и передает переменную этого типа)
Например.