Oracle apex удалить строку

Обновлено: 07.07.2024

Сегодня нашей компании исполняется 28 лет, и в честь этого приятного события мы решили поделиться с вами новым материалом.

Благодарим за помощь с переводом нашего постоянного автора Юрия Пономарева OBIEESupport.

Автор статьи – Мишель Скэмин, основатель и управляющий партнер компании, предоставляющей сервис Reading Rewards. В далеком 2009 году Мишель страдала от того, что ее сыновья 8 и 9 лет слишком мало читали. Книги просто не могли конкурировать с компьютером и видеоиграми, поэтому Мишель со своим мужем решили разработать систему, в которой дети зарабатывали время на компьютерные игры, читая книжки.

Будучи IT-консультантом и разработчиком веб-приложений, Мишель разработала софт, позволяющий детям фиксировать время чтения и просмотра телевизора, а родителям – отслеживать это время. Это и стало началом сервиса Reading Rewards.

А теперь, собственно, статья.

Итак, вы выбрали потрясающее приложение Oracle APEX за рекордную быстроту — писать много не придется. И, как гласит старая поговорка, чему быть – того не миновать!

В 2010 году я создала приложение, чтобы попытаться увлечь моих двух маленьких сыновей чтением. Признаюсь, в то время я не думала о производительности, и в нем было несколько сочных «select count(*) from huge table» вроде как стратегически разбросанных по всему коду.

Эй, мои мальчики на самом деле читали не так уж много, так что эти таблицы были довольно маленькими в то время…

Статистика из Google Analytics

Кроме того, что весь этот интерес был для меня очень волнительным, я была совсем не готова к такому количеству переходов. У меня были проблемы с производительностью. Нужно просто признать, что я стала заядлым студентом Oracle APEX performance tuning и думала, что просто поделюсь некоторыми вещами, которые я узнала за эти годы.

Как определить узкое место?

Есть много вещей, на которые вы захотите обратить внимание при оценке производительности вашего приложения. Вы захотите рассмотреть проблемы, связанные с нею, браузере. Сетевые проблемы. Конфигурация ORDS. Конфигурацию базы данных, включая возможные отсутствующие индексы. Есть ли какие-либо блокировки базы данных в игре? Ожидания?

Как только вы почувствуете, что ваша базовая инфраструктура находится в хорошем состоянии и не виновата, возможно, пришло время обратить ваше внимание на само приложение, имея в виду ваш фронтенд (Javascript и CSS) и бэкенд (SQL и PL/SQL).

1. Фронтенд: порядок файлов имеет значение

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

Это гарантирует, что ваши пользователи, по крайней мере, получат компоненты пользовательского интерфейса, даже если некоторые файлы бизнес-логики еще не загружены.

2. Просмотр монитора активности

Это всегда отличное место для начала работы. Если все кажется медленным, и вы не знаете, где искать, монитор активности в среде разработки APEX может дать ценную информацию.

Ссылка на монитор активности из консоли разработки APEX

Каждый просмотр страницы в вашем рабочем пространстве и приложениях регистрируется, включая информацию о пользователе, дате/метке времени, приложении, page_id и, самое главное, о времени работы приложения.

Мой любимый отчет просмотра страниц — «По взвешенной производительности страниц».

Пример представления из монитора активности APEX

Обратите пристальное внимание на любые страницы, которые имеют большое количество Cобытий страницы (имеется в виду, часто посещаемых), и высокое значение среднего времени работы приложения. Я, кажется, помню, как Джоэл Калман однажды сказал, что все, что выше 0,5 секунды, должно быть пересмотрено. Конечно, это довольно грубое обобщение и, может (не) относиться к вашему конкретному случаю использования APEX.

Монитор активности позволяет легко работать с IR, но если вам нужно немного больше деталей и вы хотите запускать отчеты в разных рабочих пространствах, вы можете использовать следующий запрос в SQL Developer, чтобы сделать его максимально детализированным:

Итак, вы, возможно, нашли медленную страницу. И что теперь?

Запуск отчета тогда даст результат:

Одна вещь, которая мне нравится в этой функции, — это не обязательно то, что она определяет, какие регионы требуют много времени для запуска (я могла получить это из моего окна отладки, см. ниже), но она предлагает информацию конечным пользователям.

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

4. Запустите страницу в режиме отладки

Еще лучше, запустите его в Debug LEVEL9, чтобы получить доступ к плану выполнения вашего отчета.

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

5. Остерегайтесь вызова v(”)

Если вы нашли плохо выполняющийся отчет, вы можете проверить, использовали ли вы нотацию v (”), когда вы могли бы использовать переменную bind.

В большой таблице разница между двумя операторами может быть огромной, потому что v (”) на самом деле является вызовом функции. Это означает, что вы не будете использовать преимущества каких-либо индексов, и запрос приведет к полному сканированию таблицы.

Совет: если вам нужно сослаться на состояние сеанса APEX в представлении (где вы не можете использовать переменные привязки), рассмотрите возможность использования скалярного подзапроса, который может отработать почти так же хорошо, как ваша переменная привязки. Смотрите:

Спасибо Джону Скотту за то, что он предложил это на одном из мероприятий, на котором я была.

6. Избегайте подстановки строк в запросах, если это возможно

Остерегайтесь строк подстановки в запросах.

Рассмотрим, например, ситуацию, когда вам может потребоваться оператор decode или case в запросе, чтобы определить, на какую страницу вы хотите ветвиться:

Использование переменной привязки :SESSION, а не строки подстановки &SESSION. может иметь огромное значение и сэкономить Oracle много времени на парсинг. Версия переменной bind позволяет Oracle повторно использовать запрос.

Если вам интересно узнать больше об этом, посмотрите замечательное видео Хорхе Римбласа о строках подстановки, переменных привязки и ссылках APEX.

7. Используйте декларативные параметры в ваших условиях, когда это возможно

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

8. Используйте настройки разбиения отчетов на страницы рационально

В больших отчетах выбранные параметры разбиения на страницы могут оказать существенное влияние. Несмотря на то, что с версии 18.1 APEX значительно улучшил обработку разбиения на страницы (почитайте этот замечательный пост Карстена Чарски), параметр «диапазон строк от X до Y из Z» в одном из них вы можете отключить, если у вас возникли проблемы с производительностью в очень большом отчете.

9. Избегайте HTML в запросах и используйте HTML выражение

Когда это возможно, используйте атрибут выражения HTML для столбцов отчета, чтобы включить в него любые HTML/CSS-атрибуты, которые могут понадобиться.

10. Воспользуйтесь преимуществами кэширования регионов

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

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

Если это так, вы можете воспользоваться параметром кэширования региона.

Параметры кэша сервера в регионах APEX.

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

Осторожно, если у вас есть конфиденциальные данные, которые зависят от пользователя, вы можете выбрать параметры «кэш по пользователю» или даже «кэш по сеансу».

Доступные настройки при включении кэширования регионов

Если вы решите включить кэширование, вы можете сообщать своим пользователям о последнем обновлении данных. Если это так, вы можете использовать функцию APEX_UTIL.CACHE_GET_DATE_OF_REGION_CACHE.

11. Переместите PL / SQL в пакеты

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

Ваши страничные процессы должны быть просто вызовами пакетов, когда это возможно.

Стивен Фойерштейн написал очень подробную статью о написании PL/SQL для Oracle Application Express, которую вы, возможно, захотите почитать. Ей уже несколько лет, но она все еще актуальна!

12. Запустите Advisor!

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

13. Используйте параметры сборки для отключения и включения компонентов

Хорошо, вы перепробовали ВСЕ ЭТИ ВЕЩИ, но все еще застряли непонятно на чем. Если вы собираетесь «перекроить свою страницу заново», то следует рассмотреть возможность использования параметров сборки для различных компонентов вашей страницы.

Не ставьте бессрочные условия на компоненты, иначе вы потеряете все свои драгоценные условия! К тому же, бессрочные компоненты имеют неприятную особенность жить в ваших приложениях вечно…

Создайте новый параметр сборки, используя Status: Exclude.

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

14. Разберитесь в том, как различные настройки IR могут повлиять на производительность APEX

С плохо выполняемым интерактивным отчетом различные настройки, параметры или фильтры, применяемые вашими пользователями, могут усилить плохую работу (да, это действительно так).

Как упоминалось ранее, лучше всего начать с использования режима отладки LEVEL9 и настроить отдельный реальный запрос. Изучите план выполнения, исследуйте индексы, настраивайте там, где это возможно. Помните, что каждое представление (таблица, группировка, диаграмма, сводка) — это отдельный запрос, который может потребовать настройки. Затем он снова изменяется при использовании фильтра поиска или фильтра заголовка столбца!

Ваша настройка MaxRowCount также вступает в игру, и стоит попытаться сделать ее как можно меньше. На это нет правильного ответа, и вам, возможно, придется играть с разными числами, прежде чем вы получите что-то, что работает для ваших пользователей.

В принципе, рассмотрите возможность удаления или изменения некоторых сценариев поведения, заданных по умолчанию. У разработчиков есть много средств управления, когда речь заходит о том, какие функции включены для пользователей. Стоит попробовать различные настройки, и не забудьте проконсультироваться с вашими пользователями, чтобы убедиться, что вы полностью поняли их требования.

Наконец, если вы обнаружите, что ваш IR все еще слишком медленный, вы можете рассмотреть 2 альтернативы:

Конвейерные табличные функции (select * from table (my_rpt_pipelined)) — > они могут работать намного лучше в сложных запросах.
Создание отчета по коллекции (APEX_COLLECTION)

15. Трассировка

Когда все остальное не удается, вы можете добавить "&p_trace=YES " в конец вашего URL-адреса, чтобы создать файл трассировки, который вы сможете проанализировать с помощью утилиты TKPROF.

Дополнительную информацию о трассировке SQL можно найти здесь.

Я не человек базы данных, точно, и большая часть моей работы с БД была с MySQL, поэтому простите меня, если что-то в этом вопросе невероятно наивно.

мне нужно удалить 5.5 миллионов строк из таблицы Oracle, которая имеет около 100 миллионов строк. У меня есть все идентификаторы строк, которые мне нужно удалить во временной таблице. Если бы это было всего несколько тысяч строк, я бы сделал следующее:

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

прежде всего - это делает то, что я думаю, что это-пакетные коммиты 200,000 за раз? Предполагая, что это так, я все еще не уверен, лучше ли генерировать 5,5 миллиона SQL-операторов и фиксировать партиями по 200 000 или иметь один SQL-оператор и фиксировать все сразу.

идеи? Передовая практика?

редактировать: я запустил первый вариант, оператор single delete, и он только потребовалось 2 часа, чтобы завершить разработку. Исходя из этого, он находится в очереди на запуск в производство.

первый подход лучше, потому что вы даете оптимизатору запросов четкое представление о том, что вы пытаетесь сделать, вместо того, чтобы пытаться скрыть его. Компонент database engine может использовать другой подход к внутреннему удалению 5.5 m (или 5.5% таблицы), чем к удалению 200k (или 0.2%).

вот также статьи о массовом удалении в Oracle, которое вы можете прочитать.

самый быстрый способ создать новый с CREATE TABLE AS SELECT используя . Я имею в виду:

конечно, вы должны воссоздать ограничения без проверки, индексы с nologging, гранты. но очень очень быстро.

если вы имеете тревогу в продукции, то вы можете сделать следующее:

вы должны позаботиться о:

хранимые процедуры могут быть признаны недействительными, но они будут перекомпилированы при втором вызове. Вы должны это проверить.
NOLOGGING означает, что минимальный создаются повторы. Если у вас есть роль DBA, запустите ALTER SYSTEM CHECKPOINT чтобы гарантировать отсутствие потерянных данных при сбое экземпляра.
на NOLOGGING табличное пространство должно быть также в NOLOGGING .

другой вариант лучше, чем создавать миллионы вставки:

UPDATE: почему я могу гарантировать, что последний блок PLSQL будет работать? Потому что я предполагаю, что:

никто другой не использует эту временную таблицу по какой-либо причине (dba или задания, собирающие статистику, задачи dab, такие как перемещение, вставка записей и т. д.). Это можно обеспечить потому что вспомогательная таблица только для этот.
затем, с последним утверждением, запрос будет выполнен ровно С тем же планом и собирается вернуть строки с тем же порядком.

при выполнении массовых удалений в Oracle , убедитесь, что у вас не заканчивается UNDO SEGMENTS .

при выполнении DML , Oracle сначала записывает все изменения в REDO log (старые данные вместе с новыми данными).

когда REDO журнал заполняется или происходит тайм-аут, Oracle выполняет log synchronization : он пишет new data в файлы данных (в вашем случае отмечает блоки файлов данных как свободные) и записывает старые данные в UNDO табличное пространство (так что он остается видимым для параллельных транзакций, пока вы commit ваши изменения).

когда вы фиксируете свои изменения, пробел в UNDO сегменты, занятые транзакцией yuor, освобождаются.

это означает, что если удалить 5M строки данных, вам нужно будет иметь место для all эти строки в свой UNDO сегменты, чтобы данные могли быть перемещены туда первыми ( all at once ) и удаляется только после фиксации.

это также означает, что параллельные запросы (если таковые имеются) необходимо будет прочитать из REDO журналы или UNDO сегментов при выполнении сканирования таблицы. Это не самый быстрый способ доступа к данным.

это также означает, что если оптимизатор будет выбирать HASH JOIN для вашего запроса на удаление (что он, скорее всего, сделает), и временная таблица не будет вписываться в HASH_AREA_SIZE (что, скорее всего, будет так), тогда запрос будет нужен several просматривает над большой таблицей, и некоторые из частей таблицы будут уже переехал в REDO или UNDO .

учитывая все сказанное выше, вам, вероятно, лучше удалить данные в 200,000 куски и зафиксировать изменения между ними.

таким образом, Вы, во-первых, избавиться от проблем, описанных выше, и, во-вторых, оптимизировать свой HASH_JOIN , поскольку у вас будет такое же количество чтений, но сами чтения будут более эффективными.

в вашем случае, однако, я бы попытался заставить оптимизатор использовать NESTED LOOPS , as Думаю, в твоем случае это будет быстрее.

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

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

сравните его со следующим:

, посмотрите, что быстрее и придерживайтесь этого.

лучше делать все сразу, как в первом примере. Но я определенно пройдусь по нему с вашим DBA, так как они могут захотеть вернуть блоки, которые вы больше не используете после чистки. Кроме того, могут быть проблемы планирования, которые обычно не видны с точки зрения пользователя.

если исходный SQL занимает очень много времени, некоторые параллельные SQL могут работать медленно, так как они должны использовать UNDO для восстановления версии данных без незафиксированных изменений.

компромисс может быть что-то вроде

вы можете настроить ROWNUM по мере необходимости. Меньший ROWNUM означает более частые коммиты и (возможно) снижение влияния на другие сеансы с точки зрения необходимости применения отмены. Однако, в зависимости от планов выполнения, могут быть и другие последствия, и это вероятно, потребуется больше времени в целом. Технически " FOR " часть цикла не нужна, так как выход завершит цикл. Но я параноик о неограниченных петлях, так как это боль, чтобы убить сеанс, если они застряли.

Я бы рекомендовал запустить это как одно удаление.

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

вам могут понадобиться некоторые способы проверить ход удаления по мере его выполнения. См.Как проверить базу данных oracle на длительный срок запросы?

Как предложили другие люди, если вы хотите проверить воду, вы можете поместить: rownum

Я сделал что-то подобное в прошлом с Oracle 7, где мне пришлось удалить миллионы строк из тысяч таблиц. По всем характеристикам и особенно больших удалений (миллион строк, плюс в одной таблице) этот скрипт работал хорошо.

вам нужно будет немного изменить его (т. е. изучить пользователей/пароли, а также получить правильные сегменты отката). Также вам действительно нужно обсудить это с вашим DBA и запустить его в тестовой среде. Сказав Все это, довольно легкий. Функция delete_sql() ищет пакет rowids в указанной таблице, а затем удаляет их пакет за пакетом. Например:

приведенный выше пример удаляет 500 записей одновременно из таблицы MSF170 на основе инструкции sql.

Если вам нужно удалить данные из нескольких таблиц, просто включают дополнительные exec delete_sql(. ) строки в файле delete-tables.в SQL

О и не забудьте поставить свои сегменты отката обратно в интернет, это не в сценарии.

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

все ответы здесь отличные, только одна вещь, чтобы добавить: если вы хотите удалить все записей в таблице, а конечно вам не понадобится откат, тогда вы хотите использовать усечь таблицу.

(в вашем случае вы хотели удалить только подмножество, но для тех, кто скрывается с подобной проблемой, я думал, что добавлю это)

Следуйте за нами на нашей фан-странице, чтобы получать уведомления каждый раз, когда появляются новые статьи. Facebook

1- Введение

Вы смотрите инструкцию программирования Oracle APEX 5.0. Это 2-ая статья, которая за статьей:

В данной статье я продолжу с Oracle APEX Database Desktop Application, используя Tabular Form.

2- Создать Tabular Form используя Wizard

Войти в систему Oracle APEX с аккаунтом программиста.

Далее войдите в "Hello Database Desktop Application" который вы создали в предыдущей статье инструкции.

В данном приложении имеется несколько страниц созданных с предыдущей статьи инструкции. Нажмите на "Create Page" чтобы создать другую страницу.

Ваша веб страница создана, нажмите на RUN чтобы запустить созданную страницу.

Здесь, поставленным вопросом является как поменять некоторые поля ввода (Input Fields) на SELECT LIST или POPUP LOV (List of values). Например: вы хотите выбрать Manager (MGR) через POPUP LOV, и выбрать Job через SELECT LIST.

Вернуться к экрану дизайна страницы 7. Настроить атрибуты для столбца JOB чтобы он отображался как список выбора (SELECT LIST).

Type: Select List

Type: Static Value
Static Value:

Синтаксис объявления статистических значений (Static Values):

Далее вам нужно настроить атрибуты для поля MGR чтобы он отображался в виде POPUP LOV (List of values)

Type: Popup LOV (shows display values)

Type: SQL Query
SQL Query: Select emp.ename, emp.empno from Emp

Индентично настройте атрибуты для DEPTNO, Чтобы он отображался как POPUP LOV.

Type: Popup LOV (shows display values)

Type: SQL Query
SQL Query: Select d.dname, d.deptno from Dept d

3- Создать Tabular Form вручную

Выше я показал вам как создать Tabular Form используя wizard в Oracle Apex. Далее мы создадим Tabular Form с самого начала, не используя Wizard, это поможет вам лучше понять про Tabular Form & Oracle APEX.

Создать Tabular Form в регионе "Content Body":

Вы можете протестировать страницу 8, полученные результаты:

Добавьте столбец содержащий CheckBox, который используется для выбора строк.

Настроить атрибуты для EMPNO, скрыть этот столбец, и настроить для него атрибуты primary key.

Column Name: EMPNO
Type: Hidden Column (saves state)

Type: Existing sequence
Name: EMP_SEQ

Column Name: ENAME
Type: Text Field

Column Name: MGR
Type: Popup LOV (shows display values)

Type: SQL Query
Static Values: Select emp.ename, emp.empno from Emp

Column Name: HIDEDATE
Type: Date Picker

Format Mask: YYYY-MM-DD

Type: PL/SQL Expression
PL/SQL Expression: sysdate

Column Name: SAL
Type: Text Field

Столбец SAL (Salary) это числовые данные, поэтому вам нужно добавить проверку:

Column Name: COMM
Type: Text Field

Создать Validation для COMM, COMM должен иметь числовое значение.

Type: Popup LOV (shows display values)

Type: SQL Query
SQL Query: Select d.dname, d.deptno from Dept d

4- Обработка Tabular Form

Далее нам нужно добавить обработки включая создание, изменение, удаление записей.

Изменить названия Button, как в изображении ниже:

Когда пользователь нажимает на кнопку Cancel, веб страница refresh (обновит) настоящую страницу (Здесь это страница 8).

4.1- Обработка добавления записи

Когда пользователь нажимает на кнопку ADD, table на странице автоматически добавит новую строку, позволяющую пользователю вводить данные. Вам нужно вызвать функцию javascript чтобы выполнить это.

Button Name: ADD
Label: Add

Action: Redirect to URL
Target: javascript:apex.widget.tabular.addRow();

4.2- Обработка сохранения строк данных

Кнопка SAVE должна выполнять обязанность сохранения информации разных записей одновременно, включая новые записи, измененные записи. Вам нужно объявить процесс (Process) для обработки. Этот Process будет сразу запущен после того как вызвана страница, он update (обновит) записи с изменениями и insert .

4.3- Обработка немедленного удаления нескольких строк

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

Чтобы удалить много строк вам нужно создать процесс (Process) для данной обработки. Этот Process будет выполнен один раз сразу при вызове веб страницы.

4.4- Обработка удаления многих строк - Запрос перед удалением

Перед тем, как удалить записи, программа спрашивает согласен ли пользователь удалить или нет, перед тем как выполнить команду удаления.

apex.confirm(. ) это функция Javascript использующаяся для того, чтобы спросить пользователя перед тем как выполнить команду button.

Чтобы удалить много строк вам нужно создать процесс (Process) обработки. Этот Process будет выполнен один раз сразу при вызове веб страницы.

PL/SQL предоставляет для создаваемых вами коллекций множество встроенных функций и процедур, называемых методами коллекций. Эти методы предназначены для получения информации о содержимом коллекции и ее изменения. Их полный список приведен в табл. 1.

Метод (функция или процедура)	Описание
COUNT (функция)	Возвращает текущее значение элементов в коллекции
DELETE (процедура)	Удаляет из коллекции один или несколько элементов. Уменьшает значение, возвращаемое функцией COUNT, если заданные элементы еще не удалены. Со структурами VARRAY может использоваться только для удаления всего содержимого
EXISTS (функция)	Возвращает значение TRUE или FALSE , определяющее, существует ли в коллекции заданный элемент
EXTEND (процедура)	Увеличивает количество элементов во вложенной таблице или VARRAY , а также значение, возвращаемое функцией COUNT
FIRST, LAST (функции)	Возвращают индексы первого ( FIRST ) и последнего ( LAST ) элемента в коллекции
LIMIT (функция)	Возвращает максимальное количество элементов в массиве VARRAY
PRIOR, NEXT (функции)	Возвращают индексы элементов, предшествующих заданному ( PRIOR ) и следующему за ним ( NEXT ). Всегда используйте PRIOR и NEXT для перебора коллекций, особенно при работе с разреженными (или потенциально разреженными) коллекциями
TRIM (функция)	Удаляет элементы, начиная с конца коллекции (элемент с наибольшим индексом)

Все эти конструкции называются методами, потому что синтаксис их вызова отличается от синтаксиса вызова обычных процедур и функций. Это типичный синтаксис вызова методов, используемый в объектно-ориентированных языках программирования — таких, как Java.

Рассмотрим синтаксис вызова методов на примере LAST . Эта функция возвращает наибольший индекс элемента ассоциативного массива. Стандартный вызов этой функции выглядел бы так:

Иначе говоря, ассоциативный массив передается ей в качестве аргумента. Но поскольку функция LAST является методом, она «принадлежит» объекту — в данном случае ассоциативному массиву. Правильный синтаксис ее вызова выглядит так:

В общем случае синтаксис вызова методов ассоциативного массива выглядит так:

Операция, не требующая передачи аргументов:

Операция, аргументами которой являются индексы элементов:

Например, следующая команда возвращает TRUE , если в ассоциативном массиве company_tab определена запись 15:

Методы коллекций недоступны из SQL; их можно использовать только в программах PL/SQL.

Метод COUNT

Метод COUNT возвращает количество элементов в ассоциативном массиве, вложенной таблице или массиве VARRAY . В значении не учитываются элементы, удаленные из коллекции методом DELETE или TRIM .

Синтаксис вызова:

Рассмотрим пример. Прежде чем что-либо делать с коллекцией, мы проверяем, содержит ли она хотя бы один элемент:

Граничные условия

Для инициализированной коллекции, не содержащей ни одного элемента, COUNT возвращает нуль. Это же значение возвращается при вызове COUNT для пустого ассоциативного массива.

Возможные исключения

При вызове метода COUNT для неинициализированной вложенной таблицы или VARRAY инициируется заранее определенное исключение COLLECTION_IS_NULL . Такое исключение не может возникнуть при работе с ассоциативными массивами, не требующими инициализации.

Метод DELETE

Метод DELETE предназначен для удаления одного, нескольких или всех элементов ассоциативного массива, вложенной таблицы или массива VARRAY . При вызове без аргументов он удаляет все элементы коллекции. Вызов DELETE (i) удаляет i-й элемент вложенной таблицы или ассоциативного массива. А вызов DELETE ( i,j ) удаляет все элементы с индексами от i до j включительно. Если коллекция представляет собой ассоциативный массив, индексируемый строками, i и j должны быть строковыми значениями; в противном случае они являются целыми числами.

При вызове с аргументами метод резервирует место, занимавшееся «удаленным» элементом, и позднее этому элементу можно присвоить новое значение.

Фактически PL/SQL освобождает память лишь при условии, что программа удаляет количество элементов, достаточное для освобождения целой страницы памяти. (Если же метод DELETE вызывается без параметров и очищает всю коллекцию, память освобождается немедленно.)

Применительно к массивам VARRAY метод DELETE может вызываться только без аргументов. Иначе говоря, с помощью указанного метода из этой структуры нельзя удалять отдельные элементы, поскольку в таком случае она станет разреженной, что недопустимо. Единственный способ удалить из VARRAY один или несколько элементов — воспользоваться методом TRIM , предназначенным для удаления группы расположенных рядом элементов, начиная с конца коллекции.

Следующая процедура удаляет из коллекции все элементы, кроме последнего. В ней используются четыре метода: FIRST — для получения номера первого удаляемого элемента; LAST — для получения номера последнего удаляемого элемента; PRIOR — для определения номера предпоследнего элемента; DELETE — для удаления всех элементов, кроме последнего:

Несколько дополнительных примеров:

Удаление всех строк из таблицы names :

Удаление 77-й строки из таблицы globals :

Удаление из таблицы temp_reading всех элементов, начиная с индекса –15 000 и до индекса 0 включительно:

Граничные условия

Если значения индексов i и/или j указывают на несуществующие элементы, DELETE пытается «сделать наилучшее» и не генерирует исключение. Например, если таблица содержит три элемента с индексами 1, 2 и 3, то вызов метода DELETE (–5,1) удалит только один элемент с индексом 1, а вызов DELETE (–5) не изменит состояния коллекции.

Возможные исключения

Вызов метода DELETE для неинициализированной вложенной таблицы или массива VARRAY инициирует исключение COLLECTION_ IS_NULL .

Метод EXISTS

Метод EXISTS используется с вложенными таблицами, ассоциативными массивами и массивами VARRAY для определения наличия в коллекции заданного элемента. Если таковой имеется, метод возвращает значение TRUE , а если отсутствует — значение FALSE . Значение NULL не возвращается ни при каких условиях. Кроме того, EXISTS возвращает FALSE и в том случае, если заданный элемент был удален из коллекции с помощью метода TRIM или DELETE .

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

Граничные условия

Возможные исключения

Метод EXISTS не инициирует исключения.

Метод EXTEND

Чтобы добавить элемент во вложенную таблицу или массив VARRAY , необходимо сначала выделить для него область памяти. Соответствующая операция, не зависящая от присваивания элементу значения, выполняется методом EXTEND . Следует помнить, что указанный метод неприменим к ассоциативным массивам.

Метод EXTEND , как уже было сказано, добавляет элементы в коллекцию. При вызове без аргументов он добавляет один элемент со значением NULL . Вызов EXTEND ( n ) присоединяет n элементов со значением NULL , а вызов EXTEND ( n,i ) — n элементов, и всем им присваивает значение i -го элемента. Последняя форма метода применяется к коллекциям, для элементов которых задано ограничение NOT NULL .

Синтаксис перегруженного метода EXTEND :

В следующем примере процедура push добавляет в список один элемент и присваивает ему новое значение:

В другом фрагменте кода метод EXTEND используется для включения в коллекцию 10 новых элементов с одинаковыми значениями. Сначала в коллекцию добавляется один элемент, которому явно присваивается нужное значение. При повторном вызове метода EXTEND в коллекцию добавляется еще 9 элементов, которым присваивается значение первого элемента коллекции new_value :

Граничные условия

Если параметр n имеет значение NULL , метод не выполнит никаких действий.

Возможные исключения

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

Методы FIRST и LAST

Методы FIRST и LAST возвращают соответственно наименьший и наибольший индексы элементов вложенной таблицы, ассоциативного массива или массива VARRAY . Для ассоциативных массивов, индексируемых строками, эти методы возвращают строки; «наименьшее» и «наибольшее» значения определяются порядком набора символов, используемого данным сеансом. Для других типов коллекций методы возвращают целые числа.

Синтаксис этих функций:

Так, следующий фрагмент перебирает все элементы коллекции от начала к концу:

Запомните, что такой цикл будет выполнен корректно (то есть не породит исключения NO_DATA_FOUND ) лишь при условии, что коллекция является плотной.

В следующем примере для добавления элементов в конец ассоциативного массива используется оператор COUNT . Цикл FOR с курсором используется для копирования данных из базы в ассоциативный массив. При выборке первой записи коллекция companies пуста, поэтому COUNT возвращает 0.

Граничные условия

Если методы FIRST и LAST вызываются для инициализированных коллекций, не содержащих ни одного элемента, они возвращают NULL . Для массива VARRAY , всегда содержащего хотя бы один элемент, FIRST всегда возвращает 1, а LAST — то же значение, что и метод COUNT .

Возможные исключения

При вызове методов FIRST и LAST для неинициализированной вложенной таблицы или массива VARRAY инициируется исключение COLLECTION_ IS_NULL.

Метод LIMIT

Метод LIMIT возвращает максимальное количество элементов, которое можно определить в массиве VARRAY . В случае вложенной таблицы или ассоциативного массива он возвращает NULL . Синтаксис этого метода:

В следующем примере перед добавлением нового элемента в конец массива VARRAY мы сначала проверяем, есть ли в нем еще свободное место:

Граничные условия

У метода LIMIT граничных условий не существует.

Возможные исключения

Вызов метода LIMIT для неинициализированной вложенной таблицы или массива VARRAY генерирует исключение COLLECTION_ IS_NULL .

Методы PRIOR и NEXT

Методы PRIOR и NEXT используются для перемещения по коллекциям — вложенным таблицам, ассоциативным массивам и массивам VARRAY . Метод PRIOR возвращает индекс предыдущего, а метод NEXT — следующего элемента коллекции. Следующая функция возвращает сумму чисел, хранящихся в коллекции list_t :

Та же программа, но с перебором элементов от последней к первой определенной записи коллекции:

В данном случае направление перемещения не имеет значения, но в других программах оно может повлиять на результат обработки.

Граничные условия

Методы PRIOR и NEXT для инициализированной коллекции, не содержащей ни одного элемента, возвращают NULL . Если значение i больше или равно COUNT , метод NEXT возвращает NULL ; если i меньше или равно FIRST , метод PRIOR возвращает NULL .

Вплоть до версии Oracle12c, если коллекция не пуста, а параметр i больше или равен COUNT , метод PRIOR возвращает LAST ; если параметр i меньше FIRST , метод NEXT возвращает FIRST . Однако сохранение такого поведения в будущих версиях Oracle не гарантировано.

Возможные исключения

Вызов методов PRIOR и NEXT для неинициализированной вложенной таблицы или массива VARRAY генерирует исключение COLLECTION_IS_NULL .

Метод TRIM

Метод TRIM удаляет n последних элементов коллекции — вложенной таблицы или массива VARRAY . Если метод вызывается без аргументов, он удалит только один элемент. Как упоминалось ранее, при совместном использовании методов TRIM и DELETE возможна накладка: если заданный в вызове метода TRIM элемент был уже удален методом DELETE , метод TRIM «повторит» удаление, но считает его частью n, поэтому количество реально удаленных элементов окажется меньшим, чем вы рассчитывали.

Попытка вызова метода TRIM для ассоциативного массива приведет к ошибке компиляции.

Синтаксис метода TRIM :

Следующая функция извлекает из списка последнее значение и возвращает его вызывающему блоку. Операция извлечения реализуется как выборка значения с последующим усечением коллекции на один элемент:

Граничные условия

Если значение n равно NULL , метод не выполнит никаких действий.

Возможные исключения

При попытке удалить больше элементов, чем имеется в коллекции, инициируется исключение SUBSCRIPT_BEYOND_COUNT . Если метод TRIM вызывается для неинициализированной вложенной таблицы или массива VARRAY , инициируется исключение COLLECTION_IS_NULL .

Вызывая методы TRIM и DELETE для одной и той же коллекции, можно получить неожиданные результаты. На сколько элементов станет меньше в коллекции, если удалить последний элемент методом DELETE , а затем вызвать метод TRIM с тем же значением параметра? Казалось бы, это приведет к удалению двух элементов, но в действительности оба метода удалят один и тот же элемент. Чтобы избежать накладок, компания Oracle рекомендует использовать только один из этих двух методов при работе с конкретной коллекцией.

Читайте также: