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

Я пытаюсь вставить тестовые данные указанным способом, но получаю ошибку, что добавляемые данные имеют большую длину, чем длина колонки. Что порекомендуете в этом случае?

Следует воспользоваться функций SUBSTR для усечения записей из запроса до необходимого размера.

В нашей таблице три колонки, я добавляю данные только в две из них, возможно ли такое?

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

1. Добавить в таблицу MAN1 записи из MAN, где возраст людей больше 33 лет.

2. Добавить в таблицу MAN1 записи из MAN, где возраст людей больше 50 лет, и к фамилии участника добавить количество лет — пример «Петров54».

3. Добавить в таблицу MAN1 записи из MAN, где возраст людей 19, но к полю «возраст» (AGE) необходимо добавить AGE +1.

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

Создание таблицы и заполнение таблицы на основе запроса.

Создание таблицы:

Создается таблица по запросу SELECT.

Создание дубликата таблицы AUTO.

Пример:

Создание таблицы AOBJ1 — первые четыре строки из таблицы AUTO.

Пример:

Создание таблицы AOBJ2 — первыми добавлены первые две строки из таблицы AUTO.

Пример:

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

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

CREATE TABLE TAB1 AS SELECT * FROM TAB2 WHERE 1=0

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

Этот метод работает со всеми типами колонок, в том числе и с колонками BLOB, CLOB.

Нужны ли специальные права для создания таблиц?

Для создания в своей схеме не нужны. Для создания в другой схеме необходима привилегия CREATE ANY TABLE.

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

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

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

Для транспонирования таблицы с группировкой в ORACLE SQL используется специальный оператор PIVOT.

Синтаксис конструкции:

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

СОЗДАДИМ ДЕМО-ТАБЛИЦУ.

Условимся, что перечень значений свойств был ограничен.

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

И перепишем уже запрос с использованием PIVOT.

Рисунок 143. Запрос с группировкой: демонстрация PIVOT

Проделаем то же самое с таблицей AUTO.

Рисунок 144. Демонстрация использования AUTO

Удалим тестовую таблицу t1.

Для функции PIVOT существует обратная функция UNPIVOT — для преобразования строки запроса в столбец.

Какие агрегатные функции мы можем использовать в конструкции PIVOT?

Такие же, как и в запросах с группировкой GROUP BY. Это могут быть SUM, COUNT, MIN, MAX, AVG.

Как задать свои наименования столбцов?

После оператора FOR можно задавать свои наименования столбцов через инструкцию AS.

Пример:

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

2. Повторите с использованием PIVOT запрос с марками авто AUTO (MARK).

Язык SQL позволяет программировать специальные последовательности целых чисел (итераторы), которые могут использоваться в запросах.

Создание итераторов реализуется с помощью конструкции CONNECT BY.

Синтаксис

В переменную LEVEL будут выведены последовательно значения от 1 до N.

Простой пример: выведем в запросе все числа от 1 до 100.

То есть специальное ключевое слово LEVEL выполняет роль последовательности от 0 до ограничения LEVEL <101.

Рисунок 145. Демонстрация работы итератора

Пример итератора Connect By для работы с датами на ORACLE SQL.

Вывести календарь на следующие 30 дней.

Здесь level применяется одновременно с функцией SYSDATE.

Рисунок 146. Использование итераторов

Вывести календарь на следующие 30 дней, без субботы и воскресенья.

Рисунок 147. Использование итераторов

Вывести числа от 1 до 100, кроме чисел, которые делятся на 3 нацело.

Рисунок 148. Использование итератора MOD

Данную конструкцию итератора CONNECT BY можно использовать в сочетании с другими запросами SQL, например:

Рисунок 149. Использование итератора

Иногда это может быть удобно и востребовано на практике.

Я пишу следующий запрос: SELECT level FROM DUAL CONNECT BY (Level <11 and level> 5), но результат неправильный, выводится только одна строка, почему так происходит?

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

Попробуйте сделать следующим образом: