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

Вот пример подобного запроса:

Может ли быть использован WITH совместно с UNION?

Да, например таким образом:

В SQL диалекте ORACLE 12C есть возможность определить функцию или процедуру на языке PL/SQL и использовать с помощью оператора WITH, как обычный SQL.

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

Примеры

Вывести на экран марки автомобиля из таблицы AUTO.

Используемая функция REVERSIVE_FNC выводит слово справа налево по буквам.

Рисунок 181. Использование WITH и FUNCTION

Еще пример. Добавить к идентификатору города заданное количество нулей, преобразовать к числу.

Рисунок 182. Использование WITH и FUNCTION

Подобное использование 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 объединяет значения заданного поля таблицы для каждой группы в строку, через указанный разделитель.