Целью работы является создание анонимного блока, как основной программной единицы программирования сервера в системе ORACLE. При выводе данных необходимо форматирование вывода таким образом, что бы данные выводились в табличном виде (можно использовать функцию RPAD). Вывод данных должен осуществляться из таблиц, используемых в лабораторной работе №6.
Порядок выполнения
1. По варианту, заданному преподавателем, составить анонимный блок, реализующий условия варианта;
2. Выполнить анонимный блок в среде ORASQL. Проверить правильность работы анонимного блока;
4. Составить отчет, в котором привести анонимный блок и ответы на контрольные вопросы.
Вариант 1
Создать анонимный блок, выводящий на экран данные сотрудника из отсортированной по зарплате таблицы сотрудников только тех строк, если смежные строки имеют одинаковые номера отделов.
Вариант 2
Создать анонимный блок, выводящий данные сотрудника на экран, если сотрудник имеет полного однофамильца в другом городе. В курсоре не использовать многотабличную выборку.
Вариант 3
Создать анонимный блок, выводящий данные сотрудника на экран, отсортированные по фамилии и через две строки (т.е 1-ю, 4-ю, 7-ю, 10-ю и т.д. строки).
Вариант 4
Создать анонимный блок, выводящий данные сотрудника на экран, при условии, что у сотрудника имеется более двух однофамильцев.
Вариант 5
Создать анонимный блок, выводящий данные сотрудника на экран, если у сотрудника есть однофамилец, получающий такую же зарплату и не работающий в том же отделе. В курсоре не использовать многотабличную выборку.
Вариант 6
Создать анонимный блок, выводящий данные детей сотрудников, если у сотрудника зарплата превышает среднюю зарплату предприятия.
Вариант 7
Создать анонимный блок, выводящий на экран данные сотрудников, если у них совпадает в номере 3 и более цифр.
Вариант 8
Создать анонимный блок, выводящий на экран данные сотрудника и номер другого сотрудника, наименее отличающийся от номера сотрудника.
Вариант 9
Создать анонимный блок, выводящий на экран отсортированную по номеру сотрудника таблицу сотрудников таким образом: сначала выводится первая строка, затем последняя, затем вторая, предпоследняя и т.д.
Вариант 10
Создать анонимный блок, выводящий на экран данные сотрудника и суммарную зарплату его отдела.
Вариант 11
Создать анонимный блок, выводящий на экран данные сотрудников, если имеется сотрудники его отдела с разницей зарплаты более 5000.
Вариант 12
Создать анонимный блок, выводящий на экран данные только тех сотрудников, дети которых учатся в школе с номером 15.
Контрольные вопросы
Какие операторы SQL недопустимы в блоках PL/SQL?
Как косвенно определить тип переменной?
Для чего используются курсоры?
Для чего нужно закрывать курсор после его использования?
Сколько строк должен возвращать оператор SELECT INTO?
С помощью каких атрибутов можно определить выбор всех строк курсора?
Целью работы является создание хранимой процедуры или триггера базы данных. Обработку возможных предопределенных исключений (например, при отсутствии сотрудника с заданным номером) или исключений пользователя необходимо осуществить в блоке обработчика исключений.
Порядок выполнения
1. Скопировать из схемы MAI таблицу сотрудников SOTR в схему студента одним из двух вариантов:
а) Создать таблицу с такой же структурой, что и таблица SOTR и с помощью многострочного оператора INSERT вставить в нее данные из таблицы SOTR.
б) создать таблицу на основе оператора CREATE TABLE…SELECT…
2. Определить входные параметры для хранимой процедуры или тип для триггера.
3. Для хранимой процедуры определить возможность появления предопределенных исключений в процессе работы процедуры.
4. Создать хранимую процедуру или триггер для созданной таблицы по варианту работы, предложенному преподавателем.
5. Экспериментально проверить правильность работы хранимой процедуры или триггера базы данных. В случае неправильной работы хранимой процедуры или триггера осуществлять откат транзакции.
6. Составить отчет, в котором привести программу хранимой процедуры или триггера и ответить на контрольные вопросы. Продемонстрировать преподавателю работу хранимой процедуры или триггера.
Вариант 1
Создать хранимую процедуру, увеличивающую зарплату у сотрудников отдела на 15% имеющих более 3 детей.
Вариант 2
Создать процедуру, переводящую сотрудника в другой отдел, если в этом отделе у него нет однофамильца.
Вариант 3
Создать хранимую процедуру изменения зарплаты сотрудника, если в его отделе работает более 45 сотрудников.
Вариант 4
Создать хранимую процедуру, переводящую сотрудника в другой отдел, если предельная зарплата другого отдела в случае перевода не превышает допустимую.
Вариант 5
Создать хранимую процедуру изменения города сотрудника на другой город, если в новом городе живет не более 2 его однофамильцев.
Вариант 6
Создать хранимую процедуру, переводящую фамилию сотрудника в верхний регистр, если его зарплата не превышает среднюю зарплату его отдела.
Вариант 7
Создать хранимую процедуру вставки нового сотрудника, если в результате вставки количество сотрудников отдела, куда вставляется сотрудник, не превышает максимального количества сотрудников отделов. В случае вставки в отдел с максимальным числом сотрудников, вставка должна игнорироваться.
Вариант 8
Создать хранимую процедуру перевода сотрудника в другой отдел, если сотрудника учатся более двух детей в заданной школе.
Вариант 9
Скопировать созданную таблицу сотрудников в дублирующую таблицу сотрудников. Создать триггер, обеспечивающий дублирование таблицы сотрудников в дублирующую таблицу.
Создать таблицу аудита таблицы сотрудников с полями:
вида действия с таблицей сотрудников;
даты и времени действия;
идентификатора пользователя;
номера сотрудника, для которого осуществлялось действие.
Создать триггер аудита таблицы сотрудников.
Вариант 10
Создать триггер для таблицы сотрудников, осуществляющий следующие функции:
модификацию (при вставке и изменении) вводимого значения фамилии сотрудников таким образом, что бы, при вводе фамилии сотрудника, она бы переводилась в верхний регистр, и из нее удалялись начальные и завершающие пробелы;
невозможность модификации зарплаты сотрудника, если он работает в отделе 110.
Вариант 11
Создать триггер для таблицы сотрудников, не позволяющий изменять зарплату сотрудника в меньшую сторону, если сотрудник имеет более трех детей.
Вариант 12
Создать триггер для таблицы сотрудников, не позволяющий переводить сотрудника в другой отдел, если более одного его ребенка учится в школе с номером 1. Выборку таблиц детей и школ осуществлять из схемы MAI.
Контрольные вопросы
Какие операторы SQL не допустимы в триггерах баз данных по сравнению с хранимой процедурой?
Какие типы передачи параметров используются в процедурах?
Чем отличается триггер от хранимой процедуры?
Какой блок триггера не компилируется?
Какое максимальное количество триггеров можно создать для одной таблицы?
Какое преимущество дает использование хранимых процедур?