SQL-разработчик - Часть 2

Andrew.Kaluba · 28.Февраль.2021 12:09:33

11.2 Секционирование таблиц

Откройте инструмент разработчика Oracle SQL Developer, подключитесь к Базе Данных через пользователя HR и напишите несколько запросов.

Задание 1.

Создайте таблицу accountant.cards с месячным секционированием по дате в поле create_date (дата создания карты). Поля у этой таблицы будут: card_number - текст на 16 символа (Номер карты), end_month - число (Месяц окончания работы), end_year - число (Год окончания работы), employee_id - число (Ссылочный идентификатор сотрудника на поле hr.employees.employee_id).

Задание 2.

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

Задание 3.

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

11.3 Подсекции таблиц

Откройте инструмент разработчика Oracle SQL Developer, подключитесь к Базе Данных через пользователя HR и напишите несколько запросов.

Задание 1.

Создайте таблицу seller.maker_history с секционированием и автоматическим созданием новых секций по дате в поле start_date (дата основания компании). Поля у этой таблицы будут: id - число, автоинкрементное поле (Идентификатор производителя), name - текст на 32 символа (Наименование производителя), start_date - дата (Дата начала работы) end_date (Дата ликвидации фирмы).

Задание 2.

Создайте уникальный индекс seller.maker_history_pk на поле id таблицы seller.maker_history (не забудьте включить поле-ключ партиций)

Задание 3.

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

11.4 Удаление и добавление данных в секции

Описание

Вставить данные о выдуманной автомобильной марке в таблицу SELLER.MAKER_HISTORY с датой создания через 7 лет.
Узнать название секции, в которую вставились данные.
Сделать запрос к этой секции.
Удалить данные в этой секции через TRUNCATE.
Вставить в очищенную секцию данные о собственной автомобильной марке.

Откройте инструмент разработчика Oracle SQL Developer, подключитесь к Базе Данных через пользователя HR и напишите несколько запросов.

Задание 1.

Вставьте данные о придуманной автомобильной марке в таблицу seller.maker_history с датой создания через 7 лет от текущей даты.

Задание 2.

Узнайте название секции, в которую вставились данные.

Задание 3.

Сделайте запрос к секции из задания 2.

Задание 4.

Удалите данные только из секции из задания 2 через функцию truncate.

Задание 5.

Вставьте в очищенную секцию данные о (придуманной) собственной автомобильной марке.

11.5 Оптимизатор и методы соединения

Откройте инструмент разработчика Oracle SQL Developer, подключитесь к Базе Данных через пользователя HR и проанализируйте план нескольких запросов.

Задание 1.

Проанализируйте план запроса.

select *

from job_history;

Задание 2.

Проанализируйте план запроса.

select *

from job_history hst

inner join jobs j

    on j.job_id = hst.job_id;

Задание 3.

Проанализируйте план запроса.

select count(1) over

       (partition by emp.department_id 

        order by j.job_title) as cnt

from job_history hst

inner join jobs j

    on j.job_id = hst.job_id

right outer join employees emp

          on j.job_id = emp.job_id;

Задание 4.

Проанализируйте план запроса.

select *

from emp_details_view;

11.6 Оптимизатор и подсказки

Откройте инструмент разработчика Oracle SQL Developer, подключитесь к Базе Данных через пользователя HR и напишите несколько запросов.

Задание 1.

Напишите запрос с соединением таблиц сотрудников, отделов и типов работ.

Задание 2.

Напишите подсказки оптимизатору и посмотрите как они меняют план запроса. Воспользуйтесь следующими хинтами: first_rows, use_hash, use_nl, materialize.

11.7 Домашняя работа

Задача

Цель задания

Потренироваться делать ad hoc выгрузки.

Что нужно сделать

Сегодня, войдя, в офис Вы увидели аналитика из соседнего отдела с горящими глазами и он уже пододвинул свой стул к Вашему.

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

Вы решили начать с чего-то попроще и показали ему план запроса соединения таблиц отделов и сотрудников.
Дальше Вы создали таблицу hr.employee_entrances “входы сотрудников” и секционировали ее по дате в диапазоне месяца.
Потом Вы заполнили эту таблицу данными.
Используя синтаксический сахар сделали подзапросы и соединили новую таблицу с выбркой из пункта 1.
Начали менять подсказки оптимизатора и показывать аналитику как меняются планы запросов. Вы использовали materialize, no_merge, use_hash, parallel, use_nl.
Потом Вы решили показать как меняется план вставки и применили подсказку append при вставке в таблицу сотрудников.

12.2 Вывод текста

Откройте инструмент разработчика Oracle SQL Developer, подключитесь к Базе Данных через пользователя HR и напишите несколько запросов.

Задание 1

Напишите анонимный блок, в котором необходимо вывести в dbms_output сообщение ‘Hello world!’.

Задание 2

Напишите анонимный блок, в котором необходимо вывести в dbms_output сообщение Hello и Ваше имя с помощью конкатенации.

Задание 3

Напишите анонимный блок, который выводил бы вот такое изображение, где в центре - ваше имя.

| |
| Александр |

Оцените урок

12.3 Переменные

Откройте инструмент разработчика Oracle SQL Developer, подключитесь к Базе Данных через пользователя HR и напишите несколько запросов.

Задание 1

Напишите анонимный блок, создайте в нем переменную со своим именем и выведите на экран приветствие с конкатенацией.

Задание 2

Поменяйте переменную на константу и заполните ее другим именем.

Задание 3

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

Напишите запрос, в котором будут выбираться суммы зарплат отдела, а результат выведите на экран вида: ‘Сумма заработных плат отдела ‘||переменная_отдела||’ равняется ‘||переменная_зарплаты||’ долларов’.

12.4 Ветвление IF

Откройте инструмент разработчика Oracle SQL Developer, подключитесь к Базе Данных через пользователя HR и напишите несколько запросов.

Задание 1

Напишите анонимный блок, в котором необходимо вывести в dbms_output сообщение “Hello world!” при условии, что значение числовой переменной равно 5. И после ветвления выведите на экран фразу “I’ve done!”.

Задание 2

Попробуйте поменять значения в условии.

Задание 3

Запустите программу с разными операторами сравнения, например, >, < >=, <=, !=.

12.5 Больше веток и условий

Откройте инструмент разработчика Oracle SQL Developer, подключитесь к Базе Данных через пользователя HR и напишите несколько запросов.

Задание 1

Напишите анонимный блок с выводом случайного значения с ограничением от 0 до 5.

Задание 2

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

Задание 3

Напишите анонимный блок, в котором проверяется округленное случайное значение с ограничением от 0 до 5 на условие, что оно меньше трех. Если это истина, то выводится на экран сообщение “Первый пошел!”, если оно больше двух, то вывести “Второй пошел!”.

В блоке “иначе” нужно вывести на экран сообщение “Пошел, пошел, пошел!”.

12.6 Домашняя работа

Задача

Цель задания

Потренироваться делать ad hoc выгрузки.

Что нужно сделать

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

Ну, или почти никто. Продакт-менеджер из соседнего отдела увидел Вас и рванул через весь офис. Добежал и начал сбивчиво что-то тараторить о том, что программист в их команде заболел, вчера уже с признаками ОРВИ он коммитил всё в GitLab, чтобы передать кому-нибудь все это дело и что релиз назначен на сегодняшний вечер и только Вы их сможете спасти.

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

Исправьте листинг программы.

begin

dbms_ouptut.put_line('Исправления вечны');

end

Исправьте листинг программы.

begin

if round(dbms_random.value(0, 10), 2 <= 3 then

    dbms_output.put_line('БС1')

elif round(dbms_random.value(0 10), 2) = 5 than

    dbms_ouptut.put_line('БС2');

else

    dbms_output.put_line('счёт');

end

end;

Напишите анонимный блок, в котором выбирается случайное количество яблок в отрезке [1, 5], и это число округляется до целого. Необходимо вывести на экран чётное или нечётное количество яблок.

13.2 Цикл For с Select

Откройте инструмент разработчика Oracle SQL Developer, подключитесь к Базе Данных через пользователя HR и напишите несколько запросов.

Задание 1

Напишите анонимный блок, в котором будет цикл по таблице отделов с выводом на экран наименований отделов.

Задание 2

Напишите анонимный блок, в котором будет цикл по таблице стран из региона Европы с выводом на экран конкатенации идентификаторов и названий стран. Как это показано на примере.

Задание 3

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

13.3 Цикл For

Откройте инструмент разработчика Oracle SQL Developer, подключитесь к Базе Данных через пользователя HR и напишите несколько запросов.

Задание 1

Напишите анонимный блок, в котором будет цикл по значениям от 1 до 100, на каждом шаге, выводить на экран значение счетчика и через пробел вызов функции chr с параметром-значением счетчика.

Примечание: в выводе будут ASCII символы с их номерами.

Задание 2

Измените последовательность в задании 1 на противоположную.

Задание 3

Напишите анонимный блок, в котором будет браться последовательность чисел от 15 до 37 и выведите на экран только нечетные значения возведенные в 3 степень.

13.4 Цикл While

Описание

Откройте инструмент разработчика Oracle SQL Developer, подключитесь к Базе Данных через пользователя HR и напишите несколько запросов.

Задание 1

Напишите анонимный блок, в котором будет переменная i (присвоить значение 100), пока она больше или равна 1, на каждом шаге, выводить на экран значение счетчика и через пробел вызов функции chr с параметром-значением счетчика. После вывода нужно делать декремент переменной.

Задание 2

Переделайте решение задания 1 из цикла с предусловием на цикл с выходом в середине

13.5 Домашняя работа

Задача

Цель задания

Потренироваться делать ad hoc выгрузки.

Что нужно сделать

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

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

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

Напишите анонимный блок, в котором будет бесконечный цикл с выходом по условию что количество попыток соединений равно пяти. В теле цикла необходимо реализовать ожидание перед попыткой по формуле (порядковый номер итерации) * (2 секунды). В Oracle функция ожидания - dbms_session.sleep().
Напишите анонимный блок, в котором будет цикл по сотрудникам из ИТ отдела. На экран нужно будет вывести печатную форму вида:

Имя Фамилия ___________ зарплата Имя Фамилия ___________
(подпись) руководителя (подпись)

Напишите анонимный блок, в котором задается две константы. Эти константы будут задавать верхние границы двух циклов. Первый цикл с предусловием, что суммарное количество глав и подглав меньше заданной верхней границы. Внутри первого цикла есть второй цикл, со счётчиком. Нижняя граница которого на 10 меньше верхней границы и больше нуля. Необходимо выводить на экран значения шагов счётчиков вместе, через точку (конкатенация).

14.1 Процедуры

Откройте инструмент разработчика Oracle SQL Developer, подключитесь к Базе Данных через пользователя HR и напишите несколько запросов.

Задание 1

Создайте процедуру print_my_name, которая выводит на экран Ваше имя.

Задание 2

Создайте процедуру print_datetime, которая выводит на экран текущую дату и время.

Задание 3

Создайте анонимный блок вызова процедур из задания 1 и задания 2.

14.2 Параметры на вход

Откройте инструмент разработчика Oracle SQL Developer, подключитесь к Базе Данных через пользователя HR и напишите несколько запросов.

Задание 1

Создайте процедуру print_parameters с двумя параметрами разных типов, которая выводит на экран конкатенированные названия параметров с их значениями.

Задание 2

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

Задание 3

Создайте анонимные блоки вызова процедур из задания 1 и задания 2.

14.3 Функции

Откройте инструмент разработчика Oracle SQL Developer, подключитесь к Базе Данных через пользователя HR и напишите несколько запросов.

Задание 1

Создайте функцию get_datestamp, которая возвращает текстовый штамп текущей даты.

Задание 2

Создайте анонимный блок вызова этой функции с выводом результата функции на экран.

Задание 3

Напишите запрос, который вызывает функцию из задания 1.

14.4 Параметры на выход

Откройте инструмент разработчика Oracle SQL Developer, подключитесь к Базе Данных через пользователя HR и напишите несколько запросов.

Задание 1

Создайте функцию get_salary, которая принимает на вход параметры: наименование отдела, по-умолчанию ИТ. и возвращает сумму зарплат отдела. Также переопределяется выходной параметр наименования отдела.

Задание 2

Создайте анонимный блок вызова функции из задания 1, который выводит на экран результат функции и то, что возвращает переопределенный параметр.

14.5 Пакеты

Откройте инструмент разработчика Oracle SQL Developer, подключитесь к Базе Данных через пользователя HR и напишите несколько запросов.

Задание 1

Создайте пакет pkg_print_dates.

Задание 2

Создайте функцию get_date в пакете, которая возвращает текущую дату.

Задание 3

Создайте процедуру print_date в пакете, которая берет дату из функции get_date и выводит ее результат на экран.

14.6 Домашняя работа

Задача

Цель задания

Потренироваться делать ad hoc выгрузки.

Что нужно сделать

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

Создайте пакет pkg_job_description, в котором будут функция get_min_max_salary и процедура print_employee_name.
Функция get_min_max_salary, которая возвращает минимальную и максимальную сумму зарплат по идентификатору работы (передается параметром).
Процедура print_employee_name, которая выводит в на экран полное имя сотрудника (его идентификатор передается параметром) и то что возвращает функция get_min_max_salary по идентификатору работы этого сотрудника.
Создайте запрос для запуска функции get_min_max_salary.
Создайте анонимный блок вызова процедуры print_employee_name.

15.1 Исключения

Откройте инструмент разработчика Oracle SQL Developer, подключитесь к Базе Данных через пользователя HR и напишите несколько запросов.

Задание 1

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

Задание 2

В запросе к таблице регионов из задания 1 добавьте условие фильтрации по идентификатору несуществующего региона.

Задание 3

Создайте процедуру prc_calc_amperage, которая принимает на вход два параметра — напряжение и сопротивление, далее рассчитывается по формуле Ома сила тока. В функции необходимо обработать стандартные исключения (invalid_number, value_error, zero_devide, others) и одно пользовательское, когда сила тока равна 1 А.

15.2 Курсоры

Откройте инструмент разработчика Oracle SQL Developer, подключитесь к Базе Данных через пользователя HR и напишите несколько запросов.

Задание 1

Напишите анонимный блок, в котором будет неявный курсор к таблице сотрудников и выведите всех сотрудников отдела IT на экран.

Задание 2

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

Задание 3

Напишите анонимный блок, в котором будет именованный курсор к таблице отделов с параметром локации (присоедините таблицу локаций внутренним соединением), выведите название отдела, менеджера и количество сотрудников.

15.3 Динамический SQL

Откройте инструмент разработчика Oracle SQL Developer, подключитесь к Базе Данных через пользователя HR и напишите несколько запросов.

Задание 1

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

Задание 2

Напишите динамический SQL, который будет создавать таблицу с названием из параметра и столбцами: updated_at с типом дата, id с числовым типом и char_code с текстовым типом кодом.

Задание 3

Напишите динамический SQL, который будет вызывать truncate этой таблицы.

Задание 4

Напишите динамический SQL, который вставляет строки в таблицу с кодами ‘N/A’ и ‘N/D’, идентификаторами 0 и -1, соответственно. Не забывайте зафиксировать результат транзакции.

Задание 5

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

Задание 6

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

15.4 Домашняя работа

Задача

Цель задания

Потренироваться делать ad hoc выгрузки.

Что нужно сделать

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

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

Создайте анонимный блок, который будет использовать в цикле по коллекции именованный курсор по таблице сотрудников, параметр у курсора будет на отдел.
Для каждой строки из курсора необходимо выводить на экран конкатенацию через пробел полей имя, фамилия, заработная плата.
В зависимости от значения заработной платы, перед выводом сообщения на экран, нужно добавить метку ‘LOW’ или ‘OK’, или ‘HIGH’.
В случае, если заработная плата будет равна 1000, нужно будет выдать пользовательское исключение, а в текст ошибки сложить конкатенацию через пробел полей имя, фамилия, заработная плата и меткой ‘TOOOOO LOW’.
Значения заработной платы для ветвления необходимо подобрать самостоятельно в пропорции 1 часть LOW, 2 части OK, 1 часть HIGH.

16.1 Коллекции

Откройте инструмент разработчика Oracle SQL Developer, подключитесь к Базе Данных через пользователя HR и напишите несколько запросов.

Задание 1

Напишите процедуру prc_print_employee_card, которая на вход будет принимать два параметра типа date. В процедуре реализуйте курсор по таблице сотрудников и стран, в которых эти сотрудники работают. С помощью параметров необходимо отфильтровать выборку по полю даты приема на работу, результирующая выборка должна быть положена в коллекцию. При обработке данных в коллекции нужно вывести на экран конкатенацию полей: Имя, Фамилия, Город, Зарплата.

Задание 2

Напишите блок вызова процедуры из задания 1 и запустите с разными датами.

Задание 3

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

16.2 Конвейерные функции

Откройте инструмент разработчика Oracle SQL Developer, подключитесь к Базе Данных через пользователя HR и напишите несколько запросов.

Задание 1

Напишите пакет pkg_get_employees, в котором напишите конвейерную функцию. У функции должен быть параметр на код департамента, по которому нужно будет фильтровать запрос из таблиц сотрудников и отделов. Конвейерная функция должна возвращать следующий набор полей: идентификатор сотрудника, имя, фамилия, электронная почта, номер телефона, зарплата, наименование отдела, город.

Задание 2

Напишите простой запрос к данным из конвейерной функции.

Задание 3

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

16.3 Работа с CLOB

Откройте инструмент разработчика Oracle SQL Developer, подключитесь к Базе Данных через пользователя HR и напишите несколько запросов.

Задание 1

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

Задание 2

Преобразуйте результат задания 1 в CLOB и сделайте второе поле с типом данных CLOB со значением из следующей строки. После этого в верхнем запросе добавьте поле со сравнением первых двух полей.

Задание 3

Напишите процедуру prc_print_department_list, которая на вход принимает двухсимвольный код страны и записывает в CLOB данные по отделам, результат вывести на экран.

16.5 Домашняя работа

Задача

Цель задания

Потренироваться делать ad hoc выгрузки.

Что нужно сделать

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

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

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

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

Напишите пакет pkg_agg_employees.

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

Еще одно поле в коллекции должно содержать имена и фамилии сотрудников в разрезе отделов. Подсказка - используйте в курсоре функцию listagg() с разделителем - переводом каретки (chr(10)).

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

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

Напишите запрос вызова конвейерной функции.