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

Синтаксис

Примеры

Подготовим данные.

Таблица (ручки), где PRT — партия поставки, CNT — количество в данной поставке, COLOR — цвет ручек в поставке (red, green, black).

Заполним таблицу данными (партии 1, 2, 3, 4, 7 с различным количеством ручек, разных цветов).

Напишем запрос, демонстрирующий работу MODEL.

Рисунок 192. Результаты запроса: использование MODEL

Немного изменим запрос, добавим сумму.

Рисунок 193. Результаты запроса: использование MODEL

Более интересный пример: подводятся итоги по трем партиям ручек 1…3.

Еще более интересно: дополнительные итоги по цвету ручек!

Рисунок 194. Результаты запроса: использование MODEL и SUM

Кроме суммы, можно использовать и другие агрегатные функции, например AVG.

Рисунок 195. Результаты запроса: использование MODEL

Можно ли создать представление на основе запроса с MODEL?

Да, в представлениях может использован запрос с MODEL.

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

2. На основе таблицы AUTO постройте запрос MODEL, взяв за основу запрос из последнего примера.

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

Мы можем также использовать MODEL для создания массивов.

Создание одномерного массива:

Также функция SQL MODEL может использоваться и для создания итераторов подобно CONNECT BY:

— M1 AA

— 0 A AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

Еще пример: итератор в качестве счетчика, но это более сложный пример. Теперь с конструкцией UNTIL наращиваем итерацию, пока aa [0] не будет равно «AAAAAAAA».

Аналогичный пример, но используем команды PREVIOUS () — перед значением и ITERATION_NUMBER — номер итерации.

Пример простой итерации от 0 до 5:

Сложная последовательность, в которой следующий номер — произведение двух предыдущих;

• с ограничением результата более 8000.

Интереснейший пример с партицированием данных.

Пример с партицированием:

Можно ли использовать запрос MODEL для формирования первичного ключа?

Для формирования первичного ключа необходимо и правильно использовать последовательность SEQUENCE.

1. Внимательно изучите каждый из примеров. Помните, что применение MODEL весьма распространено, очень часто используется при построении кубов в DWH-проектах.

2. Создайте простой итератор с помощью MODEL, от 1 до 100, с шагом 2.

TIMESTAMP — специальный тип данных в СУБД ORACLE, расширяющий возможности типа данных DATE.

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

Тип TIMESTAMP представлен в трех вариантах:

• TIMESTAMP — год, месяц, часы, минуты, секунды, миллисекунды, сотые доли секунд;

• TIMESTAMP WITH LOCAL TIME ZONE — то же, что и предыдущий тип данных, но дополнительно включает еще и следующие значения временных зон: TIME_ZONE_HOUR и TIME_ZONE_MINUTE, или же TIME_ZONE_REGION;

• TIMESTAMP WITH LOCAL TIME ZONE — тип данных со значением часового пояса.

Данные типа TIMESTAMP.

Для работы с типом TIMESTAMP существует специальная функция CURRENT_TIMESTAMP, которая возвращает текущее значение время-дата с данными часового пояса, заданного в параметрах сессии.

У данной функции нет параметров.

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

Рисунок 196. Использование TIMESTAMP

Рисунок 197. Использование TIMESTAMP WITH TIMEZONE

Смещение часового пояса сессии изменилось с -5:0 в -1:0, в результате чего функция CURRENT_TIMESTAMP вернет в качестве значения текущей даты и времени на 4 часа в прошлое.

Рассмотрим примеры преобразования TIMESTAMPв DATE.

Рисунок 198. Использование TIMESTAMP и DATE

Обратное преобразование из DATE в TIMESTAMP:

Рисунок 198. Использование TIMESTAMP и DATE

Более сложные преобразования типа TIMESTAMP.

Примеры более сложных преобразований с типом TIMESTAMP.

TO_TIMESTAMP — преобразование строки к типу TIMESTAMP.

Рисунок 198. Использование TIMESTAMP и DATE

Преобразование временной зоны NEW_TIME:

Рисунок 200. Использование NEW_TIME

Здесь первый параметр — TIMESTAMP, второй — значение временной зоны, из которой преобразуем, третий — значение временной зоны, в которую преобразуем.

Функция EXTRACT позволяет извлечь следующие значения из TIMESTAMP:

Рисунок 201. Использование TO_CHAR и NEW_TIME

Рисунок 202. Использование EXTRACT c TIMESTAMP

При преобразовании из текстового значения в TIMESTAMPнеобходимо правильно задавать формат преобразования.

Функция EXTRACT может быть применена не только к колонкам типа данных TIMESTAMP, но и к обычному типу DATE.

Как прибавить или отнять заданный интервал времени из TIMESTAMP?

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