ку. УП 07. Практикум по курсу технологии баз данных Учебное пособие
 Скачать 2.49 Mb.
|
|
PostgreSQL: ALTER TABLE "Marks" ADD CONSTRAINT "check_balls" CHECK ("LowBalls"<"HighBalls"); Пример 3. Требуется добавить ограничение проверки условия для поля телефона кафедры – телефон должен состоять из 7 цифр и иметь формат «ххх- хх-хх». Можно также, как и в предыдущем примере, добавить ограничение CHECK. Однако мы для демонстрации возможностей команды ALTER TABLE сначала удалим столбец телефона кафедры, а потом добавим новый столбец с учетом ограничения. В телефоне первая цифра 2 или 5, остальные цифры могут быть любыми. Для этого используется конструкция языка LIKE, задающая шаблон записи телефона (для PostgreSQL это конструкция SIMILAR TO): MS SQL Server, MySQL: ALTER TABLE Departments DROP COLUMN PhoneDepartment; ALTER TABLE Departments ADD PhoneDepartment VARCHAR(9) CHECK (PhoneDepartment LIKE '[2,5][0-9][0-9]-[0-9][0-9]-[0-9][0-9]'); PostgreSQL: ALTER TABLE "Departments" DROP COLUMN "PhoneDepartment"; ALTER TABLE "Departments" ADD "PhoneDepartment" text CHECK ("PhoneDepartment" SIMILAR TO '(2|5)[0-9][0-9]-[0-9][0-9]-[0-9][0-9]'); Пример 4. Введем ограничение на согласованность баллов и оценки в таблице результатов сессии. Будем полагать для простоты, что в таблицу зано- сятся только положительные результаты сдачи зачетов и экзаменов. Таким об- 37 разом, оценка должна быть только 3, 4 или 5. При этом должна быть учтена со- гласованность баллов исходя из шкалы принятой балльно-рейтинговой систе- мы: MS SQL Server, MySQL: ALTER TABLE Results ADD CONSTRAINT ch_res_marks CHECK (Mark IN (3,4,5) AND ((Mark=3 AND Balls BETWEEN 55 AND 70) OR (Mark=4 AND Balls BETWEEN 71 AND 85) OR (Mark=5 AND Balls BETWEEN 86 AND 100))); PostgreSQL: ALTER TABLE "Results" ADD CONSTRAINT "ch_res_marks" CHECK ("Mark" IN (3,4,5) AND (("Mark"=3 AND "Balls" BETWEEN 55 AND 70) OR ("Mark"=4 AND "Balls" BETWEEN 71 AND 85) OR ("Mark"=5 AND "Balls" BETWEEN 86 AND 100))); Заметим, что для аналогичных целей была предназначена таблица Marks. В дальнейшем мы будем использовать ее. Пример 5. В MySQL иногда требуется явно указать кодировку данных таблиц. Это также можно сделать с помощью команды ALTER TABLE: ALTER TABLE `Departments` CONVERT TO CHARACTER SET utf8; 1.3. КОМАНДЫ МОДИФИКАЦИИ ДАННЫХ (DML) Оболочки проектирования содержат средства визуального внесения дан- ных в таблицы. Например, в dbForge Studio это делается с помощью кон- текстного меню таблицы, пункта «Редактировать таблицу», вкладка «Данные»: Рис. 21. Окно редактирования данных. 38 С помощью нижней панели можно добавлять, удалять записи и переме- щаться по ним. Сохранение данных происходит не сразу, а после закрытия окна. Но в процессе ввода данные проверяются на соответствие ограничениям. Например, внесем неправильный телефонный номер. В этом случае об ошибке будет со- общено следующим образом: Рис. 22. Сообщение о нарушении ограничения. Естественно, добавление записей (особенно массовое) может проводиться с помощью команд SQL, которые объединены в один сценарий. Итак, сценарий внесения данных в базу для MS SQL Server может быть таким: USE proba GO -- вставка записей в таблицу Students insert into Students (FIOStudent,NumGroup) VALUES ('Иванов Иван Иванович',901); -- и другие записи GO -- вставка записей в таблицу Departments insert into Departments (TitleDepartment,PhoneDepartment) VALUES ('Кафедра математики','234-11-45'); -- и другие записи GO -- вставка записей в таблицу Teachers insert into Teachers (FIOTeacher,idDepartment ) VALUES ('Федосеев Александр Иванович', 6); -- и другие записи GO 39 -- вставка записей в таблицу Subjects insert into Subjects (TitleSubject) VALUES ('Математический анализ'); -- и другие записи GO -- вставка записей в таблицу Sessions insert into Sessions (NumGroup,NumSemestr, Zach_Exam,idSubject, idTeach- er) VALUES (901,1, 'зачет', 2,18); -- и другие записи GO -- вставка записей в таблицу Marks insert into Marks(idMark,LowBalls,HighBalls) VALUES (5, 86, 100); -- и другие записи GO Отметим, что свойство счетчика в MS SQL Server для поля фиксирует все операции с таблицей, поэтому, к примеру, несмотря на отсутствие записей в таблице, новый номер может быть отличен от 1. Оператор GO, который ис- пользуется в скрипте, специфичен для MS SQL Server и разделяет скрипт на не- делимые блоки, которые выполнятся полностью или не выполнятся вообще. Удобнее всего выполнять этот сценарий блоками, чтобы между ними можно было бы убедиться в корректности использования значений в полях внешнего ключа. Команды вставки для MySQL не отличаются от приведенного выше кода (за исключением команды GO). Также внимательно следует отнестись к ключе- вым полям при установке связи внешнего ключа. Сценарий для PostgreSQL имеет следующий вид. Отсутствие полей- счетчиков требует указывать ключевые поля во всех записях. -- сценарий вставки записей в таблицы базы данных insert into "Students" ("idStudent", "FIOStudent","NumGroup") VALUES (1,'Иванов Иван Иванович',901); -- и другие записи При нарушениях тех или иных ограничений оператор вставки не сраба- тывает. Так, повторная вставка записи или вставка записи с уже существующим первичным ключом будет запрещена. Например, повторно осуществляем вставку записи: insert into Marks(idMark,LowBalls,HighBalls) VALUES (5, 86, 100); 40 команда выполнена не будет. Мы увидим сообщение об ошибке: MS SQL Server: Ошибка: (53,1): Нарушено "PK_Marks" ограничения PRIMARY KEY. Не уда- ется вставить повторяющийся ключ в объект "dbo.Marks". Повторяющее- ся значение ключа: (5). PostgreSQL: ОШИБКА: повторяющееся значение ключа нарушает ограничение уникаль- ности "pk_mark" SQL-состояние: 23505 Подробности: Ключ "("idMark")=(5)" уже существует. MySQL: Duplicate entry '5' for key 1 При вставке записи с нарушением ограничения внешнего ключа также будет выведено сообщение об ошибке. Например, мы пытаемся вставить строку в таблицу сессии, в которой код преподавателя равен 100: insert into Sessions (NumGroup, NumSemestr, Zach_Exam, idSubject,idTeacher) VALUES (905,1, 'экзамен', 3,100); Сообщение об ошибке в этом случае будет таким: MS SQL Server: Ошибка: (53,63): Конфликт инструкции INSERT с ограничением FOREIGN KEY "FK_Sessions_Teachers_idTeacher". Конфликт произошел в базе данных "proba", таблица "dbo.Teachers", column 'idTeacher'. PostgreSQL: ОШИБКА: INSERT или UPDATE в таблице "Sessions" нарушает ограничение внешнего ключа "fk_sess_tea" SQL-состояние: 23503 Подробности: Ключ (idTeacher)=(100) отсутствует в таблице "Teachers". MySQL: Cannot add or update a child row: a foreign key constraint fails (`decanat/sessions`, CONSTRAINT `FK_sessions_teachers_idTeacher` FOREIGN KEY (`idTeacher`) REFERENCES `teachers` (`idTeacher`) ON DELETE NO ACTION ON UPDATE NO ACTION) 41 1.4. ВЫБОРКА ДАННЫХ. ОПЕРАТОР SELECT (DQL) Одной из основных задач, которую решают базы данных – это эффектив- ный поиск необходимых данных. Универсальным подходом для решения этой задачи является применение специального оператора языка SQL - SELECT. Этот оператор достаточно сложный, имеет множество возможностей. Теорети- ческой основой этого оператора является реляционная алгебра, которая доказы- вает возможность получения с помощью конечного набора операций любых возможных наборов данных. Приведем несколько примеров запросов к таблицам базы данных, демон- стрирующих различные возможности языка SQL и различные операции реля- ционной алгебры. Результаты будем показывать из различных вариантов реали- зации учебного проекта баз данных (при выполнении на различных СУБД). Вспомним, что главным отличием синтаксиса команд SQL для Post- greSQL является заключение в кавычки имен таблиц, столбцов и пр. В следую- щих примерах будем приводить текст запроса в стиле MS SQL Server или MySQL. В случаях более серьезной разницы в записи запросов, будем приво- дить его текст для каждого СУБД в отдельности. Запрос 1. Операция проекции. Осуществляется выбор только части по- лей таблицы, т.е. производится вертикальная выборка данных. Распечатать ФИО всех студентов, зарегистрированных в базе данных: SELECT FIOStudent FROM Students; Рис. 23. Результат выполнения запроса для MS SQL Server. 42 Запрос 2. Операция селекции. Осуществляется горизонтальная выборка – в результат попадают только записи, удовлетворяющие условию. Распечатать ФИО студентов группы 902. SELECT * FROM Students WHERE NumGroup='902'; Рис. 24. Результат выполнения запроса для MySQL. Запрос 3. Операции соединения. Здесь следует выделить декартово про- изведение и на его основе соединение по условию, а также естественное соеди- нение (по одноименным полям или равенству полей с одинаковым смыслом). Распечатать список зачетов и экзаменов, которые будут сдавать студенты группы 901 в первом семестре. С помощью декартового произведения и соединения по условию данный запрос запишется так: SELECT TitleSubject, Zach_Exam FROM Sessions, Subjects WHERE Sessions.NumGroup='901' AND Sessions.idSubject=Subjects.idSubject AND Sessions.NumSemestr=1; В этом запросе впервые мы выбираем информацию из нескольких таблиц. В случае использования одноименных полей следует дополнить их именами таблиц согласно схеме ИмяТаблицы.ИмяПоля. В случае же записи для СУБД PostgreSQL каждое из имен должно быть записано в кавычках: SELECT "TitleSubject", "Zach_Exam" FROM "Sessions", "Subjects" WHERE "Sessions"."NumGroup"='901' AND "Sessions"."idSubject"="Subjects"."idSubject" AND "Sessions"."NumSemestr"=1; 43 Рис. 25. Результат выполнения запроса для PostgreSQL. Этот же запрос с помощью операции внутреннего соединения имеет сле- дующий вид (соединение производится по равенству одноименных атрибутов idSubject таблиц Sessions и Subjects): SELECT TitleSubject, Zach_Exam FROM Sessions INNER JOIN Subjects ON Sessions.idSubject=Subjects.idSubject WHERE Sessions.NumGroup='901' AND Sessions.NumSemestr=1; Запрос 4. Операция объединения. Теоретико-множественные операции часто можно записать с помощью логических операций, примененных в кон- струкции WHERE запроса. Например, нужно получить список зачетов и экза- менов, которые сдают студенты 901 или 902 групп в 1 семестре. Таким обра- зом, нужно объединить два множества, соответствующие двум разным груп- пам. Объединение можно задать с помощью логического ИЛИ. SELECT NumGroup, TitleSubject, Zach_Exam FROM Sessions INNER JOIN Subjects ON Sessions.idSubject=Subjects.idSubject WHERE Sessions.NumSemestr=1 AND (Sessions.NumGroup='901' OR Sessions.NumGroup='902'); Аналогичный результат будет получен с помощью объединения результатов двух запросов (подзапросов) с одинаковой структурой результата: (SELECT NumGroup, TitleSubject, Zach_Exam FROM Sessions INNER JOIN Subjects ON Sessions.idSubject=Subjects.idSubject WHERE Sessions.NumSemestr=1 AND Sessions.NumGroup='901') UNION (SELECT NumGroup, TitleSubject, Zach_Exam FROM Sessions INNER JOIN Subjects ON Sessions.idSubject=Subjects.idSubject WHERE Sessions.NumSemestr=1 AND Sessions.NumGroup='902'); 44 Рис. 26. Результат выполнения запроса для MS SQL Server. Запрос 5. Операция пересечения. В простых случаях эту операцию можно описать с помощью логической операции AND. В более сложных случа- ях эта операция определяется чаще всего с помощью подзапроса и ключевого слова EXISTS, которое показывает наличие похожего элемента во множестве, которое задается подзапросом. Найти тех преподавателей, которым должны сдавать зачеты или экзамены в первом семестре студенты 901 и 902 групп. Отметим необходимость приме- нения здесь операции переименования (AS) для того, чтобы различить два эк- земпляра таблицы Sessions (из основного запроса и подзапроса). SELECT FIOTeacher FROM Teachers INNER JOIN Sessions ON Teachers.idTeacher=Sessions.idTeacher WHERE Sessions.NumSemestr=1 AND Sessions.NumGroup='901' AND EXISTS (SELECT * FROM Sessions as s1 WHERE s1.idTeacher=Sessions.idTeacher AND s1.NumSemestr=1 AND s1.NumGroup='902'); Рис. 27. Результат выполнения запроса для MySQL. Запрос 6. Операция разности. Эта операция также определяется часто с помощью подзапроса с ключевым словом NOT EXISTS, которое показывает от- сутствие элемента во множестве, задаваемом подзапросом. Приведем анало- гичный предыдущему пример. 45 Найти тех преподавателей, которым должны сдавать зачеты или экзамены в первом семестре студенты 901 группы, но не студенты из 902 группы. SELECT FIOTeacher FROM Teachers INNER JOIN Sessions ON Teachers.idTeacher=Sessions.idTeacher WHERE Sessions.NumSemestr=1 AND Sessions.NumGroup='901' AND NOT EXISTS (SELECT * FROM Sessions as s1 WHERE s1.idTeacher=Sessions.idTeacher AND s1.NumSemestr=1 AND s1.NumGroup='902'); Рис. 28. Результат выполнения запроса для PostgreSQL. Запрос 7. Операция группировки. Эта операция связана со своеобразной сверткой таблицы по полям группировки. Помимо полей группировки резуль- тат запроса может содержать итоговые агрегирующие функции по группам (COUNT, SUM, AVG, MAX, MIN). Найти итоговое количество зачетов и экзаменов, которые должны сдавать студенты различных групп в 1 семестре. Операция группировки здесь будет применяться к таблице Sessions. По- лем группировки является номер группы. Агрегирующим полем является коли- чество строк с заданной группой и номером семестра. SELECT NumGroup, COUNT(*) AS kolvo FROM Sessions WHERE NumSemestr=1 GROUP BY NumGroup; Рис. 29. Результат выполнения запроса для MS SQL Server. В случае, когда при группировке имеется условие отбора на группу, ис- пользуется конструкция HAVING. Например, в предыдущем примере нам нуж- но вывести только те группы, в которых количество зачетов-экзаменов равно 3. 46 SELECT NumGroup, COUNT(*) AS kolvo FROM Sessions WHERE NumSemestr=1 GROUP BY NumGroup HAVING COUNT(*)=3; В СУБД MySQL возможно в условии в конструкции HAVING использо- вать псевдоним агрегирующего столбца kolvo. Запрос 8. Операция сортировки. Вывести всех преподавателей, которым сдают студенты зачеты-экзамены в первом семестре, в порядке убывания коли- чества зачетов-экзаменов. Для этого следует сначала выбрать нужные элементы таблицы Sessions, затем осуществить естественное соединение полученной таб- лицы с таблицей Teachers, после чего производится группировка записей в ре- зультате запроса и последующая сортировка. SELECT FIOTeacher, COUNT(*) AS kolvo FROM sessions INNER JOIN teachers ON sessions.teachers_idTeacher=teachers.idTeacher WHERE sessions.NumSemestr=1 GROUP BY FIOTeacher ORDER BY kolvo; Рис. 30. Результат выполнения запроса для MySQL. Запрос 9. Операция деления. Это самая нетривиальная операция реляци- онной алгебры, которая обычно применяется тогда, когда требуется найти все записи первой таблицы, которые соединяются естественным образом со всеми записями второй таблицы. Например, нам требуется найти тех преподавателей, которым должны сдать в первом семестре зачеты-экзамены студенты всех групп факультета. Запрос получается достаточно сложный и он связан с выпол- нением двух операций разности (первая разность - из всевозможных комбина- ций групп и преподавателей вычитаются реальные комбинации этих полей, т.е. 47 результатом становятся всевозможные нереальные пары, вторая разность – вы- бираются преподаватели, которые в нереальных парах не присутствуют). SELECT FIOTeacher FROM Teachers WHERE idTeacher IN (SELECT DISTINCT s0.idTeacher FROM Sessions AS s0 WHERE NumSemestr=1 AND NOT EXISTS (SELECT DISTINCT s1.idTeacher, s2.NumGroup FROM Sessions AS s1, Sessions AS s2 WHERE s1.NumSemestr=1 AND s2.NumSemestr=1 AND NOT EXISTS (SELECT * FROM Sessions AS s3 WHERE s3.idTeacher=s1.idTeacher AND s3.NumGroup=s2.NumGroup) AND s1.idTeacher=s0.idTeacher)); Рис. 31. Результат выполнения запроса для PostgreSQL. Разберем этот запрос по частям. Все возможные пары «преподаватель» - «группа» получаются с помощью подзапроса: SELECT DISTINCT s1.idTeacher, s2.NumGroup FROM Sessions AS s1, Sessions AS s2 WHERE s1.NumSemestr=1 AND s2.NumSemestr=1 добавлением к нему условия: NOT EXISTS (SELECT * FROM Sessions AS s3 WHERE s3.idTeacher=s1.idTeacher AND s3.NumGroup=s2.NumGroup) из всевозможных пар вычитаются реальные пары, т.е. в результате полу- чаем все возможные нереальные пары «преподаватель»-«группа». Результат этого подзапроса внедряется в другой подзапрос, получающий тех преподава- телей, коды которых не присутствуют в этом списке. Далее подключением таб- лицы Teachers получаем их ФИО. 48 Наконец, рассмотрим возможность построения представлений – вирту- альных таблиц, которые представимы как результат выполнения некоторого за- проса. Представления в дальнейшем можно использовать в других запросах как таблицы, понимая при этом, что каждый раз при обращении к представлению производится выполнение запроса, по которому представление было создано. Например, создадим представление, в котором находится информации о том, какие зачеты и экзамены должен сдать в первом семестре каждый студент. CREATE VIEW Student_Session AS SELECT FIOStudent, TitleSubject FROM Students INNER JOIN Sessions ON Students.NumGroup=Sessions.NumGroup INNER JOIN Subjects ON Subjects.idSubject=Sessions.idSubject WHERE NumSemestr=1; Далее обратимся к этому представлению как к таблице. Например, найти те зачеты-экзамены, которые должен сдать студент Иванов: SELECT TitleSubject FROM Student_Session WHERE FIOStudent LIKE 'Иванов%'; 1.5. ХРАНИМЫЕ ПРОЦЕДУРЫ, ФУНКЦИИ И ТРИГГЕРЫ Хранимые процедуры, функции и триггеры вводятся в базу данных для обеспечения бизнес-логики приложения на уровне серверной его компоненты. Обычно хранимые процедуры и функции представляют собой утилиты, которые определенным образом обрабатывают данные или реализуют достаточно слож- ный алгоритм вычисления некоторых показателей. Триггеры – это частный случай хранимой процедуры, который выполня- ется автоматически при выполнении команд обновления данных (INSERT, DELETE, UPDATE). Триггеры привязываются к конкретным таблицам базы данных. Для каждой команды должны быть свои триггеры. В дереве элементов базы данных в любом СУБД имеются группы для определения этих программных элементов: 49 Рис. 32. Дерево элементов в MS SQL Server. Рис. 33. Дерево элементов в MySQL. Рис. 34. Дерево элементов в PostgreSQL. Для создания процедуры, функции или триггера требуется воспользо- ваться контекстным меню соответствующего элемента дерева. Для создания и редактирования существует специальное диалоговое окно, в котором, в частно- сти, можно задать программный код процедуры, функции или триггера. Про- граммный код формируется посредством перемешивания команд управления и SQL-команд. Теперь приведем несколько примеров создания хранимых процедур и функций. Здесь мы уже заметим существенные отличия в синтаксисе использу- емых команд для различных СУБД. Поэтому для каждого из СУБД текст про- цедур, функций, триггеров и способы вызова укажем отдельно. Пример 1. Напишем хранимую процедуру, которая получает в качестве входного параметра количество баллов и на основании шкалы оценок вычисля- ет полученную оценку. Результат возвращается через выходной параметр. MS SQL Server. В SQL Server любая переменная именуется, начиная с символа ‘@’. Остальной код комментировать не требуется. CREATE PROCEDURE dbo.GetMark1 (@ball int, @mark INT OUT) AS BEGIN IF @ball BETWEEN 55 AND 70 50 SET @mark=3; ELSE IF @ball BETWEEN 71 AND 85 SET @mark=4; ELSE IF @ball BETWEEN 86 AND 100 SET @mark=5; ELSE SET @mark=2; END GO Для вызова процедуры требуется создать переменную для применения ее в качестве выходного параметра, после чего воспользоваться командой EXEC. Распечатать результат в выходном потоке можно с помощью оператора PRINT. -- пример вызова процедуры GetMark1 DECLARE @mark INT; EXEC GetMark1 78, @mark OUT; PRINT '78 баллов соответствует оценке' + STR(@mark); Рис. 35. Результат выполнения хранимой процедуры в MS SQL Server. MySQL. Принципиальных отличий в программном коде нет. Стоит отме- тить, что символ ‘@’ здесь используется только для глобальных переменных, поэтому имена параметров этот символ не имеют. CREATE DEFINER = 'root'@'localhost' PROCEDURE decanat.GetMark1(in ball INT, out mark INT) BEGIN IF ball BETWEEN 55 AND 70 THEN SET mark=3; ELSEIF ball BETWEEN 71 AND 85 THEN SET mark=4; ELSEIF ball BETWEEN 86 AND 100 THEN SET mark=5; ELSE SET mark=2; END IF; END Вызов процедуры осуществляется следующим образом (достаточно от- личным от MS SQL Server). В MySQL не предусмотрено окно сообщений, по- этому вывод осуществляется посредством выборки значения переменной: call GetMark1(89,@m); select ""+@m as "Оценка"; 51 Рис. 36. Результат выполнения хранимой процедуры в MySQL. PostgreSQL. Это СУБД не позволяет создавать процедуры. Здесь исполь- зуются только функции. Еще одна особенность состоит в том, что функцию можно написать на разных языках. Наиболее распространены sql и plpgsql. Ос- новное отличие языков состоит в том, что в sql доступны только операторы sql, а plpgsql имеет также операторы управления. Интересно, что именовать пара- метры вовсе не обязательно. К параметрам можно обращаться по номерам, предваренным символом “$”. Итак, создадим скрипт, в котором запишем сле- дующую функцию: CREATE FUNCTION GetMark1 (integer) RETURNS integer AS $$ DECLARE res INTEGER; BEGIN IF $1 BETWEEN 55 AND 70 THEN SELECT 3 INTO res; ELSE IF $1 BETWEEN 71 AND 85 THEN SELECT 4 INTO res; ELSE IF $1 BETWEEN 86 AND 100 THEN SELECT 5 INTO res; ELSE SELECT 2 INTO res; END IF; END IF; END IF; RETURN res; END; $$ LANGUAGE plpgsql; Отметим применение символов “$$” в начале и конце функции. Они поз- воляют игнорировать символы-разделители внутри этих своеобразных скобок. Функция декларирует тип возвращаемого значения с помощью ключевого сло- ва RETURNS. В теле функции создается переменная для хранения результата, которой присваивается значение в зависимости от ветки условных операторов, по которой пойдет управление. К параметру производится обращение посред- ством “$1”. Отметим еще, что в конце следует указать используемый язык написании функции. Вызов функции: 52 select GetMark1(68); Пример 2. Чтобы при смене правил вычисления оценок не нужно было бы менять процедуру, мы создали справочную таблицу для хранения всех оце- нок и их диапазонов Marks. Пришло время ею воспользоваться. Второй вариант функции получения оценки по набранным баллам будет обращаться к этой таб- лице за информацией. Оформим этот вариант в виде функции с одним параметром, хранящим набранные баллы, и возвращающую найденную оценку или 2 в случае, когда набранным баллам ничего в таблице не соответствует. В данной функции де- монстрируется использование переменных, запросов и условного оператора. Приведем два варианта функции. С помощью запроса на количество таких за- писей алгоритм первого варианта предусматривает определение, есть ли в таб- лице соответствующая баллам оценка Второй вариант пользуется специальной функцией EXISTS, которая, принимая в качестве аргумента запрос, возвращает логическое значение, определяющее, есть ли в результате запроса записи. MS SQL Server. В предыдущем примере мы уже видели одно из отличий языка для MS SQL Server, связанное с присвоением значений переменным. В MS SQL Server для этого предназначен оператор SET. В других рассматривае- мых нами СУБД в этой роли выступает оператор SELECT. Итак, первый вариант функции. В нем определяются две переменные для хранения количества записей и оценки. Значением оценки по умолчанию явля- ется оценка 2. После определяется количество записей в таблице Marks, соот- ветствующее набранным баллам и, если это количество больше 0, найденная оценка присваивается переменной @mark, которая в конце возвращается как результат функции. Отметим, что оператор RETURN должен быть последним в функции. CREATE FUNCTION dbo.GetMark2(@ball int) RETURNS INT AS BEGIN DECLARE @kolvo INT, @mark INT; SET @mark=2; SET @kolvo=(SELECT COUNT(*) FROM Marks WHERE 53 @ball between LowBalls and HighBalls); IF @kolvo>0 SET @mark=(SELECT idMark FROM Marks WHERE @ball between LowBalls and HighBalls); RETURN @mark; END GO Во втором варианте функции в условном операторе вместо переменной @kolvo используется вызов функции EXISTS. CREATE FUNCTION dbo.GetMark3(@ball int) RETURNS INT AS BEGIN DECLARE @mark INT; SET @mark=2; IF EXISTS(SELECT * FROM Marks WHERE @ball between LowBalls and HighBalls) SET @mark=(SELECT idMark FROM Marks WHERE @ball between LowBalls and HighBalls); RETURN @mark; END GO Вызов функции оформляется следующим образом: DECLARE @mark INT; SET @mark=dbo.GetMark3(93); PRINT '93 балла соответствует оценке' + STR(@mark); MySQL. Аналогичные функции для сервера MySQL определяются сле- дующим образом: CREATE FUNCTION decanat.GetMark2(ball int) RETURNS int(11) BEGIN DECLARE kolvo, mark INT; SELECT 2 INTO mark; SELECT COUNT(*) INTO kolvo FROM Marks WHERE ball between LowBalls and HighBalls; IF kolvo>0 THEN SELECT idMark INTO mark FROM Marks WHERE ball between LowBalls and HighBalls; END IF; RETURN mark; END CREATE FUNCTION decanat.GetMark3(ball int) RETURNS int(11) BEGIN DECLARE mark INT; SELECT 2 INTO mark; IF EXISTS (SELECT * FROM Marks WHERE ball between LowBalls and HighBalls) THEN 54 SELECT idMark INTO mark FROM Marks WHERE ball between LowBalls and HighBalls; END IF; RETURN mark; END Вызов функции можно осуществлять непосредственно в выражении, например: SELECT ""+GetMark2(89) as "Оценка"; PostgreSQL. Как уже было сказано, в PostgreSQL хранимых процедур нет, в этом СУБД используются только функции. Программный код практиче- ски не будет отличаться от кода для MySQL за исключением определения пе- ременных за скобками функции, обращения к параметру и обращения к полям и таблице базы данных: CREATE FUNCTION GetMark2 (integer) RETURNS integer AS $$ DECLARE kolvo INTEGER; DECLARE mark INTEGER; BEGIN SELECT 2 INTO mark; SELECT COUNT(*) INTO kolvo FROM "Marks" WHERE $1 between "LowBalls" and "HighBalls"; IF kolvo>0 THEN SELECT "idMark" INTO mark FROM "Marks" WHERE $1 between "LowBalls" and "HighBalls"; END IF; RETURN mark; END; $$ LANGUAGE plpgsql; CREATE FUNCTION GetMark3 (integer) RETURNS integer AS $$ DECLARE mark INTEGER; BEGIN SELECT 2 INTO mark; IF EXISTS (SELECT * FROM "Marks" WHERE $1 between "LowBalls" and "HighBalls") THEN SELECT "idMark" INTO mark FROM "Marks" WHERE $1 between "LowBalls" and "High- Balls"; END IF; RETURN mark; END; $$ LANGUAGE plpgsql; Теперь приведем примеры создания триггеров. Пример 1. Создадим триггер для вставки в таблицу результатов сессии, в котором проверяются ограничения целостности (студент с заданном кодом су- ществует, предмет с заданным кодом существует, дисциплину нужно сдавать 55 именно в этом семестре). Если произойдет нарушение этих ограничений, то требуется откатить транзакцию, т.е. не выполнять вставку записи. Если же все данные будут корректными, проведем заполнение значений полей даты сдачи зачета/экзамена как текущей и вычислим оценку по указанным баллам. Для проверки корректности данных для вставки создадим вспомогатель- ную хранимую функцию, чтобы код триггера был не очень сложным. (Для не- которых версий СУБД требуется, чтобы в триггере было упоминание только текущей записи, обращение к другим таблицам и записям недоступно). MS SQL Server: CREATE FUNCTION dbo.IsCorrect(@idStud INT, @idSubj INT, @Sem INT, @idTeach INT) RETURNS INT AS BEGIN IF EXISTS (SELECT * FROM Students INNER JOIN Sessions ON Students.NumGroup=Sessions.NumGroup INNER JOIN Subjects ON Sessions.idSubject=Subjects.idSubject INNER JOIN Teachers ON Sessions.idTeacher=Teachers.idTeacher WHERE Students.idStudent=@idStud AND Subjects.idSubject=@idSub AND Teachers.idTeacher=@idTeach and NumSemestr=@Sem) RETURN 1; RETURN 0; END GO MySQL: CREATE FUNCTION IsCorrect (idStud INT, idSubj INT, Sem INT, idTeach INT) RETURNS INT(11) BEGIN RETURN EXISTS (SELECT * FROM Students INNER JOIN Sessions ON Students.NumGroup=Sessions.NumGroup INNER JOIN Subjects ON Sessions.idSubject=Subjects.idSubject INNER JOIN Teachers ON Sessions.idTeacher=Teachers.idTeacher WHERE Students.idStudent=idStud AND Subjects.idSubject=idSubj AND Teachers.idTeacher=idTeach and NumSemestr=Sem); END PostgreSQL: CREATE FUNCTION IsCorrect(integer, integer, integer, integer) RETURNS BOOLEAN AS $$ BEGIN RETURN EXISTS (SELECT * from "Students" INNER JOIN "Sessions" ON "Students"."NumGroup"="Sessions"."NumGroup" INNER JOIN "Subjects" ON "Sessions"."idSubject"="Subjects"."idSubject" 56 INNER JOIN "Teachers" ON "Sessions"."idTeacher"="Teachers"."idTeacher" WHERE "Students"."idStudent"=$1 AND "Subjects"."idSubject"=$2 AND "Teachers"."idTeacher"=$3 AND "NumSemestr"=$4); END; $$ LANGUAGE plpgsql; Триггер на вставку записи в таблицу Results будет вызывать функцию проверки корректности, передавая в функцию поля из новой записи. Если за- пись будет корректной, будут скорректированы поля оценки и даты сдачи заче- та/экзамена. В противном случае должен быть произведен откат транзакции. MS SQL Server: При вставке записи сначала запись попадает в виртуальную таблицу in- serted (при удалении будет использоваться таблица deleted, при изменении за- писи используются обе таблицы – в inserted хранятся новые значения записи, в deleted – прежние значения полей записи). Поэтому сначала получаем данные новой записи из таблицы inserted, после чего проверяем их на корректность. В случае корректных данных оставшиеся поля (дата и оценка) изменяются по- средством команды UPDATE. Откат транзакции в случае некорректных данных производится с помощью команды ROLLBACK. CREATE TRIGGER trigger1 ON dbo.Results FOR INSERT AS BEGIN -- объявление необходимых переменных для хранения данных новой записи DECLARE @idStudent INT, @idSubject INT, @idTeacher INT, @NumSemestr INT, @Balls INT; -- чтение данных новой записи SET @idStudent =(SELECT idStudent FROM inserted); SET @idSubject =(SELECT idSubject FROM inserted); SET @idTeacher =(SELECT idTeacher FROM inserted); SET @NumSemestr =(SELECT NumSemestr FROM inserted); SET @Balls =(SELECT Balls FROM inserted); -- проверка на корректность данных IF dbo.IsCorrect(@idStudent, @idSubject, @NumSemestr, @idTeacher)=0 BEGIN -- данные некорректны. Выводим сообщение об ошибке -- и производим откат транзакции PRINT 'Ошибка данных: данные некорректны'; ROLLBACK; END ELSE -- изменение полей даты и вычисление оценки. 57 -- В условии указывается первичный ключ UPDATE dbo.Results SET mark=dbo.GetMark3(@Balls), DateExam=GETDATE() WHERE idStudent=@idStudent AND idSubject=@idSubject AND idTeacher=@idTeacher AND NumSemestr=@NumSemestr; END GO MySQL: Для MySQL данный триггер запишется проще, так как здесь проще полу- чить данные новой записи. Новая запись хранится в виде объекта New (запись при удалении хранится в виде объекта Old). Однако имеется проблема, связан- ная с отсутствием команды отката триггера. В этом случае рекомендуется вы- полнить какую-нибудь ошибочную команду, например, вставить запись с уже существующим ключом. Ошибка в этой команде приведет к отмене действий всей транзакции (команды и триггера). CREATE TRIGGER decanat.trigger1 BEFORE INSERT ON decanat.results FOR EACH ROW BEGIN IF IsCorrect(New.idStudent, New.idSubject, New.NumSemestr, New.idTeacher) THEN SET New.Mark=GetMark3(New.Balls); SET New.DateExam=Now(); ELSE insert into Departments values (1,"",""); END IF; END PostgreSQL: В PostgreSQL триггер как таковой связан со специальной триггерной функцией, в которой и осуществляется вся обработка данных. Триггерная функция возвращает объект-запись (NEW или OLD), с которой производится работа. При написании триггера мы указываем только для какой операции, для какой таблицы и каков тип триггера, после чего вызываем триггерную функ- цию. Откат производится генерацией исключительной ситуации с указанием сообщения об ошибке. В остальном код похож на тот, что писался для MySQL: -- Создание триггерной функции на вставку результата сдачи экзамена CREATE FUNCTION trigger_results_insert() RETURNS trigger AS $$ BEGIN IF IsCorrect(NEW."idStudent", NEW."idSubject", NEW."idTeacher", NEW."NumSemestr") THEN SELECT GetMark3(NEW."Balls") INTO NEW."Mark"; 58 SELECT Now() INTO New."DateExam"; ELSE -- генерация исключительной ситуации RAISE EXCEPTION 'Ошибка корректности данных'; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; -- Создание триггера на вставку нового результата экзамена CREATE TRIGGER tr_results_insert BEFORE INSERT ON "Results" FOR EACH ROW EXECUTE PROCEDURE trigger_results_insert(); Для проверки работы триггера (например, для MySQL) проведем следу- ющие операции вставки: INSERT INTO Results (idStudent, idSubject,idTeacher, NumSemestr, Balls) VALUES (1,1,1,1,78); INSERT INTO Results (idStudent, idSubject,idTeacher, NumSemestr, Balls) VALUES (2,1,1,1,98); INSERT INTO Results (idStudent, idSubject,idTeacher, NumSemestr, Balls) VALUES (6,1,1,1,68); Согласно данным, которые мы вносили в таблицу, последняя запись не должна быть добавлена. Пример 2. Приведем еще один пример триггера на вставку новой записи в таблицу результатов. Этот триггер должен срабатывать после вставки и быть связан с подсчетом рейтинга студентов. Триггеры «после» часто используются для проведения специальной обработки данных на основании выполненной операции и могут быть связаны с другими таблицами. Для этого введем в базу данных новую таблицу, например, с помощью следующей SQL-команды: CREATE TABLE Reyting ( idStudent INT PRIMARY KEY, summ_balls INT, CONSTRAINT fk_reyting FOREIGN KEY (idStudent) REFERENCES Students (idStudent) ) При вставке нового результата рейтинг студента должен меняться. Таким образом, нужно проанализировать, есть ли запись о студенте – в случае поло- жительного ответа произвести суммирование баллов, иначе добавить новую за- пись в таблицу рейтинга. 59 MS SQL Server: CREATE TRIGGER trigger2 ON dbo.Results AFTER INSERT AS BEGIN DECLARE @idStudent INT, @Balls INT; SET @idStudent = (SELECT idStudent FROM inserted); SET @Balls =(SELECT Balls FROM inserted); IF EXISTS(SELECT * FROM Reyting WHERE idStudent=@idStudent) UPDATE Reyting SET summ_balls=summ_balls+@Balls WHERE idStudent=@idStudent; ELSE INSERT INTO Reyting (idStudent, summ_balls) VALUES (@idStudent, @Balls); END GO MySQL: CREATE TRIGGER decanat.trigger2 AFTER INSERT ON decanat.results FOR EACH ROW BEGIN IF EXISTS(SELECT * FROM Reyting WHERE idStudent=new.idStudent) THEN UPDATE Reyting SET summ_balls=summ_balls+new.Balls WHERE idStudent=new.idStudent; ELSE INSERT INTO Reyting (idStudent, summ_balls) VALUES (New.idStudent, New.balls); END IF; END PostgreSQL: CREATE FUNCTION trigger_results_insert_after() RETURNS trigger AS $$ BEGIN IF EXISTS(SELECT * FROM "Reyting" WHERE "idStudent"=NEW."idStudent") THEN UPDATE "Reyting" SET "summ_balls"="summ_balls"+NEW."Balls" WHERE "idStudent"=NEW."idStudent"; ELSE INSERT INTO "Reyting" ("idStudent", "summ_balls") VALUES (NEW."idStudent", NEW."Balls"); END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER tr_results_insert_after AFTER INSERT ON "Results" FOR EACH ROW EXECUTE PROCEDURE trigger_results_insert_after(); Поэкспериментируйте сами со вставками записей, чтобы изменялся рей- тинг студентов. 60 Разберем еще один пример хранимой функции для демонстрации исполь- зования курсоров – временных таблиц, представляющих собой результаты вы- полнения запроса и обрабатываемые построчно – от первой записи до послед- ней. Для этого создадим еще одну версию функции перевода баллов в оценку – каждая строка таблицы Marks в ней будет обрабатываться построчно до полу- чения строки с нужной оценкой или отсутствием соответствующей оценки. MS SQL Server: CREATE FUNCTION dbo.GetMark4(@balls INT) RETURNS INT BEGIN DECLARE @res INT, @mark INT, @lowB INT, @highB INT; SET @res=2; -- декларация курсора, связанного с запросом DECLARE mark_cursor CURSOR FOR SELECT * FROM Marks; -- открытие курсора OPEN mark_cursor; -- считывание первой строки курсора в переменные @mark, @lowB, @highB FETCH NEXT FROM mark_cursor INTO @mark, @lowB, @highB; -- цикл продолжается, пока считывание возможно, -- на это укажет глобальная переменная WHILE @@FETCH_STATUS = 0 BEGIN -- определяем, соответствуют ли баллы текущей оценке IF @balls BETWEEN @lowB AND @highB BEGIN SET @res=@mark; BREAK; END -- переход к следующей строке курсора FETCH NEXT FROM mark_cursor INTO @mark, @lowB, @highB; END -- закрытие курсора CLOSE mark_cursor; -- разрушение курсора DEALLOCATE mark_cursor; RETURN @res; END GO MySQL: для обработки завершения записей курсора здесь требуется создать специальный обработчик CONTINUE HANDLER FOR NOT FOUND. В остальном работа с курсором аналогична. CREATE DEFINER = 'root'@'localhost' FUNCTION decanat.GetMark4(balls INT) RETURNS int(11) BEGIN -- переменные для хранения полей кортежа из таблицы Marks DECLARE mark, lowB, highB, res INT; 61 -- переменная для определения, завершен ли просмотр курсора DECLARE is_end INT DEFAULT 0; -- определение курсора для таблицы Marks DECLARE mark_cursor CURSOR FOR SELECT * FROM Marks; -- объявление обработчика ошибки завершея записей курсора DECLARE CONTINUE HANDLER FOR NOT FOUND SET is_end=1; SET res=2; -- открытие курсора OPEN mark_cursor; -- считывание первой записи курсора FETCH mark_cursor INTO mark, lowB, highB; -- организация цикла просмотра строк из курсора WHILE is_end=0 DO -- проверка диапазона баллов для текущей оценки IF balls BETWEEN lowB AND highB THEN SET res=mark; -- организация выхода из цикла SET is_end=1; END IF; -- считывание очередной записи курсора FETCH mark_cursor INTO mark, lowB, highB; END WHILE; CLOSE mark_cursor; RETURN res; END PostgreSQL: как и в предыдущем случае отличия будут в организации цикла и выхода из него. CREATE FUNCTION GetMark4 (integer) RETURNS integer AS $$ DECLARE res integer; DECLARE mark integer; DECLARE lowB integer; DECLARE highB integer; DECLARE mark_cursor CURSOR FOR SELECT * FROM "Marks"; BEGIN res:=2; OPEN mark_cursor;--открываем курсор LOOP --начинаем цикл по курсору --извлекаем данные из строки и записываем их в переменные FETCH mark_cursor INTO mark, lowB, highB; --если такого периода и не возникнет, то мы выходим IF NOT FOUND THEN EXIT;END IF; IF $1 BETWEEN lowB AND highB THEN res:=mark; END IF; END LOOP;--заканчиваем цикл по курсору CLOSE mark_cursor; --закрываем курсор return res; END; $$ LANGUAGE plpgsql; |