Максим Чалышев - Oracle SQL. 100 шагов от новичка до профессионала

Подобное использование WITH, подобный механизм используется в СУБД ORACLE начиная с версии 12С.

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

Можно ли использовать в WITH не только функции, но и процедуры?

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

Пример, как это сделать:

В этом примере из функции MULT вызывается процедура prcplus, а сама функция MULT используется в запросе.

Можно ли использовать WITH совместно с командами обновления данных UPDATE?

В данном запросе обновляется возраст людей из MAN1 (+1) с использованием функции PLUS ().

1. Повторить запросы из данного шага.

2. Написать WITH запрос-функцию, вычисляющую количество авто каждого цвета из AUTO, вывести результат: марку машины, количество машин данной марки.

3. Написать WITH запрос-функцию, добавляющую к цвету авто из таблицы AUTO слово «цвет». Результат: BMW цвет белый.

4. Написать WITH запрос-функцию, умножающую PEOPLES на 1000 из таблицы CITY. Результат: Москва 100 000.

5. Написать запрос-функцию из таблицы MAN, объединяющую имя и фамилию.

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

Простой способ сгруппировать данные в одном запросе.

В ORACLE существует системное представление ALL_OBJECTS.

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

Напишем запрос:

Рисунок 183. Запрос к ALL_OBJECTS: использование для группировок

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

Обратимся к нашей схеме.

Посчитаем количество авто BMW, LADA и прочих одним запросом.

Рисунок 184. Запрос к AUTO: использование для группировок

В подобных запросах следует учитывать пустые значения ячеек таблицы.

Можно ли использовать другие агрегатные функции, например SUM?

Вот пример: переделаем запрос с использованием агрегатной функции SUM.

1. Повторите материалы данного шага.

2. Напишите запрос, который бы подсчитал количество авто синего, красного и остальных цветов из таблицы AUTO. Используйте DECODE.

3. Напишите запрос, который бы подсчитал людей, которые купили авто, которые не купили авто — в другой колонке, из таблицы MAN. Используйте DECODE.

В ORACLE SQL есть возможность преобразовать результат запроса в CSV-строку или в любую другую заданную строку с разделителем.

Функция LISTAGG объединяет значения заданного поля таблицы для каждой группы в строку, через указанный разделитель.

Здесь ORDER_BY_CLAUSE — сортировка, колонка, по которой будет отсортирован результат;

OVER PART — сегмент разделения данных.

Разберем на примере — создадим таблицу и заполним ее данными.

Запрос для примера:

Рисунок 185. Запрос к CL_PHONES: использование LISTAGG

Второй пример.

Создадим таблицу по следующему запросу:

Рисунок 186. Запрос к DEMO1: использование LISTAGG

Также LISTAGG может работать как аналитическая функция, то есть объединять данные в строку по заданному сегменту (PARTITION).

Рисунок 187. Запрос к MAN: использование LISTAGG c PARTITION

Как использовать функцию LISTAGG совместно с UNION?

Да, такое возможно. Вот пример:

1. Повторите материалы данного шага.

2. Выведите из таблицы MAN первую букву имени человека (FIRSTNAME) и через точку с запятой все имена (FIRSTNAME), начинающиеся с этой буквы.

3. Выведите из таблицы CITY первую букву города (CITYNAME) и через точку с запятой все города (CITYNAME), начинающиеся с этой буквы.

В ORACLE существует возможность работать с JSON-форматом. JSON — специальный формат обмена данными, основанный на текстовом формате. Как и многие другие текстовые форматы, JSON используется при обмене данными между веб-сервисами.

Проще всего пояснить синтаксис работы JSON в ORACLE на примерах.

Создадим таблицу INFO_USER_V:

В ORACLE 12 существует специальный тип ограничений, чтобы добавлять в колонку только данные JSON-формата и исключить ошибки, связанные с нарушением структуры JSON.

Добавим такую колонку к нашей таблице:

Добавляем записи:

Попробуем извлечь данные из таблиц, которые мы создали.

Рисунок 188. Запрос c использованием JSON_VALUE, JSON_DATA

Рисунок 189. Запрос c использованием JSON_VALUE, JSON_DATA

Еще один способ:

Рисунок 190. Запрос c использованием JSON_TABLE

Рисунок 191. Запрос c использованием JSON_TABLE, JSON_DATA

Работа с JSON возможна только в версии ORACLE начиная с 12c.

Можно ли создать представление на основание таблицы json?

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

Оператор MODEL в ORACLE SQL позволяет определенным образом эмулировать работу с электронными таблицами. Оператор MODEL имеет сложный синтаксис, но вместе с тем предоставляет более гибкие способы работы с данными.

Оператор SQL MODEL позволяет рассматривать результат запроса как многомерный массив.

При этом в SQL задаем оси измерения этого массива (идентифицируем данные по осям).

Использование MODEL также позволит нам подводить промежуточные и общие итоги с применением агрегатных функций.